One of the reasons to virtualize SQL Server and any other business critical application is that you can use automation and templates to rapidly provision new instances in a very standardized and consistent manner. Over the last few days I’ve been creating and destroying literally dozens of SQL Server VM’s in my test lab. But when I first started I ran into what seems to be a common problem. The custom drive letters I’d set for my SQL Server Databases were changed each time a new VM was provisioned from a template and customized. As a result there was a bit of manual post process activity required before the VM’s could be used. Anything manual is less than ideal if you want to do this a lot. To find out how I fixed this and the scripts I used, keep reading.

Fortunately I’m not alone and people have hit this problem before. However many of the solutions I tried that had previously been published didn’t work. So there was a bit of trial and error involved. The root cause of the problem is that from Windows 2008 onwards when the sysprep process is used to customize a VM it will reset the drive letters back to defaults. This is still the case in Windows 2012 R2. If you have your SQL Server Transaction Log set up on L: for example, this is a problem, as L: doesn’t exist after the VM is provisioned from the template. Michael White wrote a good article on Building a Windows 2012 R2 VMware Template, which I recommend you read. This article focuses specifically on SQL Server Templates (I.e. Windows with SQL Installed on the Template) and correcting the drive letter problem as part of the provisioning and customization process.

My SQL Server Template VM is configured with 4 vCPU and 32GB RAM and a few different drives as listed in the image below. This image is how the drive letters were meant to be configured, as this is from the original template.

However after cloning the template to a new VM and the customization process had completed the drive letters ended up like this.

At this point the SQL Server doesn’t start as all of the default locations are messed up. I needed a reliable way to ensure the drive letters were consistent once the new VM’s were provisioned. I ran across this article that talks about preventing drive letters changing during sysprep. After following the advice and exporting the HKEY_LOCAL_MACHINE\System\MountedDrives, generalizing the template by running c:\windows\system32\sysprep/sysprep /generalize /oobe /quit, and restoring the MountedDrives key and shutting down the guest, I was able to get new VM’s to provision with the same driver letters. However I had to manually enter some details such as product license key in the console of each VM as it was being provisioned and it broke the VM customization process. So this was clearly not going to work.

I then stumbled across two very helpful articles. One by David Klee – Execute a Powershell self-elevating script after a VMware template deployment and another by Alex Fontana – I Asked For Mount-Points, Not Drive Letters…, both of which were very helpful. I first experimented with using diskpart manually after a VM had been cloned to fix the drive letters by creating a text file called config_disks.txt, similar to what Alex had described. My config_disks.txt file was as follows:

select volume 0
remove all
select volume 3
remove all
select volume 4
remove all
select volume 5
remove all
select volume 6
remove all
select volume 7
remove all
select volume 8
remove all
select volume 9
remove all
select volume 10
remove all
select volume 11
remove all
select volume 0
assign letter=d
select volume 3
assign letter=b
select volume 4
assign letter=l
select volume 5
assign letter=t
select volume 6
assign letter=h
select volume 7
assign letter=g
select volume 8
assign letter=e
select volume 9
assign letter=f
select volume 10
assign letter=v
select volume 11
assign letter=u

The remove all command will remove any existing drive letters from the volume. The volume number is consistent across the source VM and the destination VM’s (at lest on Windows 2012 R2). You need to remove all the existing drive letters before attempting to assign new ones, else you risk the operations fail due to a drive letter already being used.

Manually running this with dispart /s config_disks.txt fixed the problem and following a reboot of the VM, SQL Server would start and I could access it. Now I just had to automate this task as part of the provisioning and customization process. I experimented with adding diskpart directly to the customization spec run once section, however it was unsuccessful with my Windows 2012 R2 VM. I also found due to the privileges that the startup scripts ran as I was not able to write a lot file to c:\ by piping the output of the diskpart /s config_disks.txt >> c:\diskpartlog.txt command.

The solution turned out to be easy. Although the diskpart command itself wouldn’t run directly in the run once section of the VM customization spec, a batch file would, and would do so without the need for any code to elevate it’s privileges. Here is an image of what my customization spec looks like for these SQL Server VM’s.

I wanted to make sure that the results of the customization and provisioning process were recorded so it would make troubleshooting easier. VMware stores the results from the VM customization process in c:\windows\temp\vmware-imc. The Guest Customization Log is c:\windows\temp\vmware-imc\guestcust.log. So as part of this process I wanted to output a list of the volumes before and after the change. To do this I created another dispart script called c:\listvolumes.txt containing a single line “list volume“. By running diskpart /s c:\listvolumes.txt >> c:\windows\temp\vmware-imc\diskpartlog.txt before and after the main diskpart script I’m able to record the before and after state.

Here is the final listing of the finishsetup.bat file:

REM Update Volume Drive Letters and Finish Customization Process
diskpart /s c:\listvolumes.txt >> c:\windows\temp\vmware-imc\diskpartlog.txt
diskpart /s c:\config_disks.txt >> c:\windows\temp\vmware-imc\diskpartlog.txt
diskpart /s c:\listvolumes.txt >> c:\windows\temp\vmware-imc\diskpartlog.txt
REM Set Power Profile to High Performance and Turn Off Hibernation
powercfg /l >> c:\windows\temp\vmware-imc\powercfglog.txt
powercfg /h off
powercfg /s scheme_min
powercfg /l >> c:\windows\temp\vmware-imc\powercfglog.txt
REM Reboot the system for the changes to take effect
shutdown /r /f /t 0

After the VM reboots you can log in and review the lot files diskpartlog.txt and powercfglog.txt to ensure the output is as you expect. You can also review the drive letters and that your SQL Database starts as expected. The first time you run through this you should verify that everything is as you expect it, then you can have confidence when you clone many more VM’s everything will work. If you want to clone lots of VM’s you might like to check out an article by Magnus Andersson (VCDX56) – Create multiple VMs from existing vSphere VM using PowerCLI.

This is great if you have exactly the same filesystem layout as I do in this particular SQL Server VM Template. But what if you’ve got a different layout? I actually had another template with a different layout and I didn’t want to have to manually create the main diskpart script every time I come across a different VM template. Instead I wrote a PowerShell script that would generate the config_disks.txt file for me. This script assumes you’re using drive letters, not mount points, but could be modified to work with mount points. It also assumes that you CD/DVD Drive is Volume 0, that you have the system restore and C drive as Volume 1 and 2 respectively.

# Powershell Script to Output Custom Drive Letters
# to text file for VM templates that will be cloned
# Version 1.0
# Author: Michael Webster
#
# Output file to save dispart configuration script
$outputfile = “c:\config_disks.txt“
#
# Drive letter of CD/DVD
$cdromltr= “D“
#
# End Of User Configuration
#
# Array variables to store temporary output
$OutArray = @() # Store Dispart Script Commands to remove drive letters
$OutArray2 = @() # Store Diskpart Script Command to add drive letters
#
# If Output File exists, remove it
if (Test-Path $outputfile) { Remove-Item $outputfile }
#
# Use Diskpart to enumerate the volume list

$dp = “list volume” | diskpart | ? { $_ -match “^  [^-]” }
#
# Extract the Volume Numbers and Drive Letters from Diskpart output
# Note: This ignores the system restore and C drive, assumes default
$vols=$dp.substring(2,9) | select-object -last ($dp.length-2)
$driveltr=$dp.substring(15,1) | select-object -last ($dp.length-2)
$drives=$driveltr.split(” “)
#
# The 3rd Drive Letter is the first custom drive letter specified
# Note: Assumes CD/DVD Drive, System Restore and C Drive are before it
$i=3
#
# Assign Correct Drive Letter to CD/DVD Drive
$outarray += “select volume 0`r`nremove all”
$outarray2 += “select volume 0`r`nassign letter=$cdromltr”
#
# For each volume remove the current drive letter and add the new drive letter
# Note: The output is stored in the output variables and will be written to the output file
foreach ($vol in ($vols | select-object -last ($vols.length-2))) {
$drive=$drives[$i]
$outarray += “select $vol `r`nremove all”
$outarray2 += “select $vol `r`nassign letter=$drive”
++$i
}
#
# Write the Diskpart Script commands to the output file
add-content $outputfile $outarray
add-content $outputfile $outarray2

Final Word

I’ve tested this process on vSphere 5.5 Update 2 with Windows 2012 R2. I’ve provisioned and destroyed about 50 VM’s to check that everything worked as I expected. If you’re using a different combination of Windows version and hypervisor version your milage may vary. But I hope at least this provides a good starting point that you can build from. My PowerShell skills are somewhat primitive, so I welcome suggestions on ways I could improve the script and make it more adaptable to different scenarios. As always your comments are welcome. Although I didn’t cover the reasons for having multiple drive letters in this article they are covered in the book that I co-authored with Michael Corey and Jeff Szastak for VMware Press (2014) – Virtualizing SQL Server with VMware: Doing IT Right.

—

This post first appeared on the Long White Virtual Clouds blog at longwhiteclouds.com. By Michael Webster +. Copyright © 2012 – 2015 – IT Solutions 2000 Ltd and Michael Webster +. All rights reserved. Not to be reproduced for commercial purposes without written permission.