Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted July 6, 2015

WEBINAR: On-demand Event

Replace Oracle with the NoSQL Engagement Database: Why and how leading companies are making the switch REGISTER >

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

By Marcin Policht

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 Files\Microsoft SQL Server" /INSTALLSHAREDWOWDIR="F:\Program Files (x86)\Microsoft SQL Server" /INSTANCEID=MSSQLSERVER /INSTANCEDIR="F:\Program Files\Microsoft 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 Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\setup.exe" /QS 
	/IAcceptSQLServerLicenseTerms=true /SQLSYSADMINACCOUNTS=%COMPUTERNAME%\Administrators 

We invoke the setup from the location referencing the value of the /INSTALLSHAREDDIR parameter (i.e. F:\Program Files\Microsoft 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:WinDir\OEM)) {
		New-Item -ItemType directory -Path $env:WinDir\OEM
	Set-Content -Path $env:WinDir\OEM\SetupComplete2.cmd `
		-Value '"F:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2014\setup.exe" /QS 
		/IAcceptSQLServerLicenseTerms=true /SQLSYSADMINACCOUNTS=%COMPUTERNAME%\Administrators 

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%\System32\sysprep 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:WinDir\System32\sysprep\sysprep.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

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM