Tag Archives: SQL

How to manage development against large databases

We use Dynamics NAV where I work and have a database that has grown to over 500 GB. Due to this, doing a database restore is a time consuming operation that should be avoided whenever possible. Due to the nature of the application it does not work well to have multiple developers in the same database which means we need to have a DB for each developer as well as for our test and stage environments.

The easiest way to accomplish this is through the wonders of virtualization and specifically differencing disks. The basic workflow is this:

  • Create a virtual machine using Hyper V that has a base OS and SQL versions installed for the data your working with and two drives, one for the OS and the other one completely balank
  • Restore the database you need to provide to multiple developers to the secondary drive that does not contain an operating system
  • After the restore, detach the database and shutdown the virtual machine
  • Rename the second vhd with the data and log files on it to _parent.vhd
  • Set the parent vhd to be read only
  • Move the parent vhd to some location where you have the space to store this large restore and where you can provide read access to it via a unc path like \\MyServer\Share\_parent.vhd
  • Open Hyper V manager and create a new differencing disk pointing to the parent vhd through the unc path created earlier
  • Now provide a copy of this vhd to every individual who needs to have their own copy of this data set.

That’s it. If you have developers that need to have a local copy of the data accessible from their laptops now they can simply attach the vhd using disk manager in Windows 7 or 8 and use the data however they like. If your providing a copy of the data to a staging environment that is a virtual machine simply shut down the vm and attach a copy of the child vhd as a new disk.

This process is made even better if you have windows server 2012 as the file server where your parent is stored and either 2012 or windows 8 clients pulling the data as cifs 3 really makes the performance of this solution even better than it has been.

There are many variations on this theme that you can use (snapshots of source data exposed via iscsi directly from your San, keeping the parent and child vhds on the same storage so that you can get better performance, keeping the parent and child vhds on the same disks and then attaching the child vhds to the host file server os and then creating separate shares of the data inside each child vhd so that remote SQL servers can simply attach the data and log files directly has unc paths without any knowledge of the vhd system behind it, etc.) but by using differencing vhds you can worse it more manageable to deal with many copies of a large data set while still keeping cost low.

Tagged ,