SQL, replication, and VMware SRM. How to replicate SQL and keep you network team happy.

So you made it down the path of virtualizing your servers and now are tasked to protect them and have a DR/BC plan. When using VMware the choice is simple, replicate the VM’s and protect them with SRM.

The simplest way is to use some replication product and replicate all Datastores to your DR site. Shortly after you start this process, the network people will start knocking down your door because you are flooding the network with all this replication traffic.
This is where you really need to start looking to see what needs to be replicated and what does not.

One of the things that I want to discuss here is how can we minimize the replication traffic for a SQL VM.
First we need to know where most of the IO’s are created. In most SQL databases this is on the drive that houses the TempDB files. The TempDB is a work area for the database to temporarily store data until a process is complete and the data can be written to the log files. Nothing is stored permanently on the TempDB drive. When you restart SQL it will also clean out the TempDB and start fresh.

With this knowledge, why would you want to replicate this data to your DR site? It causes a lot of load on your network connection and when you bring up the VM at your DR site the TempDB will be flushed anyway.

Let’s see how we can stop replicating the TempDB and setup SRM so that when we bring up the VM and have a TempDB drive available for SQL to use.

First off we need to make sure that your VM that is running your SQL server is configured correctly.  For this example I will keep the configuration as easy as possible. The SQL VM we will name SQL1, and that is how it shows up in vCenter. We will have two Datastores, one that will house the OS, SQL bin files, and the DB and Log files; we will name it ProdDatastoreRepl. The other Datastore will have the TempDB drive on it; this will be ProdDatastoreLocal.

Now we setup replication to our DR site and only replicate ProdDatastoreRepl as this is the only data that we need to protect. While replication is happening we can continue on with the rest of the setup.

On the DR site we create a Datastore with the name DRDatastoreLocal. This is where we are going to create the drive that will house the TempDB.
On our DR VC, we create a VM and give it the same name as our prod SQL server, in this case SQL1. We create it with two drives. One drive for the Window OS and the other for the TempDB.

Now we install windows on the VM, no need for any configuration, updates, service packs or any of that. Once the install is complete and the VM has booted into Windows, we go into Disk Management and format the second drive. We need to make sure that the Drive letter that we used in our production SQL server for the TempDB drive is the same as what we use here. So if we used D: in production we need to use D: in DR.

If we used any type of folder structure on the production SQL server for the location of the TempDB, we will need to recreate it here. So if the TempDB on production was located in D:\TempDB then we need to create a folder here as well called D:\TempDB.

Now we will shut down the VM, open setting and remove the second Hard Disk from the VM. Do not delete the files from Disk. Now that the drive is detached from the VM we can right click the VM in inventory and click remove from disk. This will clean up all the files in the folder on the Datastore with the exception of the detached drive that we need.

The hard work now has been done and from here on it is standard SRM setup. The only thing that will be different is that when you set up your Protection groups and configuring the VM SQL1, on the Storage tab, you will now see an warning for the TempDB drive, this is to be expected because it was not replicated to the DR site. So what we need to click the Hard Disk that has the warning on it, click browse and browse to DRDatastoreLocal and the SQL1 folder. Open the folder and you will find the SQL1_1.vmdk file.

The rest of the SRM setup is standard so I will leave that out for now.

We now have a replicated SQL server that is protected by SRM and we minimized replication traffic for this VM by excluding the TempDB from replication.

Tagged with: , ,
Posted in SQL Replication, SRM
4 comments on “SQL, replication, and VMware SRM. How to replicate SQL and keep you network team happy.
  1. […] rather interesting explanation of workarounds for this sort of issue. His blogpost is here – SQL, replication, and VMware SRM. How to replicate SQL and keep you network team happy. Essentially, Fred’s work around is having datastore at the Recovery Site with the SAME name […]

  2. Patrick says:

    Nice post. Thanks for the info.

    This SQL replication info applies to any SQL DB correct?

  3. djheath says:

    Hi, Do you have any information on how SRM does the replication? We are experiencing large network usage (7 to 16 GB of data waiting to be replicated between replication cycles), and not sure what is happening. Will SRM only copy the block level changes or will it copy the whole SQL data file if just one row is changed in the database?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: