Choosing a SQL Server Availability Technology

SQL Server provides rich and deep levels of enterprise database functionality. In the way of availability technologies, it might sometimes seem like it provides too many options. Deciding which right high availability or disaster recovery option is right for you can be confusing. Here’s a quick overview of SQL Server 2016’s high availability and disaster recovery technologies.

AlwaysOn Failover Clustering (FCI)

While descriptive, the name AlwaysOn became a source of confusion when Microsoft released it back with SQL Server 2012.  The AlwaysOn moniker does not really describe a single feature; instead, it is an umbrella for two quite different availability technologies. The first is AlwaysOn Failover Clusters Instances (FCI) and the second is AlwaysOn Availability Groups (AG). AlwaysOn FCI is built on Windows Failover Clustering and it provides protection from unplanned server failures. FCI protects SQL Server at the instance level and if there is a server failure it will provide automatic failover. The SQL Server service will be started on one of remaining cluster nodes. The time it takes to start the service depends on the activity level of the server. Before the server service can restart all of the committed transactions in the transaction log must be reapplied and all of the uncommitted transactions must be rolled back. For an inactive server this will only take a few seconds. A very active server may require many minutes to complete this task. SQL Server 2016 Enterprise edition supports to 64 nodes while the Standard edition support up to two node FCI clusters.

AlwaysOn Availability Groups (AG)

AlwaysOn AGs are quite different from AlwaysOn FCI. AGs provide protection at the database level. First introduced in SQL Server 2012, AGs can protect multiple databases and they provide very fast automatic and manual failover for all of the protected databases. Failover only takes a few seconds. AGs also require a Windows Server Cluster, but unlike FCI, each cluster node runs its own SQL Server instance. AGs work by forwarding transaction log entries from the primary to the secondary replicas. The secondary replicas can be setup as readable and can be used for reporting or backup. One of the great things about AGs is that they allow you to mix and match synchronous and asynchronous replicas. With SQL Server 2016, you can have up to eight secondary replicas with up to three synchronous replicas. Synchronous replicas are typically used for high availability with automatic failover and they are usually connected on low latency networks. Asynchronous replicas are used for disaster recovery. They support manual failover and they are usually located on high latency networks in separate geographical locations or in the cloud. The SQL Server 2016 Enterprise edition supports up to eight secondary replicas while the Standard edition support two secondary replicas. You can combine AlwaysOn FCI and AlwaysOn AGs for server and database level protection.

Database Mirroring

Database mirroring was first introduced back in SQL Server 2005 and it was the precursor to AGs. Like AGs, database mirroring works by forwarding transaction log entries to the mirrored server. Database mirroring provides protection for a single database and you must choose whether you implement synchronously or asynchronously. The mirrored databases do not support read access. SQL Server 2016 Enterprise edition supports both synchronous and asynchronous database mirroring. The standard and business intelligence editions only support Safety Full Only Mode (synchronous). Database mirroring is simple and it doesn’t require a Windows server cluster. However, Microsoft has stated that they intend to deprecate database mirroring in the next release of SQL Server, so this is clearly a technology of the past – not the future.

Log Shipping

Log Shipping is essentially SQL Server’s original disaster recovery technology. Log Shipping has been around forever as a Do-It-Yourself option and Microsoft added it as a built-in feature back with SQL Server 2008.  Log Shipping also works by forwarding transaction log entries to one or more target servers. Unlike AGs or Database Mirroring, Log Shipping does not work in real-time. The transaction log is backed up periodically then forwarded and restored to the secondaries. Log shipping does support limited read-only access to the secondary databases. It is supported in all of the editions of SQL Server.

Underwritten by HPE and Microsoft