Automating Image-Based Deployment of SQL Server on Azure IaaS VMs – Preparing SQL Server

In one our articles recently published on this forum, we described the process of deploying a Windows Server 2012 R2 into an Azure Infrastructure as a Service (IaaS) virtual machine using the latest gallery image. This was meant to constitute the first step of the process involving prestaging a setup of SQL Server 2014 from its installation media, generalizing the underlying operating system, and capturing the resulting system, yielding in the end an image that subsequently could be used to quickly provision custom-configured instances of SQL Server in Azure IaaS virtual machines.

We automated the first step by relying on Azure and Storage PowerShell cmdlets. Our virtual machine (Standard A2) includes four 50 GB data disks, which we used to construct a Storage Spaces-based, thin-provisioned simple (striped) volume (presented as the E: drive) intended for SQL Server binaries, system databases, and logs. We also enabled the Azure Files preview feature within our Azure subscription and created a storage account to temporarily store an image containing SQL Server installation media, which was afterwards downloaded to one of the local disks on our virtual machine in preparation for the SQL Server setup. Finally, we mounted the corresponding ISO file using the next available drive letter, which we preserved as the value of the $driveLetter PowerShell variable.

Now we are ready to initiate the SQL Server setup. Our process will be equivalent to the one described in the Install SQL Server 2014 Using SysPrep MSDN Library article, however, we will carry it out in an unattended manner. The intention is to start with the Image preparation of a stand-alone instance of SQL Server, which generates locally stored content, containing binaries and configuration settings necessary to provision a fully functional SQL Server installation following the reboot and the process referred to as Image completion of the prepared stand-alone instance of SQL Server.

In order to perform this first part of this procedure in the unattended fashion, we will leverage the option of specifying setup parameters from the Command Prompt, as described in Install SQL Server 2014 from the Command Prompt MSDN Library article. Incidentally, the same objective can be accomplished by including relevant parameters in a configuration file, which basic format and functionality are documented in the Install SQL Server 2014 Using a Configuration File also available from the MSDN Library. We will describe both options, but for the sake of simplicity, we will resort to the command line execution, which eliminates the need to perform another upload to Azure. However, since the configuration file is more descriptive, let’s start by reviewing its content. Our sample ConfigurationFile.ini resulting in the prestaged setup of a new stand-alone instance that includes the SQL Server Database Engine and Management Tools will take the following format. (Note that you can generate this file by stepping through the Installation Wizard while selecting options that reflect your requirements and saving the resulting selection once you reach the Ready to Install page):

; SQL Server Configuration File
[OPTIONS]
; Required to indicate the installation workflow, like INSTALL, UNINSTALL, UPGRADE, or PREPAREIMAGE (used in this case)
ACTION="PrepareImage"
; Applies to installation of the English version of SQL Server on a localized operating system, 
; when the installation media includes language packs for both English and the language corresponding to the operating system
ENU="True"
; Specifies that Setup runs in a quiet mode without any user interface. This is used for unattended installations.
QUIET="True" 
; Specifies that the detailed Setup log should be piped to the console. 
INDICATEPROGRESS="False" 
; 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,Tools
; Specify whether SQL Server setup should discover and include product updates. 
; By default, SQL Server setup will include updates that are found.
UpdateEnabled="True" 
; 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" 
; Specifies the root installation directory for shared components.  
; This directory remains unchanged after shared components are already installed. 
INSTALLSHAREDDIR="E:Program FilesMicrosoft SQL Server" 
; Specifies the root installation directory for the WOW64 shared components.  
; This directory remains unchanged after WOW64 shared components are already installed. 
INSTALLSHAREDWOWDIR="E:Program Files (x86)Microsoft SQL Server" 
; Specifies a SQL Server instance name. This is not allowed wehn the value of setting "ACTION" is "PrepareImage"
; INSTANCENAME="MSSQLSERVER" 
; 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="E:Program FilesMicrosoft SQL Server" 
; Accept the License agreement to continue with Installation
IAcceptSQLServerLicenseTerms="True"

The command line yielding the same outcome takes the following form:

Setup.exe /ACTION=PrepareImage /ENU=true /QUIET=true /FEATURES=SQLENGINE,Tools 
	/UpdateEnabled=false /INSTALLSHAREDDIR="E:Program FilesMicrosoft SQL Server" 	
	/INSTALLSHAREDWOWDIR="E:Program Files (x86)Microsoft SQL Server" 
	/INSTANCEID="MSSQLSERVER" /INSTANCEDIR="E:Program FilesMicrosoft SQL Server" 
	/IAcceptSQLServerLicenseTerms=true

Alternatively, you could also combine the two approaches, by referencing the configuration file with the /ConfigurationFile parameter and including any additional parameters, if needed, on the command line, for example:

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

And this concludes the second part of the process leading to creating a SQL Server 2014 custom Azure image. We will continue this topic in our upcoming articles published on this forum by presenting how to automate the procedure described above by using Windows PowerShell Remoting.

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