Renaming sysprepred SQL Servers via script
Using syspreped images of SQL Server without any problems.
There may be many of you using syspreping in the past and encountered problems during runtime due to not findable errors which were not reproducable on machine they were installed fom scratch and not syspreped. Well, although Windows takes a lot responsiblity about renaming all internals of the WIndows OS, it doesn´t do this for SQL Server installations. The most common problem is that the sys.servers is not properly configured reflected the actual server name.
If you do a
Select name From sysservers
you will see that the old (before using the configuration of the syspreps) name is stored in the table. As you cannot modify the system tables directly (Yes, you can in SQL Server 2000, but even there it was neither recommended nor suggested) you can use the sp_dropserver and sp_addserver to register the new names. This is described in detail in the following KB. Anyway, for not needing to create the script on a machine base I created the following scripts which take input parameters from a batch to fix the name issue.
IF EXISTS(SELECT * From Sys.Servers Where name = '$(NewHostName)')
BEGIN
RAISERROR('MACHINE_SETUP:Nothing to do, server already renamed.',16,1)
END
--Get the current local servername
DECLARE @SERVERNAME SYSNAME
SELECT @SERVERNAME = srvname FROM sys.sysservers
EXEC sp_dropserver @SERVERNAME
EXEC sp_addserver '$(NewHostName)', 'local'
This can be called using the following command:
sqlcmd -E -S. -i"%1\RenameSQLServer.sql" -b -v NewHostName=%COMPUTERNAME%
pause
(Using SQLCMD mode)
The scripts are attached in the blog entry.
UPDATE: Maintainance Plans will not inhertit the settings automatically. You will need to change them manually by script. A sample was posted on the community tag within the MSDN article here: https://msdn.microsoft.com/en-us/library/ms143799.aspx
-Jens