This article will discuss different options for SQL Server High Availability and how they work.
Manual Fail-over
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 (Automated Fail-over)
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. |
Determining the Best Option
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.
-
Business Cost:
-
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.
-
What About Replication?
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.
How to Setup your Database for High Availability or Fail-over
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:
Log Shipping:
https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sql-server