I just put together and delivered my first consolidation and virtualization class for SQL Server based on Windows Server 2008. One of the technology labs is building a standalone SysPrep’d server that has both Windows Server 2008 R2 and SQL Server 2008 R2 (currently still in pre-release form).

A bit of history: up until now, there really was no way to have a SysPrep’d Windows system with SQL Server on it. In fact, the wasn’t supported for that combo, and there were numerous issues if you tried to do it. All of this is pretty well documented. With the release of SQL Server 2008 R2, there is now a way to SysPrep so that you can essentially have a way of creating a SQL Server that can be partially installed earlier, and then finished later.

This is different than the advanced cluster preparation install which is for failover clustering and introduced with SQL Server 2008. In fact, you cannot Sysprep a cluster at all, so if that’s what you want to do, sorry. Standalone servers only. SysPrepping a cluster would need support both at the Windows and SQL levels, and it’s not there for either. Once it’s in Windows to SysPrep a base cluster, SQL can come up with a way to support it. I don’t see either happening soon.

The concept of having a Sysprep server makes sense: create a base image with your configuration, and then use that base which can then be “brought to life” during the loading of the image (or the ISO burned from the image). This ensures consistent builds which is a good thing. One of the problems I often help DBAs with is ensuring that the Windows builds (both standalone and clusters) they get to put SQL on isn’t a bed of quicksand. So many problems that happen with SQL Server stem from a bad configuration of hardware or underlying software which seemingly has nothing to do with SQL Server. Different rant for another day; just stating facts here.

In this day and age of virtualization, it makes even more sense to have a base image you can then deploy a million times. Just create your image, save it as a template VM, and away you go!

Let me say this before I go further: the way SQL Server implemented SysPrep in the SQL Server 2008 R2 installer works, and it works as advertised. But I feel in this version they did not go all the way.  What do I mean by that?

The way the feature is implemented in SQL Server 2008 R2, you can only Sysprep the Database Engine and Reporting Services – not Analysis Services, nor the tools. My problem stems from not doing the tools (i.e. SSMS). When I build a SysPrep’d image, I want it to have everything, and then I’ll bring SQL to life (i.e. completion of the image) to wrap things up. To me, that would mean when that process is done, I also have the tools to do the job. You can get there, but it’s more work – in my opinion – than it should be. Here’s the process at a high level of what you have to do to have a “complete” image that is Sysprep’d:

1. Create the base OS image and make sure it is left open; don’t seal it yet.
2. Install the SQL Server tools.
3. Run the image preparation for SQL Server 2008 R2.
4. Seal the Windows image.

At this point, you’ve got a usable image that can be used. When you load the image onto the new server, you’ll have the process of bringing the server to life, which will include finishing the SQL Server installation. The process is pretty quick. However, the difference is that the process I outline above gives you a usable server. If you just Sysprep the server, you’d have to load the tools after the fact, which to me defeats the whole purpose.

I still think it’s a great feature in SQL Server 2008 R2, and something that many of my customers have been asking to have for years. Remember, SQL isn’t always built by DBAs, so the more that the experience can be integrated with Windows and be stable, it makes for better SQL Server implementations. It isn’t for everyone, but it works just fine if you account for what I talk about above (which is also documented – see the link below) to ensure you have a full image for SQL Server.

Tip
Use the generalize option (either GUI or command line) for the Windows Sysprep process to ensure that each finalized server will get its own SID, new Event Logs, etc. Also use the Out Of Box Experience (oobe) option to prompt for a new system name and such.

Some good links:
1. The Books Online entry for the feature in SQL Server 2008 R2 can be found here.
2. A nice walkthrough of the SQL Server 2008 R2 GUI for SysPrep can be found here.
3. The Windows Sysprep documentation can be found here.

Fun fact:
Note that Windows spells Sysprep with a lowercase p, but SQL uses an uppercase P. Gotta love consistency!