How to uniquely identify SQL instance

Question

I need to find a way how to identify SQL Instance no matter what changes were done to SQL Server and\or Windows Server where it is running, because we're collecting remotely information about instances into our operational database and still encountering way we're identifying it is not that unique as it might look.

In example, if I have server WIN1\SQL and due to server refresh will install on different server WIN2\SQL, migrate data and swap server names so that WIN2\SQL will become WIN1\SQL and vice versa, I need to be able identify that current WIN1\SQL is different from previous WIN1\SQL disregards OS version and or SQL version\patch level

Already tried following:

NetworkName\InstanceName - that worked until server hostname\network name has been changed due to server refresh as described above

SQL Server SID - I was searching for some SID of SQL Installation like Windows has, but seems nothing like that really exists

SID of ##MS_SQLAuthenticatorCertificate## - this was last best shot I found, scanned hundreds of SQL Servers and it was really unique - thought I found it finally, until I applied Service Pack to one server and find out SID has changed

Any idea how to uniquely identify SQL Instance (like SQL Instance SID) would be appreciated, because I'm getting lost

EDIT: I'm not administrating all SQL servers. Modifications on out of scope devices cannot be done, thus looking for some "already built-in" way that would work on SQL2005+

Answer 1

Well, it is somewhat possible to have an Instance GUID, but only if you are including [msdb] in your backup / restore process. Assuming that this is indeed the case, then just check the service_broker_guid field in sys.databases for the [msdb] database:

SELECT [service_broker_guid]
FROM   sys.databases
WHERE [name] = N'msdb';

It will be different across each instance, but should be consistent across restores of backups of the same [msdb] database. This even appears to hold true for the Express editions. And while I have not tracked this value across updates and Service Packs, I don't see why it would change unless forced to manually.

If you want to create new SQL Server instances from a "master" set of backup files (including [msdb]) and need the new instances to be seen as different, then just run the following after the initial restore of the system databases:

ALTER DATABASE [msdb] SET NEW_BROKER;

NOTES:

• The service_broker_guid field in sys.databases for the [master] and [model] databases is always set to 00000000-0000-0000-0000-000000000000 and cannot be changed via ALTER DATABASE [{database_name}] SET NEW_BROKER;.

• If you are using this technique to distinguish instances of SQL Server Express LocalDB, then the the service_broker_guid field in sys.databases for the [msdb] database will initially contain the same value for all newly created instances. In this case, you just need to execute ALTER DATABASE [msdb] SET NEW_BROKER;, just like when creating new instances from a backup restore.

Answer 2

There's no logical way to do this. How could you ever distinguish between a SQL Server that has been moved to a new host, and a new one that was created from a copy of another one? You can't, based only on the instance properties and contents.

The only way to do this is to make some information/table/parameter/configuration settings/etc. that keep track of it for you, which you (or someone/something) must keep up-to-date to reflect your MIS changes.

Answer 3

This may not be a complete answer to your question, but at least you will be able to identify that the SQL server has been moved to another host. You can use the date when the SQL Server was installed. It's actually a datetime when "NT AUTHORITY\SYSTEM" was created but it usually works just fine:

SELECT createdate as sql_install_date FROM sys.syslogins where sid = 0x010100000000000512000000

I just found another approach that may work even better. There is a checksum field in a registry that supposed to be unique for every SQL Server installation:

declare @Checksum nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'checksum', @Checksum OUTPUT
SELECT @Checksum

Answer 4

There is no definitive formula for tracking a "unique" Windows server or SQL Server in the context I believe you are intending. All combinations of registry information (and even WMI calls that pull BIOS information) are subject to cloning issues. I myself have tested combinations of ProductID, UUID, MachineGUID, SID, SERVERNAME, MACHINENAME, MAC ADDRESS, etc. etc. etc. in VERY massive environments (thousands of servers, physical and virtual) and nothing ever comes out unique 100% of the time.

Unless there is a process in place to manually create an "id stamp" some time after initial install\setup\cloning, you can't be guaranteed anything.

Interesting read on the myth of the need for unique SID's in Windows.

Answer 5

Avoiding duplicate keys: The only way I see is creating a fresh token on every start of the database. To make it truly unique, you could place this token generation on one highly-available central server.

Keeping keys: This is pretty difficult to realize, you never know if the database has been moved or cloned or just started. If you manage to put all those operations under your control (special scripts, etc.) you could keep up the illusion that it works. But go on vacation for 2 weeks and your replacement does not know about the tools or forgets, you're doomed.

You could disable board tools, but if he sets up a fresh machine, he can use the tools there. I think this is a question of change/configuration management, keeping track and not so much about sql-server.