Problem solve Get help with specific problems with your technologies, process and projects.

Sysprep utility speeds up SQL Server 2008 R2 deployments

A Windows staple for years, the Sysprep tool has finally made its way over to the SQL Server world with the release of R2.

The release of SQL Server 2008 R2 brings us a feature unheard of in the SQL Server world – the ability to deploy an image of a server with SQL Server already installed. This is done using a feature that has been available through Windows for many years called Sysprep.

Sysprep allows the administrator to remove all the accounts, computer names and security identifiers (SID) from a computer and create an image. This image can then be deployed to physical or virtual machines. When the new machine boots up, it asks for various pieces of information such as the computer and Windows domain name and the networking settings – much of which can be configured as part of the Sysprep process.

If done correctly, after the image is deployed the only question that needs to be asked when the new machines boots up is, “What’s the computer’s name?” Every other setting can be preconfigured via the Sysprep process.

With SQL Server 2008 R2 we can now pre-configure the SQL Server components on the base image, allowing the SQL Server to be configured via Sysprep after the machine has been deployed. You’ll probably find this most useful when deploying virtual machines that have SQL Server on them, as this is where the bulk of Sysprep-oriented servers are being deployed.

Sysprep step-by-step

A SQL Server 2008 R2 installation is broken down into two separate processes: preparing the image and completing the image. During the first step the binaries are installed, while during the second step the computer, network and account-specific information are configured. When installing SQL Server normally, both of these steps are completed by the installer. On the other hand, when using Sysprep to deploy an image with SQL Server on it, only the prepare step is completed. The complete step is done by the Sysprep process after the image has been deployed to the new machine.

When deploying an image with Sysprep, not all of the SQL Server components can be installed. You can only install the SQL Server database engine and the Reporting Services components. The SQL Server Browser and SQL Server Writer services will be prepared automatically when the instance is completed.

In order to install a SQL Server instance using Sysprep, launch the SQL Server 2008 R2 installer like normal, but instead of choosing the Installation menu option on the left, choose the Advanced option. Then select the “Image preparation of a stand-alone instance of SQL Server” option on the right. This will open a shortened wizard that will ask you which services to install and where to put them. You will notice that when you get to the feature selection page there are a few less options, as you can only select from the database engine, SQL Server replication, full-text search and Reporting Services. Any other features you wish to set up need to be installed once the image has been deployed.

After selecting the features you wish to install, you’ll be able to configure the instance name and instance path. You’ll then move through a couple more screens while you verify there is enough drive space and that you have set your installation options correctly. At this point, the installer will install the binaries to the server and exit.

Once the installer has exited, the machine can be configured with the Sysprep tool. While you can’t choose to install the Browser, Writer or SQL Native Client, they will be installed automatically once the instance is complete.

There are two ways to complete the installation once the image has been deployed. First, you can follow this path:

Start menu > Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > “Complete SQL Server 2008 Installation”

The other option is to launch the installer from the SQL Server 2008 R2 DVD, navigate back to the Advanced tab and click the “Image completion of a prepared stand-alone instance of SQL Server” option on the right side. In either case you’ll effectively be walked through the rest of the installation wizard to complete the installation.

When you install using non-express SQL Server 2008 R2 media, the Evaluation Edition is used. This mandates a product key during the complete phase of the process. Note that if you select the Evaluation Edition, the timer will be set to expire 180 days after the wizard is completed.

The shortcomings of Sysprep

There are some specific limitations to using Sysprep to install SQL Server 2008 R2:

  • SQL Server cannot be installed on a failover cluster.
  • Tools cannot be installed via the Sysprep process.
  • Computers that use Itanium processors do not support the Sysprep process.
  • Repairing an instance that is prepared is not supported, so if the setup process fails the instance must be uninstalled and reinstalled.
  • If an instance is installed that is older than Microsoft SQL Server 2008 R2, you cannot prepare the instance via Sysprep. Install the older instances after the instances that have been installed are completed.
  • 32-bit editions cannot be installed on a 64-bit server using the Windows on Windows (WOW) 64 feature when preparing the server for deployment using Sysprep.
  • If you need to uninstall the instance either before or after the completion process has taken place, it can be completed through the Program and Features icon within the control panel.

That’s about it. As you can see though, using the Sysprep deployment feature can greatly increase the speed with which new SQL Server 2008 R2 servers can be deployed in either physical or virtual environments.

Denny Cherry has over a decade of experience managing SQL Server, including's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. Check out his blog at SQL Server with Mr. Denny.

Dig Deeper on Microsoft SQL Server Tools and Utilities

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.