SQL Server 2012 High Availability

Although performance is always nice, it’s really availability that's job number one for database professionals. After all, nothing slows down query performance like a server outage. SQL Server 2012 has a better high availability arsenal than any previous release. Here's my 10,000 foot overview of SQL Server 2012’s primary high-availability options.

AlwaysOn Failover Clustering
Microsoft’s premier high-availability technology, AlwaysOn Failover Clustering, is designed to reduce unplanned downtime by allowing clustered services such as SQL Server to automatically fail over to a backup node when a hardware or software failure makes the primary node unavailable. The SQL Server 2012 Business Intelligence and Standard editions are limited to two node failover clusters. SQL Server 2012 Enterprise edition can have up to 16 node clusters on Windows Server 2008 R2 and up to 64 on Windows Server 2012.

AlwaysOn Availability Groups
A new option for SQL Server 2012, AlwaysOn Availability Groups is essentially the next evolution of database mirroring. Designed to protect against unplanned and planned downtime, AlwaysOn Availability Groups can replicate multiple databases to as many as four replica systems with a mix of both synchronous and asynchronous replication of the transaction log. It fully supports automatic failover. AlwaysOn Availability Groups are found only in the SQL Server 2012 Enterprise edition.

Database Mirroring
Although I expect it will be eventually replaced by AlwaysOn Availability Groups, database mirroring is still available in SQL Server 2012. Database mirroring is limited to two systems: a principal and a mirror, with an optional witness that enables automatic failover. Database mirroring works by forwarding transaction log entries from a principal to a mirrored database, and it can be set up either synchronously for high availability or asynchronously across a WAN for disaster recovery. The SQL Server 2012 Business Intelligence and Standard editions can operate only in the synchronous full safety mode.

Log Shipping
Designed to protect against disasters at the system and site levels, log shipping works by periodically running a stored procedure that forwards transaction log entries to one or more destination systems Unlike database mirroring, which works in real time and is capable of automatic failover, log shipping requires manual failover and failback processes. Log shipping is supported by all SQL Server 2012 editions.

Database Snapshots
Database Snapshots are a point-in-time metadata-based copy of a given database, and they can be used for reporting purposes or to restore a database to previous state. For instance, database snapshots could be an effective protection against data corruption caused by user error. Database snapshots are supported only in the SQL Server 2012 Enterprise edition.

Fast Recovery
Fast recovery helps a database to be available more quickly following a restore operation. With Fast Recovery, the database is available as soon as the committed transactions are reapplied following a restore operation, with no need to wait for the uncommitted transactions to be rolled back. Like many of the other high availability options, Fast Recovery is available only in the SQL Server 2012 Enterprise edition.

Live Migration
While not technically a part of SQL Server 2012, Live Migration is a Windows Server technology that can help reduce planned downtime for virtualized SQL Server systems by allowing the SQL Server virtual machines (VMs) to be moved to a different host, with no end-user downtime. This enables maintenance to be performed on the virtualization host. All editions of SQL Server 2012 support live migration.

SQL Server on Server Core
Again, while not strictly a high-availability technology, SQL Server 2012’s support for running on Server Core can also boost SQL Server 2012’s availability by reducing the amount of patching that’s required by the host Windows Server OS. All editions of SQL Server 2012 support running on Windows Server Core.