Sql-server – SQL Server High Availability without Windows Clustering

We have multiple customers using SQL Server 2012 with availability groups on Windows Server 2008 R2. The fixes listed in the pre-requisites for implementing availability groups needs to be applied for Windows to ensure that you don't run into known issues.

1. Database mirroring is the fastest HA method for SQL Server - you can expect as little as 10 seconds (maybe less) downtime when manually failing over. Perhaps 40 seconds when an automatic failover occurs. Automatic failovers take longer since they rely on the "timeout mechanism" to detect a problem. Manual failovers are very quick since you are initiating the process, thereby removing the timeout.

   Mirroring consists of two entirely separate SQL Server instances, utilizing a "shared-nothing" approach. This means none of infrastructure required by mirroring is shared by more than a single instance. The mirrored database(s) served by the instances involved in the mirroring partnership typically reside on separate storage subsystems - the mirroring process sends each and every transaction from the primary to the secondary instance thereby providing two physically independent copies of each mirrored database.

   If you have a requirement for automatic failovers without data loss, which I highly recommend, you will need a mirroring witness instance (this can be a SQL Server Express instance, by the way). The witness acts as a quorum to allow SQL Server to determine which of the two instances will actually provide access to the mirrored databases.

   Database Mirroring has been deprecated since SQL Server 2012. What this really means is at some point in the future, Microsoft will remove Database Mirroring from the product. The pertinent point is "at some point"; Microsoft is almost never explicit about when they will actually remove support for deprecated items. One good example is they removed support for the antiquated RAISERROR @errno @errmsg syntax in 2012. This syntax has been on the "deprecated" list since around the year 2000 - it took them 12 years to truly disable support for this syntax. Database Mirroring is supported in the as-yet unreleased SQL Server 2016 product. Presuming Microsoft removes mirroring support from the next version of the product, which may come out in 2018, you could expect to be officially supported by Microsoft running on SQL Server 2016 until at least 2021, since Microsoft typically provides support for at least 5 years after the product is released to manufacturing.

   One downside to Mirroring is each client needs to be aware that there are two possible hosts for the database; and needs to be coded to handle that. There is a special entry you can provide through the connection string, namely "failover partner", that helps automate some of that; however the client still needs to be coded in such a way that it will re-try transactions gracefully during the failover period.

2. SQL Server Clustering provides high-availability by allowing one of several instances to "serve" the databases residing on the clustered instance. SQL Server Clustering relies on Windows Server Failover Clustering, and as such they are essentially one-and-the-same for the purposes of comparing HA methodologies. The process of failing over a clustered instance consists of:

   a. Shutting down the currently-running instance.

   b. Moving resources (disks and network connections, etc) from the currently-running instance to the next-to-run instance.

   c. Starting up the SQL Server instance on the next-to-run instance. This startup process is exactly like starting any other SQL Server instance; the recovery process must be ran against each database on the instance, including roll-back and roll-forward of any transactions that were in-flight at the time the prior instance shut down.

   Because of the steps I outlined above, the process of failing over a clustered instance is generally slower than failing over a mirrored database.

3. Always On is the technology Microsoft is pushing as the replacement for Database Mirroring. It is essentially a hybrid of both mirroring and clustering, providing both the faster failover speeds of mirroring along with the useful management aspects of clustering. With Always On, clients do not need to be aware of the multiple instances of SQL Server involved; they simply "speak" to the virtual network interface. Always On is a great technology, however it can be a bit daunting to setup, and it requires Enterprise Edition, among a number of other prerequisites.

4. HA at the hypervisor level - SQL Server does not have control over any aspect of this process. If vMotioning a server causes its response time to slow, you may get client timeouts, etc. If the SQL Server is part of critical infrastructure, which it almost certainly is if you are looking for HA, why would you take a chance on some process that SQL Server cannot understand?