Today marks another installment in my ‘Smart Moves with SQL Server VMs’ blog post series. Today we are going to help stretch the minds a bit farther and talk about another way to help SQL Server DBAs move large quantities of data between VMs in an extremely rapid manner.
Last time we talked about using network and virtual machine proximity to reduce the network bottleneck when moving around a lot of data. Today let’s use a different strategy to do a similar action. In some cases, this could even move the data faster than before, but it takes a bit more scripting to do the same thing. What am I referring to here?
Move the virtual hard disk from one VM to another to rapidly move a lot of data from one server to another.
The scenarios here are endless…. migrating data from one SQL Server to another for database migrations, huge volumes of ETL processing, emptying local database backup locations for archival purposes, etc.
In this example, I’ll click through how to do this with a VMware vSphere 5.5 environment, but not only will any hypervisor do the same thing here, you can script out this process in a large number of ways to automate the process.
First, pick a VM (in this case one called DB1) and add a dedicated virtual disk for the placement of the data that needs to be moved.
Inside the guest operating system, mount and format the new disk. Label it something obvious.
Add whatever files you wish to add to the new virtual disk that you wish to move to a second server.
Now, disconnect the virtual disk from this VM. Before you do this, determine the exact location in the infrastructure where this disk resides. Do not delete them from the underlying infrastructure.
Note that the source VM no longer has this drive mounted.
Back in vCenter, let’s add this disk to a second VM (DB2). This time, let’s find the existing hard disk.
You just moved 15GB of data in roughly the thirty seconds that it took you to click about ten times. If you were to script this action, you are talking about just a couple of seconds or so.
Consider what scripting a solution like this could do in your environment. If you are on a VMware environment, PowerCLI is your PowerShell extension answer (PowerCLI book on Amazon for your reference). If you are on a Hyper-V environment, the Hyper-V PowerShell commandlets are your answer (Hyper-V book with PoSH examples at Amazon). Go automate your data movement and make your systems more efficient!