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

June 8, 2010 8:26 am / Leave a Comment /

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

TSQLTuesday 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

    1. I used D:\Virtual Machines\SQLR2SysPrep
    2. 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
    1. I entered 1024 MB
  7. Configure Networking
    1. I selected “Local Area Connection 2 – Virtual Network”
  8. Connect Virtual Hard Disk
    1. 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
    1. I named it SQLR2SysPrep

Part 2: Prepare the Image

  1. Connect to the VM again
  2. Copy the SQL Server 2008 R2 installation files to the VM or make the file accessible over the network
  3. Start the installation running
  4. Install any pre-requisites if prompted
  5. When the Installation Center starts, select the Advanced tab

    Click to enlarge
  6. Click on “Image preparation of a stand-alone instance of SQL Server”
  7. Install the Setup Support Files and click Next
  8. Agree to licensing statement and click Next
  9. Select the features you want included in this image and click Next. You will notice that the list is very short.
  10. 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
  11. Confirm disk space requirements and click Next
  12. Check Image Rules and click Next

    Click to enlarge
  13. Confirm image configuration and click Prepare

    Click to enlarge
  14. When image preparation completes, click Next. On the next page, click Close

    Click to enlarge
  15. Log off of the VM
  16. Using Hyper-V Manager, shut the VM down
  17. Copy the VHD for the prepared VM image to where ever you store the prepared images

Part 3: Create New VM/Complete the Image

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

    1. I used D:\Virtual Machines\SysPrepSQL01
    2. I renamed the VHD to SysPrepSQL01.vhd
  2. Create a new VM the same way you did in part 1 using the newly copied VHD in step 29
  3. Start and connect to the new VM
  4. Rename the VM to the desired final name and reboot
    1. I renamed it to SysPrepSQL01
  5. Connect to the VM
  6. Navigate to and click on “CompleteSQL Server 2008 R2 Installation”
    1. Start -> All Progams -> Microsoft SQL Server 2008 R2 -> Configuration Tools
    2. Can also be started by starting the Installation Center and looking under the Advanced tab

      Click to enlarge
  7. Install the Setup Support Files
  8. Enter the Product Key or select a free edition
  9. Agree to the licensing terms
  10. 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 10

    Click to enlarge
  11. 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
  12. 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
  13. Verify the image rules and click Next

    Click to enlarge
  14. Verify the summary information and click Complete

    Click to enlarge
  15. When the image progress complete, click Next

    Click to enlarge
  16. On the next page, click Close

    Click to enlarge
  17. 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
  18. Run Installation Center again and install the missing features that you want
  19. 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.

Posted in: SQL Server / Tagged: , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation