Sql-server – Compare SQL Server high availability methods

clusteringhigh-availabilitysql server

I want to provide high availability for my SQL Server. It is important that the service is back as soon as possible, more than making sure that data isn't lost.
My SQL Server 2008 R2 is running on a VMware machine.

Which of the following ways will provide the highest availability?

  1. SQL cluster
  2. AlwaysOn (is it worth upgrading)?
  3. VM level cluster (HA / VMotion)
  4. Windows server cluster
  5. Any other method

Regarding mirroring, I see this comment:

Database Mirroring (SQL Server)
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead.

What is replacing it?

Best Answer

  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?

Related Question