DR Options for SQL Server in a vSphere EnvironmentPosted: November 10, 2014 | |
While SQL Server is not one of my core competencies, I have worked with clients to protect their business critical applications in a VMware environment that utilizes SRM for DR. These options rely on either Native SQL protection schemes or VMware options like SRM or vSphere Replication. There are, of course, many 3rd party options, as well, depending on the storage array in use, which I won’t go into here. While there are usually good, better, and best options, the idea I’d like to get across here is that there are many ways to protect SQL Server. They can all be used at the same time even. I’ve had clients that had so many SQL Servers, this is essentially what they did – they had to pick and choose how to protect each based on their relative importance.
SQL Server 2012 AlwaysOn Availability Groups
For the most critical SQL Servers, the image below shows the high-level view of what my clients have used with success. For server failures at the Primary Data Center, there are multiple SQL Servers. AAGs can use both an Active-Active model and an Active-Passive model with regard to where the active database resides. Continuing with the Primary Site, Node 1 can host both an Active and a Passive database. Node 2 can host an Active and Passive database, as well, working with Node 1 to perform synchronous replication. Through asynchronous replication, both databases can be replicated to the DR site, where only Passive copies reside. In the event Site A completely fails, Node 3 can be brought online.
SRM (and array-based replication) for non-critical SQL Servers
For non-critical database servers, standard array-based replication works well. Once the data is replicated, use SRM to orchestrate the recovery just like any other workload. Here, one needs to decide if they need database-consistent replications. Sometimes, the decision is made to replicate databases in an inconsistent state because 1) regular backups are done that will be available for restore at the DR site and 2) the database just isn’t important enough for the extra work or licensing needed to make sure it’s replicated in a consistent state. If consistency is important, then third-party plugins can be used to ensure database consistency is achieved before replicating to the DR site. If plugins are used, you can still use SRM to recover the VM. These plugins are usually available from storage vendors.
vSphere Replication for RPOs of no more than 15 minutes
SRM, array-based replication, and vSphere Replication can work together. If one doesn’t want to use third-party plugins to ensure consistent databases are replicated, one can use vSphere Replication just for those SQL Servers. vSphere Replication makes calls to Microsoft’s VSS, which can tell applications such as SQL Server to flush outstanding writes to disk and freeze I/O just before a snapshot is taken and replication is started. The catch with vSphere Replication is that it has a minimum RPO of 15 minutes, but replicated data is consistent. Yay!
Other native SQL Server DR solutions
Microsoft and SQL Admins are likely already familiar with the existing, native SQL Server solutions for protecting data across sites. Database mirroring between SQL Server 2008 clusters (one cluster at each site) is an option as well as log-shipping. RPOs and RTOs will drive this discussion if one is using SQL Server 2008. If using 2012, lean towards AAGs.