Creating a Custom SQL Server VM Image in Azure

Recently I had the opportunity to work on a project were I needed to create a custom SQL Server image for use with Azure VMs.  The process was a little more challenging than I initially anticipated.  I think this is mostly because I was not familiar with the process of preparing a SQL Server image.  Perhaps this isn’t much of a challenge for an experienced SQL Server DBA or IT Pro.  For me, it was a great learning experience.

Why a Custom SQL Server Image?

The Azure VM image gallery already contains a SQL Server image.  It’s very easy to create a new SQL Server VM using this image.  However, doing so has a few important trade-offs to consider:

• Unable to fully customize the base install of SQL Server.  This is a template/image after all – you get a VM configured the way the image was configured.
• Unable to use your own SQL Server license.  If your company has an Enterprise Agreement (EA) with Microsoft, it’s likely there is already some SQL Server licenses built into that agreement.  Depending on the details, it may be significantly cheaper to use the licenses from the EA instead of paying the SQL Server VM image upcharge from Azure.

The Basic Steps

There are 6 basic steps to creating a custom SQL Server VM image for use in Azure.

1. Provision a new base Windows Server VM
2. Download the SQL Server installation media
3. Run SQL Server setup to prepare an image
4. Configure Windows to complete the installation of SQL Server
5. Capture the image and add it to the Azure VM image gallery
6. Create a new VM instance using the custom SQL Server image

The basic idea here is to create a base VM, customize it with a SQL Server image, capture the VM to create an image, and then provision new VMs using that captured VM image.

Let’s dive into each of these in a little more detail.

Note: the terminology here can be a little confusing. When referring to the VM used to create the template/image, I’ll use the term “base VM”. When referring to the VM created from the base VM, I’ll use the term “VM instance”.

1. Provision a new base Windows Server VM

There are multiple ways to create a Windows Server VM in Azure.  Creating a VM via the Azure management portal and PowerShell are probably the two most popular options.  Be sure to check out this tutorial to learn how to do so via the portal. For the purposes of this post, I’ll do so via PowerShell.

$img = Get-AzureVMImage `
    | where { ( $_.PublisherName -ilike "Microsoft*" -and $_.ImageFamily -ilike "Windows Server 2012 Datacenter" ) } `
    | Sort-Object -Unique -Descending -Property ImageFamily `
    | sort -Descending -Property PublishDate `
    | select -First(1)
 
$vmConfig = New-AzureVMConfig -Name "sql-1" -InstanceSize Small -ImageName $img.ImageName |
    Add-AzureProvisioningConfig -Windows -AdminUsername "[admin-username-here]" -Password "[admin-password-here]"
 
New-AzureVM -ServiceName "SQLServerVMTemplate" -VMs $vmConfig -Location "East US" -WaitForBoot

2. Download the SQL Server installation media

With the base Windows Server 2012 VM created, we can now get ready to prepare (sysprep) the SQL Server installation.  To do that, we need to get the SQL Server installation media onto the machine.  The easiest way I found to do this was to leverage Azure blob storage.

1. Upload the SQL Server ISO file to Azure blob storage
2. Remote Desktop (RDP) into the base VM
3. From the VM, download the SQL Server ISO file to the local disk
4. Mount the SQL Server ISO file to the VM
5. Copy the ISO contents (not the ISO file itself) to the VM’s C:\ drive.  For example, use C:\sql

The SQL Server installation media files need to be copied to the local C: drive so it can be used later to complete the SQL Server installation (when provisioning the actual SQL Server VM instance).

3. Run SQL Server setup to prepare an image

In order to prepare the (sysprep’d) SQL Server VM image (which we can use as a template for future VMs), we need to run the SQL Server installation and instruct it topreparean image – not run the full installation.  An easy way to do this is with a SQL Server configuration file, an example of which I’ve included below.

ConfigurationFile.ini

;SQL Server 2012 Configuration File
[OPTIONS]
; Specifies a Setup workflow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
ACTION="PrepareImage"
; Detailed help for command line argument ENU has not been defined yet.
ENU="True"
; Parameter that controls the user interface behavior. Valid values are Normal for the full UI, AutoAdvance for a simplified UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.
;UIMODE="Normal"
; Specifies setup not display any user interface.
;QUIET="False"
; Specifies setup to display progress only, without any user interaction.
QUIETSIMPLE="True"
; Specifies whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.
UpdateEnabled="True"
; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components.
FEATURES=SQLENGINE
; Specifies the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.
UpdateSource="MU"
; Displays the command line parameters usage
HELP="False"
; Specifies that the detailed Setup log should be piped to the console.
INDICATEPROGRESS="False"
; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.
X86="False"
; Specifies the root installation directory for shared components.  This directory remains unchanged after shared components are already installed.
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
; Specifies the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed.
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
; Specifies the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance.
INSTANCEID="MSSQLSERVER"
; Specifies the installation directory.
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

There are two steps in this process:

1. Copy the ConfigurationFile.ini file (from your local PC) to the same location as the SQL Server installation media (i.e.c:\sql) on the base VM.
2. Run SQL Server setup to prepare an image.  From a command prompt (on the base VM), navigate to theC:\sqlfolder and then execute the following command:

Setup.exe /ConfigurationFile=ConfigurationFile.ini /IAcceptSQLServerLicenseTerms=true

4. Configure Windows to complete the installation of SQL Server

At this point the base VM should have an “installation” of SQL Server that is not fully completed. The SQL Server bits are in place, but they’re not configured for a full server install . . . at least not yet. The final configuration of SQL Server will take place when the VM instance (of which this template/image is the base) is provisioned and boots up for the first time. This is accomplished by using a CMD file with the following content:

@ECHO OFF && SETLOCAL && SETLOCAL ENABLEDELAYEDEXPANSION && SETLOCAL ENABLEEXTENSIONS
REM All commands will be executed during first Virtual Machine boot
"C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\setup.exe" /QS /ACTION=CompleteImage /INSTANCEID=MSSQLSERVER /INSTANCENAME=MSSQLSERVER /IACCEPTSQLSERVERLICENSETERMS=1 /SQLSYSADMINACCOUNTS=%COMPUTERNAME%\Administrators /BROWSERSVCSTARTUPTYPE=AUTOMATIC /INDICATEPROGRESS /TCPENABLED=1 /PID="[YOUR-SQL-SERVER-PRODUCT-ID-HERE]"

1. On your local PC, save the file as SetupComplete2.cmd
2. RDP / log into the base VM
3. Copy the SetupComplete2.cmd from your local PC file to the c:\Windows\OEM folder on the base VM
4. Change the value for the SQLSYSADMINACCOUNTS value to be that of the administrative account created on the VM (or better yet – the local Administrators group account)
5. If needed, supply the SQL Server product ID (PID) value.

When Windows starts on the new VM instance for the first time, the SetupComplete2.cmd file should automatically run.  It is invoked by the SetupComplete.cmd file already on the machine.

5. Capture the image and add it to the Azure VM image gallery

At this point a base SQL Server VM has been created and the groundwork laid to complete the install. Now it is time to create the VM image from the base VM, and do to that you sysprep and capture the base VM.  Please follow the guide on How to Capture a Windows Virtual Machine to Use as a Template.

6. Create a new VM using the custom SQL Server image

With a new custom VM image template available in the VM image gallery, you can provision a new VM instance using that custom template.  Upon first boot, the newly provisioned VM should complete the full SQL Server installation as laid out in your SetupComplete2.cmd file.  Please follow the guide on How to Create a Custom Virtual Machine for more information on creating the VM from the template.

Closing Thoughts

One of the quirks I noticed when preparing the base SQL Server image is that it was not possible to prepare the image with SQL Server Management Studio (SSMS).  I would have to do the install after the newly provisioned VM instance is created. Not hard, but time consuming (an annoying if doing this on multiple VM instances).  I later learned that SQL Server 2012 Cumulative Update 1 does allow for preparing a SQL Server image with SSMS installed.  I’ve included a link below that describes the process for creating a SQL Server image with CU1.

In the end, this process really is not all that hard.  Time consuming?  Yes!  The worst part (at least for me) was really just understanding how the SQL Server installation and sysprep process works.  Once I wrapped my head around that, the process was a lot smoother.

Helpful Resources

While I was learning how to create a custom SQL Server VM image, the following resources were very helpful:

• How to: Create a Windows Azure Virtual Machine Operating System Image for Microsoft Dynamics NAV. This MSDN article provided the jumping off point on learning how to install SQL Server by using a sysprep image.
• Install SQL Server 2012 from the Command Prompt
• Install SQL Server 2012 Using a Configuration File
• Install SQL Server 2012 Using SysPrep
• How to create a slipstream SQL Server 2012 and Cumulative Update 1 image –http://sqlperformance.com/2012/12/system-configuration/sql-2012-slipstream

I would like to thank Scott Klein for his assistance in verifying these steps.  His help was extremely valuable to ensure I was doing this the right way.