Automating Image-Based Deployment of SQL Server on Azure IaaS VMs – Completing the Process

In our recently published article, we presented a script that provisions a new Azure Infrastructure-as-a-Service (IaaS) virtual machine running Windows Server 2012 R2 and prestages a SQL Server 2014 default instance. Effectively, once you capture the virtual disks containing the operating system and the database engine setup files (which can be easily accomplished either directly from the Azure Management Portal or by using Azure PowerShell module cmdlets), you will be able to deploy any number of new systems based on the resulting image. Note, however, that anyone taking advantage of such a solution would still need to manually initiate the second part of the SQL Server setup (by invoking the CompleteImage action) once a new, image-based virtual machine is brought online. Let’s see how we can eliminate this last remaining manual step in our procedure and finalize the capture.

As a reminder, the original virtual machine used to generate the image hosts multiple data disks (whose number we control by using the $numberOfDisks variable in our script), striped into a single volume (to which assigned the letter F) constructed by leveraging the Storage Spaces functionality present in Windows Server 2012 R2. It also stores a copy of an ISO file containing SQL Server 2014 setup media mounted as the Z drive. We used it to run setup.exe in order to prestage the installation by setting the /ACTION parameter to PrepareImage (along with the number of others, including /ENU=true /QUIET=true /FEATURES=SQLENGINE,Tools /UpdateEnabled=false /INSTALLSHAREDDIR=”F:Program FilesMicrosoft SQL Server” /INSTALLSHAREDWOWDIR=”F:Program Files (x86)Microsoft SQL Server” /INSTANCEID=MSSQLSERVER /INSTANCEDIR=”F:Program FilesMicrosoft SQL Server” and /IAcceptSQLServerLicenseTerms=true).

In order to trigger completion of the setup once a new virtual machine based on the image is deployed, we will follow the approach referenced in MSDN that describes the process of installing SQL Server by using sysprep (which incidentally mirrors the technique used to handle provisioning of standard Azure IaaS virtual machines). This approach relies on the functionality inherent to the operating system, which automatically invokes excecution of the file named SetupComplete2.cmd residing in the %WinDir%OEM folder once the generalized image goes through the out-of-box experience (OOBE) stage. In our case, this file will contain a call to setup.exe with a number of parameters (including /ACTION=CompleteImage), necessary to complete the installation of the SQL Server 2014 instance. More specifically, the file will have (as per SetupComplete2.cmd File Example) the following content (note that the setup.exe command and its parameters should appear on the same line and we present them on multiple lines strictly for readability reasons):

"F:Program FilesMicrosoft SQL Server120Setup BootstrapSQLServer2014setup.exe" /QS 
	/ACTION=CompleteImage /INSTANCEID=MSSQLSERVER /INSTANCENAME=MSSQLSERVER 
	/IAcceptSQLServerLicenseTerms=true /SQLSYSADMINACCOUNTS=%COMPUTERNAME%Administrators 
	/BROWSERSVCSTARTUPTYPE=DISABLED /INDICATEPROGRESS /TCPENABLED=1 

We invoke the setup from the location referencing the value of the /INSTALLSHAREDDIR parameter (i.e. F:Program FilesMicrosoft SQL Server) we used when performing the PrepareImage action prior to the image capture. We also specify the matching name of the SQL Server instance (with the /INSTANCEID and /INSTANCENAME parameters), automatically accept the licensing terms (by setting /IAcceptSQLServerLicenseTerms to true), disable the SQL Server Browser service (as designated by /BROWSERSVCSTARTUPTYPE=DISABLED), assign the System Admin role to the local Administrators group (/SQLSYSADMINACCOUNTS=%COMPUTERNAME%Administrators), and enable TCP/IP protocol (/TCPENABLED=1).

In order to create this file in an Azure virtual machine, we will rely on PowerShell Remoting functionality. We can incorporate the code into our existing script by adding an extra script block, which gets executed via the Invoke-Command cmdlet (note that we take advantage on the Install-WinRmCertificate helper function, which populates the $winRMUri PowerShell variable) as well the operating system-level administrative credentials (stored in the $credential PowerShell variable).

Invoke-Command -ConnectionUri $winRmUri.ToString() -Credential $credential -ScriptBlock {
	If(!(Test-Path -Path $env:WinDirOEM)) {
		New-Item -ItemType directory -Path $env:WinDirOEM
	}
	Set-Content -Path $env:WinDirOEMSetupComplete2.cmd `
		-Value '"F:Program FilesMicrosoft SQL Server110Setup BootstrapSQLServer2014setup.exe" /QS 
		/ACTION=CompleteImage /INSTANCEID=MSSQLSERVER /INSTANCENAME=MSSQLSERVER 
		/IAcceptSQLServerLicenseTerms=true /SQLSYSADMINACCOUNTS=%COMPUTERNAME%Administrators 
		/BROWSERSVCSTARTUPTYPE=DISABLED /INDICATEPROGRESS /TCPENABLED=1'
}

Before we can capture the image, we need to also prepare in an equivalent mannner the operating system of the virtual machine. This is done by running the sysprep.exe utility (residing in the %WinDir%System32sysprep folder) with the /generalize and /oobe switches and shutting it down afterwards, which can be accomplished in a single step by running:

Invoke-Command -ConnectionUri $winRmUri.ToString() -Credential $credential -ScriptBlock {
$sysprep = "$env:WinDirSystem32sysprepsysprep.exe"
$parameters = "/generalize /shutdown /oobe"
$cmdLine = $sysprep + $parameters
Invoke-Expression -Command:$cmdLine
}

This will generalize the operating system (with a prestaged SQL Server installation) and shut it down afterwards. At that point, you can capture it directly from the Azure Management Portal, by clicking on the Capture button in the Command Bar at the bottom of the virtual machines page (with the target virtual machine selected). This will display the Capture the Virtual Machine dialog box, where you need to assign an arbitrary name to the image and enable the I have run Sysprep on the virtual machine checkbox (as described in the Microsoft Azure Documentation). Alternatively, you can use the Save-AzureVMImage cmdlet of the Azure Powershell module for this purpose. The cmdlet requires providing the name of the virtual machine (as the value of the -Name parameter) and the corresponding cloud service (ServiceName), specifying a name to be used by the resulting image (ImageName) along with its label (ImageLabel), as well as including the OSState parameter with its value set to Generalized. For example, assuming that our virtual machine is named VMSQL1 and it resides in the CloudService1, then the cmdlet would take the form of Save-AzureVMImage -ServiceName CloudService1 -Name VMSQL1 -NewImageName SQLImage1 -NewImageLabel ‘Custom SQL Server 2014 Image’. Note that this will effectively delete the existing virtual machine and convert it into an image residing in the same storage account. Once this step completes, you will be able to deploy new virtual machines based on this custom image.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles