Choosing the Right SQL Server Availability Technology

There’s no doubt that the biggest concern for DBAs and other database professionals is availability. If the database is down, you know exactly how fast those queries are going to be: slow. Then there’s the question of resulting downtime, recoverability, and data loss. When your server is down, these types of issues quickly take precedence over everything else.

In this post, I’ll cover the different SQL Server® 2014 availability technologies and help you to understand which technology is the best fit for your availability requirements. Or you can view this video where I show the different technologies and how they fit in your environment:

AlwaysOn Failover Clustering Instances

Built on Windows® Failover Clustering, AlwaysOn Failover Clustering Instances (FCI) are designed to provide server/instance level protection from both planned and unplanned downtime. AlwaysOn FCI provide automatic failover in the event of an unplanned server failure, and they offer no data loss. If a server in an AlwaysOn FCI fails, then the SQL Server service is started on one of the remaining nodes, and any uncommitted transactions are applied to the new SQL Server instance.

There can be a several-minute delay for the failover process to complete because of the length of time it takes to start the service and reapply all of the outstanding transactions. Windows Failover Clusters can have up to 64 nodes with Windows Server® 2012 R2.

AlwaysOn FCI needs shared storage like an iSCSI or Fibre Channel SAN that can be accessed by all of the nodes in the cluster. AlwaysOn FCI can be implemented on physical SQL Server systems, or it can be implemented on SQL Server systems that are running as VMs. The different cluster nodes can reside on the same host, or they can span different hosts as long as they can access the shared storage.

On the SQL Server 2014 Enterprise edition, AlwaysOn FCI supports up to 64 nodes with Windows Server® 2012 / R2. The SQL Server 2014 Standard and Business Intelligence editions are limited to two-node clusters.

Like you might expect, to use AlwaysOn FCI you need to first create a Windows Failover Cluster. You give the cluster a unique name and IP address, which network clients use to access the clustered SQL Server instance. After you create the cluster, you need to use the SQL Server Installation Center and select the New SQL Server failover cluster installation. Then, on all of the other additional cluster nodes, you would launch the SQL Server Installation Center, and run Add node to a SQL Server failover cluster to install SQL Server on the remaining cluster nodes.

AlwaysOn Availability Groups

Today, AlwaysOn Availability Groups (AG) are probably the most widely used SQL Server availability technology. AlwaysOn AGs were first introduced with SQL Server 2012, and they provide database-level protection from unplanned downtime. AlwaysOn AGs offered several advantages over database mirroring, which was SQL Server’s previous database-level, high availability technology. Database mirroring was limited to a single secondary mirror server. It could only protect a single database. Plus, it required you to choose between asynchronous and synchronous replication between the primary and the secondary mirror server.

AlwaysOn AG addresses all of these limitations. With SQL Server 2014, AlwaysOn AGs can have up to eight replicas, two of which can be synchronous. It provides the ability to protect multiple-user databases. You can simultaneously implement synchronous replication for high availability and automatic failover, as well as asynchronous replication for disaster recovery.

Secondary replicas can offer optional read-only access. Failover is automatic and takes only a couple of seconds. AlwaysOn AGs work by forwarding transactions from the primary to the secondary replicas. For the SQL Server 2014 Enterprise edition, AlwaysOn AGs can have a maximum of eight replicas. For the SQL Server 2014 Business Intelligence and Standard editions, they are limited to two nodes.

You can combine AlwaysOn FCI and AlwaysOn AG. For example, you might use AlwaysOn FCI to provide local high availability within a data center, and AlwaysOn AG for disaster recovery business requirements.

Database mirroring

While database mirroring has been supplanted by AlwaysOn AG, it is still implemented in both SQL Server 2014 and SQL Server 2016. Database mirroring was first introduced in SQL Server 2005, and it is essentially a real-time log shipping application. Database mirroring provides protection for a single-user database to one secondary mirror server. That mirror server can be connected either synchronously or asynchronously.

Database mirroring works by forwarding transactions from the principle server to the mirror server, and takes only a couple of seconds. While database mirroring is not as full-featured as AlwaysOn AG, it is far simpler to implement because there is no need to implement a Windows Server® Failover Cluster.

Database mirroring is best suited for customers with little technical expertise who require very limited database protection. Database mirroring on the SQL Server 2014 Enterprise edition supports either synchronous or asynchronous connections. For the SQL Server 2014 Standard and Business Intelligence editions, it is limited to Safety Full Mode (synchronous).

Database mirroring can also be combined with AlwaysOn FCI, where database mirroring provides disaster recovery. Database mirroring will still be supported in SQL Server 2016; however, Microsoft® has stated that it will not be supported in the next version of SQL Server. For more information, check out Deprecated Database Engine Features in SQL Server 2016.

Log shipping

Log shipping is really more of a disaster recovery technology than it is an availability technology. Log shipping works by taking a database backup and applying it to one or more remote secondary servers. Stored procedures on the primary database are then used to send transaction log backups to the secondary servers.

An optional third server, known as the monitor server, can monitor the status of the transaction log backup, restore operations, and raise alerts if there are failures. There is no automatic failover or failback, and there can be data loss. Log shipping is supported by the SQL Server 2014, Enterprise, Business Intelligence, Standard, and Web editions.

Replication

To be honest, replication should not be considered an availability technology. However, many organizations have implemented replication for high availability. Replication is really designed to address distributed database access and reporting, but it does allow you to replicate data between SQL Server instances. You can filter the data you want to protect. When used as an availability technology, replication has no automatic failover, and returning the data back to a failed replication source is a difficult, manual process.

For more information on SQL Server high availability solutions, check out High Availability Solutions (SQL Server).