An Overview of SQL Server High Availability Options

There’s no doubt that availability is one of the most important if not the most important job for the database administrator. However, choosing the right options isn’t always easy because there’s a lot of different options and each is designed for a different purpose. Here's an quick overview of SQL Server’s different high availability options:

Windows Failover Clustering—Microsoft's premier high availability solution, Windows Failover Clustering provides sever level protection. Before Windows Server 2008 clustering was very difficult to setup. With Windows Server 2008 and higher this task is much easier but there are still many working parts. Clustering provides automatic failover and can be implemented with up to 16 nodes (each node is a separate server system). There is some downtime as services are shifted to the backup node. Clustering requires Windows Server Enterprise edition and higher.

Database Mirroring—Unlike clustering database mirroring provides protection at the database level. Database mirroring automatically forwards transaction log entries from a principle system to a mirror system. Database mirroring is limited to two systems: the principle and the mirror. There are two modes of database mirroring: high safety and high performance. High Safety mode is only available in the SQL Server Enterprise edition and provides automatic failover but it also requires a third SQL Server system to act as a witness. One catch with database mirroring is that you need to be sure that server level objects such as logins are the same between both systems. Mirroring doesn’t handle this for you.

Log Shipping—Log shipping is really more of a disaster recover technology. With log shipping transaction logs are periodically send from the primary system to one or more secondary systems. Unlike mirroring which forwards transaction log entries as they occur log shipping forward entries periodically whenever the SQL Agent job runs the log shipping stored procedure. Log shipping is supported in both the SQL Server Standard and Enterprise editions.

Live Migration/VMotion—Other important technologies that can provided improved availability for SQL Server include Live Migration and VMotion. Both of these technologies are based on virtualization and they are designed to address planned downtime. Live Migration and VMotion enable a VM, including one running SQL Server, to be moved between virtualization hosts with no downtime. This allows you to perform scheduled maintenance like host patching and hardware repair with no loss of services to the end users.  Live Migration requires Windows Server 2008 R2 or Hyper-V Server 2008 R2 or higher. VMotion requires VMware’s vSphere.

Fault Tolerant solutions—In addition to these technologies other third party vendors provide different high availability technologies that can be effectively used to increase the availability of SQL Server installations. One of the most effective is using fault tolerant hardware. Fault tolerant hardware systems use redundant hardware components to provide availability even in the event of a hardware failure. We have tested several fault tolerant systems in our labs and found them to be very effective. Like you might expect fault tolerant are more expensive then standard x86 servers. However, they are also easier to setup and manage than clusters in that you manage them like a regular standalone server system. Because you use them just like a standard server system they can also be combined with any of these other availability technologies.