Our Windows admins have identified an issue with the way that they clone Windows servers. Apparently some of the cloned servers end up with the same SID at the OS level. I hear Microsoft doesn't support servers that have duplicate SIDs. Therefore, the SIDs on these servers need to be changed.
I'm curious how that affects SQL Server. Any ideas? How does it affect clustered database servers?
Leave the SID alone. NewSID has been retired because Mark Russinovich did some digging and found that the whole "duplicate SIDs == bad!" line we've all had drummed into our skulls over the past decade or so is just a load of nonsense.
See Mark's latest blog entry: The Machine SID Duplication Myth.
I would definitely recommend against changing the SID, until after you read: Changing Machine SID With NewSID Breaks SQL Server (And How To Fix It)
Apparently, some of the cloned servers end up with the same SID at the OS level.
I would hazard to guess ALL of your cloned systems have the same SID. GhostWalk can regenerate SIDs for you. Using sysprep on your initial clone image could save you on future systems as well.
If you installed SQL Server DO NOT CHANGE THE SID. Bad things will happen.
You can use sysinternals NewSID: http://technet.microsoft.com/en-us/sysinternals/bb897418.aspx
Change the computer name in SQL:
use master
sp_dropserver '<old computer name>'
GO
sp_addserver '<new computer name>', local
GO
sp_helpserver -- will show you the new computer name
Then restart the sql server service.
Use the now Microsoft owned tool NewSID or sysprep, which is kind of like re-installing windows without all the file copying.
I don't think you can join two computers to the same domain with the same SID, so I'd say that clustered SQL Servers wouldn't stand a chance since servers need to be on a domain.
If your database does remote transactions using Microsoft Distributed Transaction Coordinator, be aware that cloned machines also have the same MSDTC ID, which is not the SID and not changed by NewSID.
You will see this in the Event Viewer:
The local MS DTC detected that the MS DTC on SERVER has the same unique identity as the local MS DTC. This means that the two MS DTC will not be able to communicate with each other. This problem typically occurs if one of the systems were cloned using unsupported cloning tools. MS DTC requires that the systems be cloned using supported cloning tools such as SYSPREP. Running 'msdtc -uninstall' and then 'msdtc -install' from the command prompt will fix the problem. Note: Running 'msdtc -uninstall' will result in the system losing all MS DTC configuration information.
I resolve it like so:
msdtc -uninstall
Wait a couple of minutes, then
msdtc -install
sc config msdtc start= auto
sc start msdtc
The only supported way to clone a system is with sysprep. There are a bunch of reason why not to clone a sql server:
-Its not supported by Microsoft CSS.
-SQL will not work properly until it is "renamed".
-If you have reporting services it will be hosed as well.
-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$$MSSQLSERVER. Its not supported to rename these
In order to rectify the situation I would-
Break the cluster, rebuild the system, install SQL, create a new cluster, run a backup on the server that's not been rebuilt- then stop it, restore that backup to the new cluster, point the application to the new cluster, rebuild the remaining server and add it to the new cluster
-- alternatively (probably easier) why not build a new server with new name(this will solve potential issues with SID of any type) then break the cluster install SQL join it to the cluster, failover to that box then repeat the process, that way no downtime and no need for backup/restore (although I would suggest you did though anyway). We use zznode1, zznode2 and a cluster name that way creating zznode3 and joining it to the cluster is simple as the node is not referenced when in the cluster. Hope that helps.
