High-availability solutions for SQL Server 2008 R2

Find out how to implement SQL Server 2008 R2 high-availability solutions, including failover clustering, database mirroring and log shipping, and learn how they affect a database.

Solution provider's takeaway: There are a few SQL Server 2008 R2 high-availability solutions to choose from, and you should know how each solution improves your customer's database, as well as the issues that they can present. Read through the pros and cons of each high-availability solution before making recommendations to your customers.

Because your customers' SQL Server 2008 R2 databases are often mission critical to their organizations, solution providers need to ensure that the databases are protected against various types of failures. This tip discusses a few options available to providers that implement high-availability (HA) solutions and redundancy services for SQL Server 2008 R2 databases.

Using failover clustering for SQL Server 2008 R2 high availability

Failover clustering is the primary high-availability solution that Microsoft uses in its server products. It is a Windows Server technology that provides hardware-level redundancy for various Microsoft server products, such as SQL Server 2008 R2 and Exchange Server 2007 and 2010. The primary advantage of failover clustering is that clustered applications are normally immune to server hardware failures. Another benefit is that when failover clustering is in use, solution providers can take a physical server down for maintenance without affecting application availability because the application will continue to run on another cluster node.

Despite the advantages of failover clustering, there are some disadvantages to keep in mind as well. One important consideration is that failover clustering can be expensive to implement. Microsoft only supports failover clusters if they are running on hardware that is certified for use with Windows Server. It isn't enough for just the servers to be Windows Server certified; every component in the server must be certified for Windows Server. Therefore, if you purchase a server that was certified for use with Windows Server, but install a generic network interface card (NIC) into the server, your cluster nodes will be in an unsupported state.

Another issue with failover clustering in SQL Server 2008 R2 is that it requires shared storage.
Regardless of how you choose to implement shared storage, solution providers should keep two things in mind: First, shared storage can also be expensive. Your customers can't simply purchase a Windows Storage Server license and install it onto their hardware. It's only available from hardware manufacturers that pre-load it onto servers. As you can imagine, vendors usually charge a premium for storage servers, and costs increase even more if your customer requires Fibre Channel adapters or high-speed storage arrays.

Another consideration for shared storage is that because all cluster nodes share a common storage array, the storage server can become a single point of failure. Failover clusters are relatively immune to most hardware failures, but a storage failure can bring down the entire cluster. A number of different ways exist to implement shared storage. In my own organization, for example, I have failover cluster nodes that connect to Windows Storage Server 2008 via iSCSI.

One last thing about failover clustering: Failovers occur at the server level, not at the database level. Therefore, if you have a clustered SQL Server that hosts multiple databases and one of those databases fails, you can't perform a failover on that one single failed database.

Database mirroring as a high-availability solution

One popular alternative to failover clustering is database mirroring. Disk mirroring involves keeping two separate copies of a disk, while database mirroring lets solution providers create a single duplicate database copy.

Database mirroring has two advantages that failover clustering doesn't have. First, it operates at the database level instead of the server level, which means that database mirroring gives you much more flexibility than failover clustering.

The other advantage is that you can use standard servers for database mirroring. Microsoft recommends that customers purchase servers that are compatible with Windows Servers, but they don't have to buy hardware that is specifically certified for failover clustering. And, with database mirroring, you don't have to worry about the cost and complexity of using shared storage.

Using log shipping as a high-availability solution

Another option for SQL Server 2008 R2 high availability is log shipping, which allows solution providers to create multiple secondary database copies. Log shipping works by synchronously sending copies of database logs to SQL Server instances. The logs are then replayed on the secondary servers, bringing the database copies up to date.

Some solution providers use log shipping as a way of overcoming the limitations associated with database mirroring. Database mirroring works well, but it only allows you to have one database copy. Mirroring occurs in near real time, so database changes are written to the secondary database quickly. This can be a problem if your customer's database becomes corrupt or if records are accidentally deleted from a database.

Log shipping has two main advantages. First, solution providers can implement a delay so that logs are not replayed immediately. This is important because if there is a problem with the primary (or mirror) database, the logs can be intercepted before they are replayed, thus preventing the problem from spreading.

The second main advantage to log shipping is that it allows for multiple copies of a database. Some organizations use log shipping as a way of maintaining database copies at alternate data centers, which prevents data loss in the event that the primary data center is destroyed.

Solution providers should note that although log shipping is commonly used as a supplement to database mirroring, it is an independent technology and can be used regardless of whether mirroring is in use or not.

About the expert
Brien M. Posey, MCSE, is a five-time recipient of Microsoft's Most Valuable Professional award for his work with Exchange Server, Windows Server, Internet Information Services (IIS), file systems and storage. Posey has served as CIO for a nationwide chain of hospitals and was once responsible for the Department of Information Management at Fort Knox. As a freelance technical writer, he has written for Microsoft, TechTarget, CNET, ZDNet, MSD2D, Relevant Technologies and other technology companies. You can visit Posey's personal website at www.brienposey.com.

Dig Deeper on Database software management