Installing SQL Server can be tedious and boring, and now that we live in a virtual world surely there’s a better way to give developers, application managers and even tech savvy business users theSQL love without the putting your self through installation hell each time?

I imagine some of you have resorted to copying, renaming the server and then hacking around SQL Server to get it recognise the change of name, but there is a cleaner and supported way of doing this called image preparation. Essentially you do a pre install of the features of SQL Server you want, sysprep the machine and then use this as a template for as many installations as you need.  For each copy you then fire it up join it whatever domain and infrastructure you need and then run a script to complete the SQL Server installation. 

If you aren’t familiar with sysprep, it makes an installation of windows anonymous, it is no longer has a name, or more importantly an SSID ,( the way each machine is uniquely identified to a domain). So sysprep allows virtual machines to be copied and then each copy can be joined to the same domain.  

Creating a template like this makes even more sense if you use windows server core as it will take up less space, and the original template will need less patching, (as I covered in part 1 of this series).

In the accompanying video demo,

 

  • I have installed SQL Server with the prepare image installation action:

SETUP

/QS

/ACTION=PrepareImage

/FEATURES=SQLENGINE,FULLTEXT

/INSTANCEID=MSSQLServer

/IAcceptSQLServerLicenseTerms

  • sysprepped the virtual machine
  • restarted it and joined it to  a domain (part 2 of this series covers this in more detail)
  • finished the SQL Server installation with the complete image installation action:

SETUP

/QS

/ACTION=CompleteImage

/INSTANCEID=MSSQLServer

/INSTANCENAME=MSSQLServer

/SQLSVCACCOUNT=[domain\account]

/SQLSVCPASSWORD=[password]

/SQLSYSADMINACCOUNTS=[domain\account]

/AGTSVCACCOUNT="NT AUTHORITY\Network Service"

/IAcceptSQLServerLicenseTerms

/TCPENABLED=1

Image preparation does look like a lot of extra work the way I have shown it here,but in reality the process of configuring it post sysprep would be fully scripted, and handled by System Center, specifically Virtual Machine Manager, possibly in combination with Service Manager to handle the request for a new SQL Server and Orchestrator to handle the workflow. This final step is exactly what the private cloud is all about, and so I do want to make a final video to show this in action, but I want to wait until the System Center 2102 lineup is all released to beta in the autmn.

 

Notes:

  • Sysprep doesn’t remove settings or patches, so the SQL Server firewall rule (to open TCP port 1433) I configured in part 2 is still there so I can connect to it remotely.
  • You’ll need an evaluation copy of Windows Server 2008 R2 (this has sp1 included) for this if you haven’t got a TechNet subscription.
  • You'll also need SQL Server Denali ctp 3
  • The video is part 4 of a series and I have put links on them so you can watch them in sequence to get SQL Server working core as quickly as possible. However there will be accompanying posts and notes to help as well .