4

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+

  • 3
    Why dont you just add an extended property to the master database ? – Spörri Jun 5 '15 at 16:21
  • Thanks for reply. That's one thing I actually forgot to mention. I'm not administrating all SQL Servers and can't really make modifications on those out of scope. Thus looking for a way to identification that ca be done with "deafault" information only – Ricky-Brno Jun 5 '15 at 16:29
  • I don't think SQL server provides any unique identification other than the server's MAC address + instance name (which can't be changed). But the instance itself could be re-created on another server (or fail over) - is that still the same unique instance to you, or is it a new one? – Aaron Bertrand Jun 5 '15 at 16:36
  • 1
    How about going for a three part key? select serverproperty('MachineName') MachineName, serverproperty('InstanceName') InstanceName, srs.database_guid from sys.database_recovery_status srs where srs.database_id = 1; – Mister Magoo Jun 7 '15 at 0:28
  • 1
    When you move the instance are you restoring the msdb and master databases from the old server or starting new? – Kenneth Fisher Sep 14 '15 at 22:32
2

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.

1

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.

  • I might stand corrected on this point - do you have the ability to run powershell scripts in this environment, and are all servers joined to the same domain? – Todd Harmon Sep 15 '15 at 0:40
1

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.

-3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.