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
31. Start and connect to the new VM
32. Rename the VM to the desired final name and reboot
a. I renamed it to SysPrepSQL01
33. Connect to the VM
34. Navigate to and click on “CompleteSQL Server 2008 R2 Installation”
a. Start -> All Progams -> Microsoft SQL Server 2008 R2 -> Configuration Tools
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.
