Why Not to Sysprep SQL Server


If you do a lot of installs its a good idea to have an automated process for provisioning SQL Server. A lot of people try to use "sysprep"

Here's a few reasons why not to use sysprep.

  • Its not supported by Microsoft CSS.
  • SQL will not work properly until it is "renamed".
  • If you have reporting services it will be messed up to.
  • The System and Network Service accounts will get new SID and passwords, so if you have used these as service accounts there will be some pain.
  • SQL Server creates a good few local groups with the format. SQLServer2005MSSQLUser$<Server>$MSSQLSERVER. Its not supported to rename these
  • It just doesn't feel clean 😉

If you do want to plod on here's some links and tips

- How to rename SQL Server

http://msdn.microsoft.com/en-us/library/ms143799.aspx

- Renaming a Report Server Computer

http://msdn.microsoft.com/en-us/library/ms345235.aspx

- Don't start the SQL Server when sysprep is taken. This can help resolve the issues with SID values.

Alternative Approach

The best alternative is to add the pre-requisites to the base image, sysprep the computer WITHOUT SQL Server installed and then have the installation fully automated via a configuration file as per http://msdn.microsoft.com/en-us/library/dd239405.aspx 

or with SQL 2005 you'll need the command line approach

http://msdn.microsoft.com/en-us/library/ms144259(SQL.90).aspx

Comments (5)
  1. I posted something similar to this about using NewSID from sysinternals and how to fix it: http://kendalvandyke.blogspot.com/2009/01/changing-machine-sid-with-newsid-breaks.html

    I agree it’s not something I would recommend doing, but sometimes the non-DBA types don’t know this will break SQL and it’s up to us DBAs to clean up the mess afterwards.

  2. Iain Wyatt says:

    Hi Bob, could you expand on what you mean by – Don’t start the SQL Server when sysprep is taken. This can help resolve the issues with SID values.

    Do you mean that we should stop the sql Services before kicking sysprep off?

  3. MSDN Archive says:

    Hi Iain,

    ideally sysprep before SQL has started the FIRST time (bets option) or have it manually stopped (next best). The problem I have found is that the NETWORK_SERVICE and SYSTEM account SIDS’s can get changed by sysprep and joining a domain.  These accounts would be stored in the syslogins table.

    If SQL has any of these service accounts used, you may get an error and by unable to start the service.

    Sorry for the delay (must configure my notification for comments!)

  4. Larry Crystalrock says:

    None of the reasons that are cited for not sysprep’ing SQL Server apears to be a solid reason. I understand you need to get MS to support it, and so on. But what are the technical reason for not doing it? If it may break things, what does it break?

  5. MSDN Archive says:

    Hi Larry,

    you are right in that non is a "SOLID" reason by itself. The facts are sysprep will hit the technical issues listed above (like invalid server name), but each issue has a work around (except supportability).

    Personally I use sysprepped SQL Servers in lab conditions, but for clean production servers I use sysprepped windows and automated install with config manager or a batch file. Same end result, longer time, but better configurablity and supportability.

    If someone has tested sysprepped SQL, acknowledges the limitations and can swallow the supportability pill I say go for it..

Comments are closed.

Skip to main content