Problem solve Get help with specific problems with your technologies, process and projects.

Using file_guid as a unique identifier for SQL Server instances

DBAs can use the file_guid property to create a globally unique identifier for one or all of their SQL Servers by following a few simple steps.

Normally, one identifies a SQL Server instance through the @@servername configuration function. This returns the name of the server that SQL Server is running on, as well as the current instance of SQL Server. I recently read a discussion, however, about a scenario where someone wanted to have a GUID-type identifier for each of his SQL Server instances, to the exclusion of just about every other instance regardless of the machine name.

One basic way to do this, as hinted at in the above discussion, involves simply using the file_guid property for the first physical file in the database (that is, apart from the master database). It’s not a perfect solution, and it has some drawbacks that are worth discussing, but it can be used well for this purpose.

To retrieve the file_guid property, you can simply run the following query. It will return a GUID that should be suitably universal:

select top 1 [file_guid] from [sys].[database_files]

A slightly expanded version of this trick would be to create a function or stored procedure that is addressed by a common name across all the servers you’d be returning the information from, and with a suitable name to avoid collisions of its own:

create procedure [dbo].[Server:GetGUID]
select top 1 [file_guid] from [sys].[database_files]

Another advantage to this approach is it gives you a common interface that you can implement in your own programs and expand as needed. For instance, you can return additional columns (e.g., the @@servername property), or concatenate a string from multiple properties in the event you don’t need to have the result returned in strict GUID format.

One reason using the master database’s primary file GUID might trip you up is because there may be circumstances where it doesn’t always have a value assigned to it. An instance of SQL Server that has been upgraded from a previous version, for example, will have a NULL value (see the sys.master_files reference for more on this.)

To avoid this problem, you can simply use the first file that has a GUID:

create procedure [dbo].[Server:GetGUID]
select top 1 file_guid from [master].sys.master_files
where file_guid is not null

Another thing to be mindful of with this technique is that the file_guid property tends to be highly persistent; it stays the same even if you back up the database and restore it to another machine. To that end, the GUID is more closely identified with the database than the server in question.

The circumstances in which this could get hairy are few, but worth noting. Say you have a number of servers with databases that were created by restoring a backup of an empty database template instead of generating it from scratch using a T-SQL script (which is what you should be doing anyway). Those databases will have identical file_guid properties. Another workaround, as I’ve touched on above, is to use the file_guid in conjunction with the server name in some fashion, to generate a string that is part-GUID and part-something else unique.

Serdar Yegulalp has been writing about computers and information technology for more than 15 years for a variety of publications, including InformationWeek and Windows Magazine.

Dig Deeper on Microsoft SQL Server Tools and Utilities