SQL 2008 R2 brings among many of its new features, the ability to sysprep a SQL installation. Now as we know SQL installation can be quite boring and time consuming. Combine the SysPrep preparation with unattended SQL installation and you have a completely automated process to go about doing it.

Playground

  1. Windows 2008 R2 Active Directory
  2. Base Windows 2008 R2 Server for SQL
  3. SQL 2008 R2 Enterprise Version

Pre requisites

One of the pre requisites that would be required before starting the installation of SQL 2008 R2 server is the installation of .net 3.5 as a feature on the server. This can be either completed using Server manager or by running the following simple lines of PowerShell Codes

Part 1:

1
Set-ExecutionPolicy Remotesigned

Press “Y” to allow for remote scripts to be executed on the server.

Part 2:

1
2
3
4
5
#Script to enable .net 3.5 feature for Windows 2008 Server
Write-Host -ForegroundColor Blue "Importing Modules for managing the server features"
Import-Module ServerManager
Add-WindowsFeature as-net-framework
Write-Host -ForegroundColor Blue "Script has completed execution. .net Framework 3.5 has been enabled on this server"



SysPrep SQL 2008 R2 Server

At this stage the blank Windows 2008 R2 server will be staged to be sysprep with SQL server 2008. The server may or may not be domain joined and would need its FQDN, IP addresses setup at a later stage.

Depending on the type of media (DVD, ISO, Network Share, Local Share) run the setup.exe to kick-start the SQL server installation.

  1. Under the advanced TAB click on “Image Preparation of a standalone – Instance of SQL Server”

  1. Accept the license agreement and install the support files

  2. Once the Support Rules check has completed click next
  3. Select the instance features that need to be enabled on the SQL Server Image. (Note: not all features are displayed here. They can be installed later)

  4. Provide a name to identify the SysPrep Instance ID

  5. The Final Summary page displays all the settings that have been selected to be prepared onto the server. (Note: Save the configuration file as it can be used to automate the above steps in the future using command line setup of SQL)
  6. Click on Prepare to start the process

This process shouldn’t take more than 10 minutes to complete.

At this point, the SQL server template is ready to be used for deploying SQL servers as and when required. If it is a Virtual Machine, either clone the machine to a new VM or create a VM template to be used to deploy a SQL Server.

Deploying a SysPrep SQL 2008 R2 Server

At this stage all the backend work that needs to be done to the Server can be completed, such as providing the server with a FQDN, changing network settings. For the sake of this post, the VM has been cloned and the new VM will be domain joined and provided with a FQDN that is completely different from the name of the original SysPrep server.

  1. Once all the steps that are required for setting up the server have been completed, log back onto the server with a local administrator / domain account privileged with the right permissions.
  2. Restart the setup process by opening setup.exe and browsing to the Advanced TAB and clicking on “Image completion of a prepared stand-alone instance of SQL Server”

  3. Click Install for the Setup Files
  4. Proceed through the Product Key, and agree to the license agreement and choose the SYSPREP Instance ID that were created in the previous process.

  5. Provide a Instance Name for the SQL server instance

  6. Provide the SQL administrator account, setup the locations for all the *.mdf, *.log files

  7. Complete the process to start the install process to complete

  8. This will kick-start the install process

  9. The whole process was completed in less than 10 minutes

To add new Features for the SQL Server Instance

One of the features that would be required and is not installed by this process is the SQL Management Studio.

  1. Start the installation process and click on the Installation Tab and choose to add new features

  2. Choose the right instance for which the new features need to be installed onto

  3. For the sake of example, Management studio will be chosen as the new feature to be added onto the installed instance of SQL

  4. Summary of all the features that will be installed

  5. Install will continue to display the progress

This completes most of the processes that are involved with setting up a SYSPREP SQL installation. One of the main points to be taken away is the amount time that this will be saving setting up SQL compared to previous versions. As most of the summary pages before clicking install shows the location of the “Configuration File” using the same would help reduce the time even further by having preconfigured configuration files. Follow this link Unattended Installation of SQL 2008 R2