T-SQL Tuesday # 07: Walkthrough for Sysprep in SQL Server 2008 R2

This blog entry is participating in T-SQL Tuesday #007, hosted this month by Jorge "@SQLChicken" Segarra (Blog|Twitter). You are invited to visit his blog to join the party and read more blogs participating in this month’s theme: Summertime in the SQL. This month’s theme is all about the new hotness (i.e., the hot new features of SQL Server 2008 R2). For my part in this month's TSQL2sday, I'm going to talk about SysPrep.

One of the long desired features that many administrators have wanted is finally here. SQL Server 2008 R2 is the first version of SQL Server to support sysprep for a server image. Sysprep is technology that has been used for years by Windows administrators to create a base image of the operating system that can be quickly rolled out to new machines. This can be done for physical hardware, but it has really gained in popularity with the rise of virtualization. Nobody wants to take 4+ hours to create a new virtual machine. Once you have the base image created, you should be able to have a new VM up and running in a matter of minutes.

The SQL Server admins have been clamoring for sysprep support. Finally it is here. But does it live up to the expectations? Does it enable you to have a new SQL Server VM up and running in a matter of minutes?

I attempt to answer these questions in this walkthrough. I’m going to talk a little about how it works and give my opinion on the usefulness and robustness of the feature. The main point of this article; however, is to provide a simple walkthrough of how to use the feature.

Hardware/Software Specifications

For this walkthrough, I will be using Windows 2008 R2 x64 Enterprise Edition and Hyper-V. I want to take a moment first and thank my colleague Jose Rivera who manages the lab environments for the application for which we both perform operations duties. Thanks Jose for loaning the use of one of your Hyper-V hosts in the lab to me!!

VM Host:

OS: Windows 2008 R2 x64 Enterprise Edition

Processor: Intel Xeon E5345@2.33GHz (2 quad core = 8 total CPU’s)

RAM: 32 GB

The Walkthrough

This will be a fairly simple and straightforward walkthrough. There will be three parts to this process: create a new VM using an existing VHD of a Windows Server 2008 R2 server with no SQL installation, perform the steps of preparing a sysprepped image of SQL Server 2008 R2, and create a new VM from this image with SQL Server 2008 R2.

Part 1: Create a New VM

1.Copy the existing VHD to the location where I want the VM to be located

a.I used D:\Virtual Machines\SQLR2SysPrep

b.I renamed the VHD to SQLR2SysPrep.vhd

2.Open Hyper-V Virtual Machine Manager

3.Create a new Virtual Machine

4.Skip the first page of the dialog

5.Specify a name for the VM and the location where I put the VHD in step 1

Click to enlarge

6.Assign memory for the VM

a.I entered 1024 MB

7.Configure Networking

a.I selected “Local Area Connection 2 – Virtual Network”

8.Connect Virtual Hard Disk

a.Use the existing VHD you copied in step 1

9.Check the Summary to make sure everything looks okay and Click on the Finish button

Click to enlarge

10.Start the VM and connect to it

Click to enlarge

11.Rename the instance to the desired name and reboot

a.I named it SQLR2SysPrep

Part 2: Prepare the Image

12.Connect to the VM again

13.Copy the SQL Server 2008 R2 installation files to the VM or make the file accessible over the network

14.Start the installation running

15.Install any pre-requisites if prompted

16.When the Installation Center starts, select the Advanced tab

Click to enlarge

17.Click on “Image preparation of a stand-alone instance of SQL Server”

18.Install the Setup Support Files and click Next

19.Agree to licensing statement and click Next

20.Select the features you want included in this image and click Next. You will notice that the list is very short.

21.Enter an Instance ID. Note that this is not the Instance name that will be used in the final install. This is merely to identify the prepped image in case you have multiple instances prepped and click Next

Click to enlarge

22.Confirm disk space requirements and click Next

23.Check Image Rules and click Next

Click to enlarge

24.Confirm image configuration and click Prepare

Click to enlarge

25.When image preparation completes, click Next. On the next page, click Close

Click to enlarge

26.Log off of the VM

27.Using Hyper-V Manager, shut the VM down

28.Copy the VHD for the prepared VM image to where ever you store the prepared images

Part 3: Create New VM/Complete the Image

29.Copy the VHD you created to the location where I want the new VM I am going to create to be located

a.I used D:\Virtual Machines\SysPrepSQL01

b.I renamed the VHD to SysPrepSQL01.vhd

30.Create a new VM the same way you did in part 1 using the newly copied VHD in step 29

b.Can also be started by starting the Installation Center and looking under the Advanced tab

Click to enlarge

35.Install the Setup Support Files

36.Enter the Product Key or select a free edition

37.Agree to the licensing terms

38.Select the Instance ID of the prepared image you want to complete and click Next. This would be the same Instance ID you entered in Part 2, step 21

Click to enlarge

39.Verify the features you are installing and click Next. This will display the features selected in Part 2, step 20. You cannot change the features at this point. The importance of this step is if you had multiple instances to complete with different features for each, this would help you verify that you are completing the correct image

Click to enlarge

40.Configure the instance to install as and click Next. This is where you define if it is the default instance or a named instance.

Click to enlarge

41.Verify the image rules and click Next

Click to enlarge

42.Verify the summary information and click Complete

Click to enlarge

43.When the image progress complete, click Next

Click to enlarge

44.On the next page, click Close

Click to enlarge

45.Check the program files and see your installation. At this point, it is installed, but because the feature set is so limited, you don’t have SSMS, client connectivity, Books Online or many other features

46.Run Installation Center again and install the missing features that you want

47.Connect to your completed instance

Click to enlarge

Conclusion

The sysprep functionality required a lot more work to complete the image than I had hoped. My desire was for the image completion to be quick and simple. There really isn’t a lot of time saved by sysprepping the image. And since many of the components cannot be sysprepped, if I want more components installed, like Books Online or the client tools or Analysis Services, I have to re-run the installation wizard a second time to get everything I need. In these cases, it would seem that sysprepping the image first actually ended up being more work after the fact and not less.

I think sysprep has a long way to go before we see wide adoption of this technology.