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.
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.
MethodMinimum VersionMinimum EditionRequires WSFCDescriptionDatabase Mirroring (Synchronous Mode)2005StandardNoSynchronously 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)2012EnterpriseYesSynchronously 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 Groups2016StandardYesSynchronously 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 Cluster2008StandardYesAll 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: