A recent requirement was to create a template within VMware to deploy a configured virtual machine with a named Database Engine instance of SQL Server 2012 installed. Previous versions of SQL Server did not allow for a clean sysprep image, in SQL Server 2012 this has changed with the introduction of the concept image preparation and image complete of stand-alone instances of SQL Server. So it looks like its time to through away those configuration scripts for previous automated deployments of SQL Server 2005 and 2008!
So how did I go about this…
I currently have a base template of Windows 2012 Server Datacenter Edition, which I will deploy a virtual machine to configure as my template for SQL Server installations, also prior to beginning this process, I have created an additional hard disks for my virtual machine to host the database files.
Now for the configuration steps to create the SQL Server template. Firstly we need to prepare a stand alone instance of SQL Server.
1) Mount the installation media in the CD/DVD drive (or copy the contents of the media to an installation folder) and begin the setup process.
2) From the SQL Server Installation Center, select Advanced.
3) Select Image preparation of a stand-alone instance of SQL Server.
4) Select product updates available which will be installed during the SQL Server features setup.
5) Select the features you want to prepare, currently only Database Engine and Reporting Services features can be selected.
6) Configure your Instance ID and Instance root directory.
7) The SQL Server features will now be ready to be included in the prepared image, this will create a configuration file at ‘C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\YYYYMMDD_HHMMSS\ConfigurationFile.ini’.
8) Select Prepare to complete the Image process. If prompted restart the computer, to complete the installation before capturing your image.
Once the prepare image is complete and you have configured the virtual machine as per the requirements for your template, capture the Sysprep image and convert the virtual machine to a template.
Now, we are going to deploy the virtual machine from the template captured in the above steps, in this instance I use a customization wizard to configure a number of settings.
Once the customization wizard has completed, and the deployed virtual machine is available, we now need to complete the configuration of a the prepared imaged instance of SQL Server.
1) Begin the setup process from either the mounted installation media or from the installation folder.
2) Select Advanced.
3) Select Image completion of a prepared stand-alone instance of SQL Server.
4) Enter the product key
5) Select the ID of the prepared instance to complete and review the features in the prepared instance.
6) Specify the instance name for the prepared instance.
7) Specify the service accounts and the collation configuration
8) Specify the authentication mode, administrators and data directories.
9) The SQL Server features will now be ready to be configured and completed, this will create a configuration file at ‘C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\YYYYMMDD_HHMMSS\ConfigurationFile.ini’.
10) The image process will now be completed. If prompted restart the computer, to complete the installation before capturing your image.
Obviously, I do not want to perform this attended completion of a prepared stand-alone instance of SQL Server following each deployment of the template. This is where the configuration file created to complete the configuration file will allow for this to be run once following deployment.
I will run a script to run once on deployment, to complete the configuration of the prepared image instance of SQL Server as below:
Setup.exe /ACTION="CompleteImage" /QS /INSTANCENAME="SQL12" /INSTANCEID="SQL12" /AGTSVCACCOUNT="NT Service\SQLAgent$SQL12" /AGTSVCSTARTUPTYPE="Automatic" /SQLSVCSTARTUPTYPE="Automatic" /SQLCOLLATION="Latin1_General_CI_AS" /SQLSVCACCOUNT="NT Service\MSSQL$SQL12" /SQLSYSADMINACCOUNTS="Domain\Group" /SECURITYMODE="SQL" /SQLBACKUPDIR="D:\Backup" /SQLUSERDBDIR="D:\Databases" /SQLUSERDBLOGDIR="D:\Logs" /SQLTEMPDBDIR="D:\TempDB" /ADDCURRENTUSERASSQLADMIN="False" /TCPENABLED="1" /NPENABLED="0" /BROWSERSVCSTARTUPTYPE="Automatic"
Once the script has been run as above, a configurable instance of SQL Server will be available.
So based on the previous versions of SQL Server and the steps required post and pre deployment of a SQL Server template the above steps are simple to use and reduce the deployment time for new instances of SQL Server.
Deans Blog 
Hi there, I’m interested in doing this for SQL 2012 SP2 standard – Can you elaborate on this step:
“Once the prepare image is complete and you have configured the virtual machine as per the requirements for your template, capture the Sysprep image and convert the virtual machine to a template.”
Are you talking about windows sysprep here? Or are you just saying that after you go through the SQL sysprep, save your vm as a template (no windows sysprep needed)?
Also, our sql servers are joined to our domain, will that cause any issues with this process?
LikeLike
In terms of the prepare image step I am referring to the process of preparing one or more unconfigured instances of SQL Server, capturing the sysprep image refers to preparing the Windows installation image.
There should be no issues joining the SQL server to the domain using this process…
LikeLike
Hi there, very good article. Though i’m having trouble understanding where do you put your key when using command line script ? Thank you !
LikeLike
Hi Gregor, you should be able to specify the switch /PID=”” and your volume key in addition to the above syntax.
LikeLike
Thanks for your helpful article. However, I don’t seem to have the “Advanced” option in the SQL Server Installation Center. Is this because I’m installing SQL on a Windows 8.1 OS instead of on a proper Server OS?
LikeLike