High availability involves keeping your servers and their services up and running. Organizations running large enterprise applications, with users from all around the globe, demand a high-availability solution to assure 24x7 accessibility of the application by users. Continuous availability is essential to maintaining a successful online business. The need for a high-availability solution is easily justifiable by looking at the cost of unavailability, which in some instances can exceed the cost of the application and infrastructure.
The key to building highly available solutions is to minimize or eradicate the points of failures in the system architecture. The design of a highly available solution is focused on minimizing the points of failure within the available budget. Designing for availability involves anticipating, detecting, and automatically resolving hardware or software failures before they result in service errors, event faults, or data corruptionthereby minimizing downtime.
The following are the high-availability options in SQL Server 2000:
SQL Server 2005 continues to support and enhance these three high-availability solutions. In addition, a lot of new features, such as database mirroring, database snapshots, online and parallel indexing, and early restore access, are provided to increase availability. Let's look at a new, interesting high-availability solution, called database mirroring, that has the potential of becoming the best high-availability solution for SQL Serverbased applications.
Like replication and log shipping, database mirroring is a software solution for increasing the availability of a specific database. Database mirroring was originally called real-time log-shipping. However, database mirroring is the accurate and more appropriate term for this high availability technology. Mirroring maintains two copies of the database, only one of which is accessible by clients at a given time. The accessible database is known as the principal database, and its duplicate is known as the mirror database. The two server instances communicate and cooperate as partners in a database mirroring session. The principal and mirror databases must reside on different server instances, which are known as the principal server and the mirror server, respectively. In production environments, the principal and mirror servers are installed on different servers to enable the mirror server to operate as a "hot" standby server for the database. In typical mirroring configurations, if the principal server is lost, the mirror server is readily available as a failover partner.
In its simplest form, database mirroring involves only the principal and mirror servers. A third server instance, known as the witness, can be added to a mirroring session. The witness server enables automatic failover. Automatic failover causes the mirror server to roll forward its copy of the database until it is completely up-to-date and then switch principal role. The former mirror server becomes the principal server, and the former mirror database becomes the principal database. In addition to automatic failover, the mirror server also plays the tie-breaker role between the principal and mirror to prevent a split-brain scenario where both machines think they are the principal. For instance, if the principal and mirror servers come up at the same time and both think they are the principal, then the witness can break the tie.
Database mirroring works only with databases that use the full recovery model. It does not work with databases that use the simple and bulk-logged recovery models. In addition, the master, msdb, temp, and model databases cannot be mirrored.
Figure 8.1 shows a typical database mirroring configuration.
Figure 8.1. Database mirroring configuration consists of a principal server instance hosting the live database, a mirror server instance hosting the "hot" standby copy of the mirrored database, and, optionally, a witness server instance to be used for automatic failover.
Before we look at database mirroring in more detail, let's review some of the benefits offered by this high-availability solution and also compare it with failover clustering.
Benefits of Database Mirroring
The following are some of the benefits offered by a database mirroringbased high-availability solution:
Setting Up Database Mirroring
You can configure database mirroring by running T-SQL statements or by using Object Explorer in SQL Server Management Studio. The Mirroring page on the database properties dialog provides an interface to configure and monitor database mirroring. This section discusses the T-SQL approach.
Database mirroring requires SQL Server 2005 to be installed on the primary and the mirror instances and on the witness server instance (if present). After you have installed SQL Server 2005 on these servers and verified that the principal database is running in full recovery mode, you need to take these steps to set up database mirroring:
You can pause database mirroring by running the ALTER DATABASE...SET PARTNER SUSPEND statement. You can resume mirroring by running the ALTER DATABASE...SET PARTNER RESUME statement. You can stop mirroring by running ALTER DATABASE...SET PARTNER OFF.
Database Mirroring Operating Modes
A database mirroring session can operate in either synchronous or asynchronous mode. In synchronous mode, every transaction committed on the principal database is also committed on the mirror server, guaranteeing the protection of the data (that is, high-protection mode). This is achieved by waiting to commit a transaction on the principal database until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. In other words, synchronous mode forces the principal to not consider a transaction committed until the mirror has entered the transaction into its transaction log and sent back an acknowledgement. Note that the wait for this message increases the latency of the transaction. The time required for synchronization essentially depends on how far the mirror database was behind the principal database at the start of the session (as measured by the number of log records initially received from the principal server), the workload on the principal database, network latency, and the speed of the mirror system.
Synchronous operation is maintained in the following manner:
In synchronous operation mode, if a witness server is present, the session supports both automatic and manual failover (that is, high-availability mode). In the absence of a witness, synchronous sessions run in high-protection mode, and only manual failover is supported. In other words, high-availability mode requires the presence of a witness, which ideally resides on a third computer.
If transaction safety is set to OFF (that is, ALTER DATABASE <database> SET PARTNER SAFETY OFF), the mirroring session operates in asynchronous mode, also known as highperformance mode. In asynchronous mode, as soon as the principal server sends the log for a transaction to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. The database is synchronized after the mirror server initially catches up to the principal server. However, transactions commit without waiting for the mirror server to harden the log. Asynchronous operation permits the principal server to run with minimal transaction latency. The mirror server attempts to keep up with the log records sent by the principal server. But the mirror database always lags somewhat behind the principal database, never quite catching up. The gap between the databases is typically small. However, the gap can become substantial if the principal server is under a heavy workload or the system of the mirror server is overloaded. If the witness server is absent in the mirroring configuration, it is not recommended that you turn off transaction safety and run in asynchronous mode. Such a configuration can lead to data loss and split-brain between the principal and mirror.
Changing the transaction safety restarts the database, disconnecting all clients that are currently using the database.
Monitoring Database Mirroring
The following are some system catalog views, dynamic management views (DMVs), Profiler trace events, and PerfMon (that is, System Monitor) counters that you can use to monitor database mirroring:
You might want to avoid using your OLTP system (that is, the principal server) for reporting; this helps you better utilize the mirror server resources. As mentioned earlier, to use a mirror database for reporting, you can create one or more database snapshots on the database. However, remember that creating snapshots and using the mirror server for reporting increases the load on the mirror server, and this performance hit can slow down the entire database mirroring solution. Let's take a detailed look at database snapshots feature.
The CREATE DATABASE statement now supports a new clause, AS SNAPSHOT OF, that can be used to create a static, read-only, transaction-consistent snapshot of a database as it existed at the moment of the snapshot's creation. The most common application of database snapshots is to create a snapshot of a mirror database so that clients can access the snapshot database for purposes such as reporting. In order to provide updated data to the clients, new snapshots must be created periodically, and new client connections must be directed to the most recent snapshot. Each database snapshot exists independently of any other database snapshots. Database snapshots on a mirror database may reduce performance on the principal database. Therefore, after all the clients have disconnected from an out-of-date snapshot, it should be deleted.
The other application of database snapshots is in the event of a user error on a source database. You can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.
You cannot create a database snapshot graphically by using Management Studio. The only way to create a database snapshot is to run the CREATE DATABASE statement with the AS SNAPSHOT OF clause. SQL Server creates an NTFS sparse file to hold the snapshot. Snapshots cannot be created on FAT32 or RAW partitions.
Database snapshots are available only in the Enterprise Edition of Microsoft SQL Server 2005.
The following is an example of creating a database snapshot:
SET NOCOUNT ON USE master; GO IF EXISTS(SELECT database_id FROM sys.databases WHERE [name] = 'AW_05082005_SS1') DROP DATABASE AW_05082005_SS1; GO CREATE DATABASE AW_05082005_SS1 ON (NAME = AdventureWorks_Data, FILENAME='C:\AW_05082005_SS1.ss') AS SNAPSHOT OF AdventureWorks; GO SELECT * FROM sys.databases WHERE name = 'AW_05082005_SS1'; GO USE AW_05082005_SS1; GO SELECT * FROM sys.database_files; GO
This script checks whether a database named AW_05082005_SS1 exists. If it does, the script drops the database snapshot. Dropping a database snapshot is exactly the same as dropping any other user database. (Dropping a database snapshot deletes all the sparse files used by the snapshot.) Next, the preceding script creates a database snapshot by using the CREATE DATABASE statement. You can use the sys.databases, sys.master_files, and sys.database_files catalog views to see the database snapshot metadata. You can identify the source database for a snapshot by using the source_database_id column in the sys.databases catalog view. You can obtain the NTFS sparse filename for a database snapshot by using the physical_name column in the sys.master_files catalog view. You can use the fn_virtualfilestats function to determine the actual size and other statistics about the sparse file. The following SELECT statement uses the fn_virtualfilestats function to return the sparse file details for the database snapshot created in the preceding script:
SELECT * FROM fn_virtualfilestats(DB_ID('AW_05082005_SS1'),FILE_IDEX('AdventureWorks_Data'))
A database snapshot must exist on the same instance as the source database. Multiple snapshots can exist on a source database, and they must always reside on the same server instance as the database.
Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page do not affect the contents of the snapshot. The same process is repeated for every page that is being modified for the first time. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken.
To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file. Figure 8.2 shows the properties of a sparse file used for a database snapshot just created on the AdventureWorks sample database. Note that the Size value is 165MB; however, the Size on Disk value is 128KB.
Figure 8.2. When a database snapshot is created, the sparse file reserves the space but does not use any space until the source database is updated.
When users read data from a database snapshot, SQL Server always accesses the original data pages, regardless of where they reside. If the page has not yet been updated on the source database, a read operation on the snapshot reads the original page from the source database. After a page has been updated, a read operation on the snapshot still accesses the original page, which is now stored in a sparse file.
Sparse files grow in 64KB increments; thus, the size of a sparse file on disk is always a multiple of 64KB. The latest 64KB increment holds from one to eight 8KB pages, depending on how many pages have been copied from the source database. This means that, on average, the size of a sparse file slightly exceeds the space actually filled by pages. It is important to remember that a snapshot is not an entire copy of the source database. The snapshot refers to the source database for data that has not changed. Therefore, if the source database goes into recovery mode or if the source database takes a file offline, some data might not be available in the snapshot. If a filegroup was offline when a snapshot was created and you bring the filegroup online later, it is still considered offline in the snapshot.
Here are some considerations to keep in mind when creating and using database snapshots:
As mentioned earlier, a database snapshot can sometimes be very helpful in recovering from user errors. The following script shows that a table is dropped from a database and then a database snapshot is used to recover that deleted table:
SET NOCOUNT ON USE master; GO IF EXISTS(SELECT database_id FROM sys.databases WHERE [name] = 'AW_05082005_SS1') DROP DATABASE AW_05082005_SS1; GO CREATE DATABASE AW_05082005_SS1 ON (NAME = AdventureWorks_Data, FILENAME='C:\AW_05082005_SS1.ss') AS SNAPSHOT OF AdventureWorks; GO USE AdventureWorks; GO DROP TABLE dbo.DatabaseLog; GO --SELECT * FROM dbo.DatabaseLog USE master; GO RESTORE DATABASE AdventureWorks FROM DATABASE_SNAPSHOT = 'AW_05082005_SS1'; GO USE AdventureWorks; GO SELECT * FROM dbo.DatabaseLog; GO
The RESTORE statement allows you to restore from a database snapshot, provided that the following are true:
You have now learned about two new availability solutions introduced in SQL Server 2005. In addition to these new capabilities, SQL Server improves the classic availability solutions. The next two sections provide an overview of enhancements made to failover clustering and replication.
Failover Clustering Enhancements
Earlier versions of SQL Server supported only two-node or four-node clusters. SQL Server 2005 now supports failover clusters with up to eight nodes on 32-bit and 64-bit systems. In addition, Analysis Services has been enhanced to be cluster aware. Similarly to the database engine, you can configure Analysis Services on a failover cluster with up to eight nodes on 32-bit systems and four nodes on 64-bit systems.
SQL Server 2005 on Windows Server 2003 now supports dynamic Address Windowing Extensions (AWE) memory management. In an active-active cluster scenario, when the failover happens, SQL Server can dynamically adjust the amount of memory use based on the current workloads on the instances. It is recommended that you set the minimum server memory configuration setting to achieve a better balance after failover.
SQL Server 2005 setup has been enhanced to provide a simpler and more robust installation experience. Setup now allows scripted installation and also allows you to select components that you want to install. In SQL 2000, setup log files were dispersed across nodes and in various directories. In addition, the logging behaved differently for noncluster installations than for clustered installations. In SQL 2005, all logs are consolidated on the host computer. These logs use a standard naming convention that includes the machine name and the name of the component. The logs are placed by default into %ProgramFiles%\Microsoft SQL Server. Logging is now the same for clustered and nonclustered installations.
SQL Server 2005 now supports mount points. Clustered installations of SQL Server are limited to the number of available drive letters. Assuming that you use only one drive letter for the operating system, and all other drive letters are available as normal cluster drives or cluster drives hosting mount points, you are limited to a maximum of 25 instances of SQL Server per server. A mounted volume, or mount point, allows you to use a single drive letter to refer to many disks or volumes. If you have a drive letter G: that refers to a regular disk or volume, you can connect, or "mount," additional disks or volumes as directories under drive letter G: without the additional disks or volumes requiring drive letters of their own.
SQL Server 2005 significantly enhances the replication support to allow you to implement a secure, easy-to-set up, easy-to-manage, and scalable enterprise data movement and availability solution. Complete description of these new features is beyond the scope of this chapter. However, the following are some of the replication enhancements introduced in SQL Server 2005:
Online and Parallel Index Operations
SQL Server 2005 Enterprise Edition now supports index operations such as creating, altering, and dropping an index to be performed while users are still accessing the table data and using other indexes on the table. The CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements support the ONLINE option with a value of either ON or OFF (the default is OFF). The ONLINE option being set to ON specifies that underlying tables and associated indexes are available for queries and data modification during the index operation.
ONLINE cannot be set to ON when an index is being created on a local temporary table, when an XML index is being created, while you're creating a clustered index of a table that contains large object data types, or while you're creating nonclustered index on a large object data type.
When the ONLINE option is set to ON, long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an intent share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to proceed. At the start of the operation, a shared (S) lock is held on the source object for a very short period of time. At the end of the operation, for a short period of time, an S lock is acquired on the source if a nonclustered index is being created; or a schema modification (SCH-M) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt.
The underlying table cannot be modified, truncated, or dropped while an online index operation is in process. Under certain circumstances, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. In these rare cases, the SQL Server 2005 database engine selects the user or application activity as a deadlock victim. Although online index operations permit concurrent user update activity, the index operations take longer if the update activity is very heavy. Typically, online index operations are slower than equivalent offline index operations, regardless of the concurrent update activity level.
Let's try an online index operation and at the same time monitor it by using the Profiler. You need to start SQL Server Management Studio and connect to a SQL Server instance, using Query Editor, and then type the following query:
USE AdventureWorks; GO ALTER INDEX ALL ON Sales.SalesOrderHeader REBUILD WITH (ONLINE = ON); GO
Before you execute this query, you need to start SQL Profiler and connect to the same SQL Server instance. On the Trace Properties dialog, you need to select Blank from the Use the Template combo box. Then you should select the Events Selection tab, expand Progress Report, and check Progress Report: Online Index Operation. You can use this event to view the progress of an online index build while it is running. Then you click the Run button to begin the Profiler trace. Next, you execute the preceding query in Management Studio. Figure 8.3 shows an example of trace events that result from running this query.
Figure 8.3. You can use Profiler to track the progress of an online index build while it is running.
In addition to online index operations, SQL Server 2005 Enterprise Edition also enables support for parallel index execution. The MAXDOP option can now be specified with the CREATE INDEX, ALTER INDEX REBUILD, DROP INDEX, and ALTER TABLE DDL statements. The MAXDOP index option cannot be specified in the ALTER INDEX REORGANIZE statement.
Enhanced Multi-instance Support
The SQL Server 2000 supported up to 16 instances of the relational engine per computer. SQL Server 2005 Enterprise Edition increases the number of instances supported to 50, making server consolidation a more viable option. Other SQL Server 2005 editions support up to 16 instances of the relational engine. When you install multiple instances, it is possible to install different editions of SQL Server 2005. For instance, you may have a few instances of Enterprise Edition and a few instances of Standard Edition on the same computer. One instance of the Microsoft search service MSSearch 3.0 (a full-text search engine) exists per instance of SQL Server.
In addition to the database engine, SQL Server 2005 Analysis Services now supports the notion of multi-instance installations. Previous releases did not support installing multiple instances of Analysis Services. With SQL Server 2005 Enterprise Edition, up to 50 instances of Analysis Services can be installed on a single computer. Other SQL Server 2005 editions support up to 16 instances of Analysis Services.
Dedicated Administrator Connection
In previous releases, when SQL Server appeared to be unresponsive or running in an abnormal state, administrators struggled to connect to that instance in order to terminate problematic processes and fix the problem. SQL Server failed to respond to client connections in such situations. This has been fixed in SQL Server 2005 by means of Dedicated Administrator Connection (DAC). The new command prompt utility SQLCMD.exe supports the -A switch, which you can use to connect to an instance of SQL Server even if the server appears to be locked or running in an abnormal state. Members of the sysadmin fixed server role can activate the dedicated administrator connection locally over TCP/IP. Because DAC is established over TCP/IP, you need to ensure that the SQL Browser service is running if you are connecting to a named SQL Server 2005 instance. The connection is only allowed from a client running on the server. No network connections are permitted.
When connected using DAC, to avoid any potential blocking scenarios, it is recommended that you run all diagnostic queries with the lowest transaction isolation level, READ UNCOMMITTED, and that you use the LOCK_TIMEOUT value to set to a short time-out period. You should run only diagnostic queries and avoid potentially long-running operations such as defragmenting or rebuilding indexes. You can also use SQL Server Management Studio to connect to an instance by using DAC, by prefixing the instance name with ADMIN:. However, note that any Management Studio activity that attempts a second connection fails. For instance, let's say you connect to a Query Editor window in Management Studio by using DAC. Now, if you try to change the database by using the Database combo box on the toolbar, the operation fails, and you get an error message.
Here is an example of using SQLCMD.exe to use DAC on a SQL Server 2005 instance:
SQLCMD -S DDGXP\SQL2005 -E -A
This command prompt statement connects to a SQL Server 2005 instance by using DAC over Windows authentication.
Early Restore Access
A restoration is a multi-phase process. There are three possible phases of a restoration:
The database engine in SQL Server 2005 Enterprise Edition now lets users access a database after the redo, or roll forward, phase of a database restore operation completes. Earlier versions of SQL Server did not allow access to the database until completion of the roll back, or undo, phase. The other editions of SQL Server 2005 also do not let users access the database until recovery completes.
Instant File Initialization
If the database and transaction log files do not already exist, they must be created before data can be restored to them. The database and transaction log files are created, and the file contents are initialized to zero. Separate worker threads create and initialize the files in parallel. In addition to while creating a database, the file initialization process is also run when you add files to an existing database by using the ALTER DATABASE statement.
On Windows XP and Windows Server 2003 systems, SQL Server 2005 enables data files to be initialized instantaneously, which allows for fast execution of database or filegroup restore operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. To use instant file initialization, you must run the SQL Server service under an account that has the SE_MANAGE_VOLUME_NAME special Windows privilege. This privilege is assigned to the Windows Administrators group by default. If you have system administrator rights, you can assign this privilege by adding the Windows account to the Perform Volume Maintenance Tasks security policy.
Instant file initialization works only for data files and not for log files. A log file is a circular chain of log blocks, and the log manager relies on a block parity bit change to detect the end of a log at startup. SQL needs to initialize the log files so that SQL can establish the initial parity bit to correctly detect the end of a log after a restart.