How does changing Windows SID affect SQL Server?

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.

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

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.