Category Archives: Virtualization

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 ,

How to rebase differencing disk VHDs (or how to change the parent of a differencing VHD when the parent has moved)

I am a big fan of differencing VHDs as the provide tremendous power when working with large database but they also come with their own set of complications.

One of those complications that can arise is when due to files being moved or folder structures being reorganized the parent VHD of your differencing VHD is no longer accessible. After you try and attach your differencing VHD you may see an error message like this one:

Virtual Disk Manager The network path was not found.

We can use the following power shell from a Windows 7 PC with the latest version of Microsoft Virtual PC that comes with Windows XP Mode installed to take a look at the parent property of our differencing disk to see if something is wrong

$vpc=new-object -com VirtualPC.Application
$VHDChild = $vpc.GetHardDisk("C:\NAV2010.06.10_Diff")


In my case I recently changed the IP address of a NAS that was storing the parent VHD and assigned a DNS record to the NAS so that I wouldn’t have to keep track of it’s IP address but now the path to the parent VHD that is stored in my differencing VHD is no longer valid.

Thankfully this is fairly easy to fix. All we need to do is add the following code so that we get a new $VHDParent object using the corrected path and then assign that object to the Parent property of our differencing disk

$VHDParent = $vpc.GetHardDisk("\\devnas\Data_Backups SQLCluster1\NAV2010.06.10.vhd")
$VHDChild.Parent = $VHDParent


Now we can attach our differencing VHD without error and get back to work =).

How to create a differencing VHD that refers to a parent VHD that is on a network share from Windows 7

I love the fact that Windows 7 can attach VHD disks as if they were local hard disks. This allows me to pull off some very interesting stuff that I will blog about later but one pain point has been that through the UI with only Windows 7 I cannot create a differencing VHD from a parent VHD that is stored at a UNC path on the network.

The solution to this is to install the latest version of Microsoft Virtual PC which can be downloaded form the Windows XP Mode website. After installing this you are now able to create differencing disks based on parent VHDs that are stored on your network at a UNC path as shown below:

$vpc=new-object -com VirtualPC.Application
$vpc.CreateDifferencingVirtualHardDisk("\\devnas\Data_Backups\SQLCluster1\NAV 2010.08.20 Differencing.vhd","\\devnas\Data_Backups\SQLCluster1\NAV 2010.08.20 Full.vhd")

Note: If you still have the parent VHD attached to a machine this command will silently fail. Make sure that the parent is not being used and is not attached to any machine before running this.

In a future post I will talk about some of the use cases for this technique that make it invaluable.

Hint: Multiple 300GB+ database development from a laptop with only an 120 GB hard drive as if all DBs were local

Control Windows Virtual PC from PowerShell

I have used the command line interface (CLI) for Sun’s Virtual Box and for Hyper V but I didn’t realize that Virtual PC also can be accessed through PowerShell. This might be the key to creating network based differencing disks natively on Windows 7 which currently isn’t something that can be done in a straightforward way through the GUI and even the way that it can be done has limitations (like not being able to create a differencing disk with the parent disk on the network).

Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.
PS H:\> $vpc=new-object -com VirtualPC.Application
PS H:\> $vpc
HostInfo                    : System.__ComObject
VirtualMachines             : System.__ComObject
VirtualNetworks             : System.__ComObject
UnconnectedNetworkAdapters  : System.__ComObject
SupportDrivers              : System.__ComObject
Tasks                       : System.__ComObject
MinimumMemoryPerVM          : 4
MaximumMemoryPerVM          : 3575
SuggestedMaximumMemoryPerVM : 3217
MaximumFloppyDrivesPerVM    : 1
MaximumSerialPortsPerVM     : 2
MaximumParallelPortsPerVM   : 1
MaximumNetworkAdaptersPerVM : 4
MaximumNumberOfIDEBuses     : 2
DefaultVMConfigurationPath  : C:\Users\cmagnuson\AppData\Local\Microsoft\Windows Virtual PC\Virtual Machines\
SearchPaths                 : {}
USBDeviceCollection         : System.__ComObject
Name                        : Windows Virtual PC
Version                     : 6.1.7600.16393
UpTime                      : 6
PS H:\> $vm = $vpc.FindVirtualMachine(
PS H:\> $vm = $vpc.FindVirtualMachine("IE 6")
PS H:\> $vm
Name                  : IE 6
ConfigID              : {DB33D85B-1FAC-4A67-910A-33EE4A4F06FC}
File                  : C:\Users\cmagnuson\AppData\Local\Microsoft\Windows Virtual PC\Virtual Machines\IE 6.vmc
Accountant            : System.__ComObject
GuestOS               : System.__ComObject
Display               : System.__ComObject
Keyboard              : System.__ComObject
Mouse                 :
State                 : 2
ShutdownActionOnQuit  : 0
SavedStateFilePath    : C:\Users\cmagnuson\AppData\Local\Microsoft\Windows Virtual PC\Virtual Machines\IE 6.vsv
BIOSGUID              : {D5356F8F-4383-4B68-ACDA-03D1A06E5C46}
BIOSSerialNumber      : 8425-1374-7407-2656-1007-9980-44
BaseBoardSerialNumber : 8425-1374-7407-2656-1007-9980-44
ChassisSerialNumber   : 8425-1374-7407-2656-1007-9980-44
ChassisAssetTag       : 1746-8710-8930-6641-7796-6780-67
HardDiskConnections   : System.__ComObject
Undoable              : False
UndoAction            : 1
DVDROMDrives          : System.__ComObject
FloppyDrives          : System.__ComObject
NetworkAdapters       : System.__ComObject
SerialPorts           : System.__ComObject
ParallelPorts         : System.__ComObject
ProcessorSpeed        : 2926
HasMMX                : True
HasSSE                : True
HasSSE2               : False
Has3DNow              : False
RdpPipeName           :
Notes                 :
Memory                : 1024
AttachedDriveTypes    : {1, 0, 2, 0}
PS H:\>

Original Source: