SQL 2012 (Denali) Enables Exciting New HA Scenarios
Up until now there’s been several ways to try to achieve high availability with SQL Server but all present significant complications. There’s SQL clustering, but this is expensive and still only leaves us with one copy of the database (shared storage). There’s mirroring, but this is done on a per-database level and presents challenges with some applications. There’s log shipping, which is somewhat difficult to setup and requires manual effort for failover and other challenges.
There are also other challenges around disk performance and availability. In a previous post, I wrote about some PCI Flash storage solutions that can be used to improve performance, but there are challenges here with vMotion and more.
What if the best of clustering, mirroring and log shipping could be combined into a new HA paradigm, and that this paradigm could also solve some problems around PCI Flash (i.e. vMotion) as well? I don’t want to present SQL 2012’s Always-On feature as a magic bullet, but it seems to me that there’s a lot here to be excited about.
To start with, SQL 2012 is based on the cluster concept so this doesn’t go away. But on top of the clustering foundation, SQL 2012 will replicate databases (and groups of databases) to other hosts in the cluster (no shared storage). In addition, the passive copies of the data are accessible and could be used for searches and queries. You can now essentially have one “write” copy of the database and multiple “read only” copies of those databases within the cluster that are ready to automatically fail over and become primary if needed.
This opens up many new possibilities for scalability, performance and HA. You can now select a group of databases (databases managed as a single logical unit) to protect and have automatically replicated to other host(s) within the cluster. Because shared storage is not being used, any corruption or availability issues at the DB/LUN levels do not need to incur downtime. And these secondary copies can be configured to be accessible for reporting and other read-only functions – allowing such activity to take place without adding any load on the primary DB server. You could even configure a host for asynchronous replication to create a remote replica without slowing down the primary DB/host.
And as for the challenges with PCI Flash cards and vMotion, that problem pretty much goes away. You can now leverage PCI Flash in your host servers to maximize I/O performance and allow SQL 2012 Always On to provide the HA (via replication) and not need be concerned so much about trying to vMotion across non-shared-storage (PCI Flash).
I haven’t had the opportunity to work with SQL 2012 yet but it sounds very promising and I am very much looking forward to the new possibilities. Microsoft’s SQL 2012 virtual launch event is scheduled for March 7, 2012 and more details are available here.