Chapter 3: Availability and Recovery Features

New improvements in the SQL Server 2005 release have continued to improve SQL Server’s availability by increasing its failover clustering capabilities and providing new database options that offer both continuous availability and improved database recovery capabilities. Achieving high availability is relatively easy on a small scale but gets exponentially more difficult as the size of the system increases. SQL Server 2005 provides a number of new features that enhance database availability and recoverability by addressing the primary obstacles that hinder enterprise-level database availability. Some of the factors that have hindered database availability in previous versions of SQL Server include things like delayed failover times, the need to have exclusive database access for selected maintenance operations, and occasional difficulty in getting the server to respond during high CPU usage time, such as when some user’s code has gone into an infinite loop. Another factor that can adversely affect database availability is hardware upgrades. Even though hardware upgrades are planned events, they typically require system downtime in order to perform the upgrade. SQL Server 2005 provides a new capability designed to decrease the downtime required by one of the most common hardware upgrades. In this chapter, we’ll take a look at these new availability and recovery options found in SQL Server 2005 so that you can understand how these features can be used to implement SQL Server 2005 in a highly available and recoverable production database environment.

Protection Against Database or Server Failure

A database server failure is a breakdown that’s caused by either a hardware failure or a software issue that renders the server inoperable for a period of time. Database server failure can also be caused by environmental factors such as a disaster. In this section, you’ll learn about some of the most important new features that SQL Server 2005 provides to address database server failures.

Improved Failover Clustering

One key high-availability benefit that SQL Server 2005 derives in part from its support for Windows Server 2003 is significantly improved support for failover clustering. Taking advantage of the enhanced clustering support found in Windows Server 2003, SQL Server 2005 can now be implemented on clusters of up to eight nodes on Windows Server 2003 Datacenter Edition. In addition, SQL Server 2005 supports four-node clustering on Windows Server 2003 Enterprise Edition and Windows 2000 Datacenter Server. A maximum of two-node clustering is supported in Windows 2000 Advanced Server.

With Windows clustering services, each server in the cluster is called a node. All of the nodes in a cluster are in a state of constant communication. If one of the nodes in a cluster becomes unavailable, another node will assume its duties and begin providing the same services as the failed node. This process is called failover. Users who are accessing the cluster are switched automatically to the new node.

Clustering can be set up in a couple of basic ways: Active-Active clustering, where all of the nodes are performing work, or Active-Passive, where one of the nodes is dormant until the active node fails. Windows Server 2003 also supports N+I configurations (N active with I spare), which provide a very flexible and cost-effective clustering scenario to enable highly available applications. For example, with an eight-node cluster in an N+I configuration, you can have seven of the eight nodes set up to be available and providing different services while the eighth node is a passive node that can assume the services of any of the seven active nodes. Figure 3-1 illustrates an example eight-node cluster, where seven nodes are active and one node is in standby waiting to step in if any of the seven active nodes fail.

Figure 3-1: Eight-node cluster support

Some of the clustering-specific improvements in SQL Server 2005 include support for an unattended cluster setup. In addition, all of the different services within SQL Server 2005 are fully cluster-aware, including:

• Database Engine

• Analysis services

• Reporting services

• Notification services

• SQL Server Agent

• Full-Text Search

• Service Broker

• SQLiMail

In addition, all of the major management tools in SQL Server 2005 are also cluster-aware, including:

• SQL Server Management Studio

• Service Control Manager

• SQL Profiler

• SQL Query Analyzer

You can find more information about the SQL Server 2005 management tools in Chapter 1.

Database Mirroring

Probably the biggest new feature in the area of availability is SQL Server 2005’s support for Database Mirroring. The new Database Mirroring feature protects against database or server failure by giving SQL Server 2005 an instant standby capability. Database Mirroring essentially provides database-level failover. In the event that the primary database fails, the database mirror enables a second, standby database to be available in about 2–3 seconds. Database Mirroring provides zero data loss, and the mirrored database will always be up-to-date with the current transaction that’s being processed on the primary database server. The impact of running Database Mirroring to transaction throughput is zero to minimal. The database failover support can be set up to be performed either automatically or manually. The manual failover mode is good for testing, but you’ll want your production databases to be able to fail over automatically. Database Mirroring works with all of the standard hardware items that support SQL Server—there’s no need for special systems, and the primary server and the mirrored server do not need to be identical. In addition, unlike in high-availability clustering solutions, there’s no need for any shared storage between the primary server and the mirrored server. You can see an overview of how the new Database Mirroring feature works in Figure 3-2.

Figure 3-2: Database Mirroring

Database Mirroring is implemented using three systems: the primary server, the mirroring server, and the witness. Don’t be confused by the names. The primary server is just the name of the system currently providing the database services. All incoming client connections are made to the primary server. The mirror server’s job is to maintain a copy of the primary server’s mirrored database. Depending on the configuration of the mirrored server, the primary server and the mirror can seamlessly switch roles. The witness essentially acts as an independent third party, helping to determine which system will assume the role of the primary server. Each system gets a vote as to who will be the primary server. It takes two votes to decide on the primary server. This is important because it’s possible that the communications between the primary server and the mirror server could be cut off, in which case each system would elect itself to function as the primary server. The witness would cast the deciding vote.

Database Mirroring works by sending transaction logs between the primary server and the mirroring server. So in essence, the new Database Mirroring feature is a real-time log shipping application. Database Mirroring can be set up with a single database, or it can be set up for multiple databases. When a client system writes a request to the primary server, that request actually gets written to the primary server’s log file before being written into the data file, because SQL Server uses a write-ahead log. Next, that transaction record gets sent to the mirroring server, where it gets written to mirroring server’s transaction log. After the mirroring server has written the record to its log, it sends an acknowledgment to the primary server that the record has been received, giving both systems the same data in each of their log files. In the case of Commit operations, the primary server waits to receive an acknowledgment from the mirroring server before sending its response back to the client, telling it the operation is completed. In order to keep the data files up-to-date on the mirroring server, it is essentially in a state of continuous recovery, taking the data from the log and updating the data file.

Database Mirroring is initialized by taking a backup of the database that you want to mirror on your primary server and then restoring that backup to your mirroring server. In other words, the mirrored database must exist on the mirror server before the mirroring process can begin. This puts the underlying database data and schema in place. The backup and restore process can use any of SQL Server’s standard media types, including tape or disk. Next, you use the ALTER DATABASE command as shown here to start the mirroring process:

ALTER DATABASE <database name> SET PARTNER = '<partner server name>'

The ALTER DATABASE command must first be run on the mirroring server, pointing it to the name of the primary SQL Server in the SET PARTNER clause. Then the ALTER DATABASE command is run a second time, this time on the primary server. When the ALTER DATABASE command is run on the primary server, the name of the mirroring server is supplied in the SET PARTNER clause. Once the ALTER DATABASE command has completed on the primary server, database mirroring is set into motion and the primary server will begin shipping logs to the mirroring server. The next step in setting up Database Mirroring is to set up the witness. Much as you set up the primary and mirroring server, you set up the witness by again running the ALTER DATABASE command on the primary server, as shown in the following listing:

ALTER DATABASE <database name> SET WITNESS = '<witness server name>'

In this case, the database name would be the same database name that was used in the earlier commands. However, this time the SET WITNESS clause is used to specify the witness server. Once the witness server has been set up, Database Mirroring has all of the information that it needs to perform database failover.

Database mirroring essentially gives you a fault-tolerant virtual database. However, that isn’t the entire Database Mirroring story. Implementing an instant standby database enables the database to be quickly available after a failure, but what about the user connections to that failed database? That’s where the new Transparent Client Redirect feature comes into play.

Transparent Client Redirect

The Transparent Client Redirect feature works very closely with the new Database Mirroring feature to allow client systems to be automatically redirected to the mirroring server when the primary server becomes unavailable. This new feature is implemented in the new SQL Server 2005 Microsoft Data Access Components (MDAC), and no changes are required to the client- or data-layer applications. The MDAC middleware is aware of both the primary and mirroring servers. MDAC acquires the mirroring server’s name upon its initial connection to the primary server. When the connection to the primary server is lost, MDAC will first try to reconnect to the primary server. If that first connection attempt fails, then MDAC will automatically redirect its second connection attempt to the mirroring server. Just as with clustering, if the connection is lost in the middle of a transaction, then that transaction will be rolled back and must be redone after the client connects to the mirroring server.

When to Use Clustering or Database Mirroring

Both clustering and database mirroring are enterprise-ready solutions that are capable of providing a backup system in case of database or server failure. They have many similarities. Both are capable of enabling zero data loss, both have automatic failure detection, both provide automatic failover, and both enable client reconnection. However, there are some important differences. As its name implies, Database Mirroring works at the database level, while clustering works at the server level. Database Mirroring provides nearly instant 2–3 second failover time, while clustering typically has about a 30-second failover time—sometimes longer, depending on the level of database activity and the size of the databases on the failed server. Database Mirroring also protects against disk failures slightly better because there is no shared disk storage, as there is in a clustering solution. There is virtually no distance limitation for Database Mirroring, while clustering has a limit of about 100 miles, because there is a time limit for transmitting the heartbeat between cluster nodes. In addition, Database Mirroring is a simpler technology that’s easier to implement than clustering. On the other hand, Database Mirroring can’t be used for the system databases, while clustering does protect the system databases as well as the user databases. Basically, clustering is the better solution for protecting an entire server, while Database Mirroring is the better solution for protecting a single critical database or application.