This article will discuss different options for SQL Server High Availability and how they work.
Manual fail-over refers to the ability to manually switch to a secondary server if the primary server fails. The following options allow manual fail-over:
NOTE: WSFC stands for Windows Server Fail-over Cluster.
|Method||Minimum Version||Minimum Edition||Requires WSFC||Allows Data Loss||Description|
|Log Shipping||2000||Web||No, but recommended||Yes||Automatically copies and restores log backups to a secondary server.|
|Database Mirroring (no witness)||2005||Standard||No||No||Synchronously commits all transactions to both the primary and secondary database. Mirroring has been deprecated in favor of Availability Groups.|
|Database Mirroring (Asynchronous Mode)||2005||Enterprise||No||Yes||Asynchronously commits all transactions to the secondary database. Mirroring has been deprecated in favor of Availability Groups.|
|Always On Availability Groups (High Performance Mode)||2012||Enterprise||Yes||Yes||Asynchronously commits all transactions to one or more replicas.|
High availability refers to the ability to not only fail-over but to do so automatically when the primary server goes down. Below are the options available in SQL Server for this:
Note: WSFC stands for Windows Server Fail-over Cluster.
Note: Unlike the manual fail-over options. None of these options allow data loss.
Note: For automated fail-over, these options require a third server to act as a witness to establish quorum.
|Method||Minimum Version||Minimum Edition||Requires WSFC||Description|
|Database Mirroring (Synchronous Mode)||2005||Standard||No||Synchronously commits all transactions to both the primary and secondary database. For automated fail-over the client application must have both servers in the connection string. Mirroring has been deprecated in favor of Availability Groups.|
|Always on Availability Groups (High Safety Mode)||2012||Enterprise||Yes||Synchronously commits all transactions to both the primary and secondary database. For automated fail-over the client application uses an availability listener (special DNS name). Allows multiple replicas and allows read-only replicas.|
|Basic Availability Groups||2016||Standard||Yes||Synchronously commits all transactions to both the primary and secondary database. For automated fail-over the client application uses an availability listener (special DNS name). Allows only a single replica, which is inaccessible until fail-over. Intended to replace database mirroring.|
|Fail-over Cluster||2008||Standard||Yes||All databases must be stored on a WSFC disk, which is automatically attached to the secondary server if the primary goes down. Requires both servers to have access to the same SAN or to a replicated SAN volume. Not ideal for cross-datacenter fail-over.|
The best fail-over option will be best determined by the needs of your business and/or application.
Monetary Cost: You will notice from the tables above that all automated fail-over solutions require Standard Edition or above. Additionally, these all require two servers to be licensed, which doubles that licensing cost. If you cannot afford at least $450/month in SQL licensing costs, then consider using Log Shipping.
Data: The Log Shipping option is the least expensive option, but you can lose at least several minutes of data in the event that you need to fail over and fail-over must be handled manually. If your business cannot afford to lose data during a fail-over event, then you should stick with the High Availability options.
Time: If you cannot afford to have more than a few minutes of downtime, not only will you want to choose one of the High Availability options, you may want to put your fail-over server in a separate datacenter, which will limit you to the Mirroring and Availability Group options.
Performance: If performance is more critical than downtime, then a Fail-over Cluster in a single datacenter may be a good option as Synchronous Commit based fail-over options are going to slow down database writes since all data must be written to both servers before transactions complete.
SQL Server replication is not considered a High Availability or Fail-over tool. It is mainly a performance tool that allows particular tables or data to be distributed to separate servers for processing. With Replication, data is not guaranteed to be copied and is simply replicated on a best-effort policy so both databases are never guaranteed to be the same.
Each of these fail-over and high availability options are complex enough that they deserve their own article. For now, the following Microsoft documentations can be referenced for each option: