SQL Server 2008 R2 Prepare Image Cook Book

This article walks you through the steps to prepare and complete an image installation of SQL Server 2008R2.  I am assuming you’ll be doing this in a virtual machine so that you can make copies of it for development and test.

If you are running your virtual machine on Windows Server 2008R2 you should install the application role which is the simplest way to install the .net framework 3.5 .

To prepare a SQL Server image from the command line you run setup with the following switches..

SETUP

/QS

/IAcceptSQLServerLicenseTerms

/ACTION=PrepareImage

/FEATURES=SQLENGINE,REPLICATION,FULLTEXT,RS

/INSTANCEID=“INSTANCEID”

Notes

    • This command should all be on one line I have split it out for clarity
    • /IACCEPTLSQLSERVERLICENSETERMS is a new mandoatory switch for ALL types of command line installs of SQL Server 20087 R2.
    • These are just the mandatory commands for a full list refer to this section of books on line.
    • InstanceID is not the InstanceName which you can specify when you complete the image, but you need to know what it is so you can enter it for that step
    • The example above includes all of the feature you can prepare an image for i.e. you cannot do this for analysis services or integration services

Having done that you can then sysprep the box ..

windows\system32\sysprep\sysprep.exe

Check the generalize box and select shutdown.

Once you have done this in a virtual machine you can make copies of it as you need to.

To use one of the copies..

Start the VM and you’ll see it go into the out of box install i.e. you’ll be asked to confirm the locale and enter the license agreement. 

You will probably want to rename the virtual machine (the initial boot will give it  a random name) and optionally you may wish to join the virtual machine to a domain.

You can now complete the SQL Server installation with this

Setup

/QS

/ACTION=CompleteImage

/INSTANCEID=“from prepare image step e.g.TechNet”

/INSTANCENAME=“MyInstance”

/SQLSVCACCOUNT=“domain\sql service account"

/SQLSVCPASSWORD=“*******"

/RSSVCACCOUNT=“domain\sql service account"

/RSSVCPASSWORD=“*******"

/SQLSYSADMINACCOUNTS="domain\sysadminuser"

/AGTSVCACCOUNT= =“domain\sqlagent service account"

/AGTSVCPASSWORD=“******“

/IACCEPTSQLSERVERLICENSETERMS

/BROWSERSVCSTARTUPTYPE=AUTOMATIC

/SECURITYMODE=SQL

/SAPWD=“******"

/SQLSYSADMINACCOUNTS=“domain\user"

/TCPENABLED=1

Notes:

  • SAPWD is mandatory if you select mixed mode security (/SECURITYMODE=SQL)
  • I have optionally set  /BROWSERSVCSTARTUPTYPE to automatic to make instances discoverable on multi instance installations
  • I have optionally set /TCPENABLED=1 to allow remote access over TCP/IP
  • the /RSSVCACCOUNT and /RSSVCPASSWORD are mandatory because I optionally selected reporting services when I prepared the image.
  • This instance will need to be remotely managed as you cannot install the client tools as part of this process