I was at a client where they are using non-Microsoft clustering technology to achieve the high availability of SQL Server instances. This was partly because of legacy reasons and partly because it supports clustering across all major hardware, operating systems, and applications, including SQL Server. SQL Server instances are set up in either 2 or 3 nodes, active/passive, active/active, etc., configurations. There are approximately 30 physical servers hosting SQL Server instances. Yes, the client is going to move all the SQL workloads to Always On Clusters, but the process has been slow because all the databases are used for COTS/third-party applications.
A virtual name is used to make a connection to a SQL Server instance. Often, I would need to know the actual physical node name where a particular SQL instance is active, and I needed to find it out programmatically.
You may have different reasons for connecting to SQL Server using a virtual name but need to know the underlying machine name.
So I first tried this query:
SELECT @@SERVERNAME ServerName_Global_Variable
,SERVERPROPERTY('ServerName') ServerName
,SERVERPROPERTY('InstanceName') InstanceName
,SERVERPROPERTY('MachineName') MachineName
,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') NetBIOS
That would be the easier way if it worked in this situation. Alas, it still kept giving me virtual server name.
Then I tried using a DOS command, assuming you have or are able to temporarily turn on XP_CMDSHELL.
EXEC master..XP_CMDSHELL 'Hostname'
And I still got the virtual server name.
Then I tried the following which does display the actual server name in one of the messages:
EXEC master..xp_cmdshell 'net user'
Mission accomplished, great! I could write additional code around it to trim out every other text from it, extracting only the computer name, and then store it in a variable or in my own metadata table for further processing, reporting, etc.
Then I thought, is there another way, perhaps a better way? Well, I wouldn't necessarily call my next approach better, but it's certainly another way.
If you're looking for a one-liner to remotely look up a computer name, simply run the following command from the command prompt or PowerShell:
wmic /NODE:sqlservernode1 computersystem get Name
Here is a bit lengthy TSQL code to do this while connected to a SQL instance.
Note that if the XP_CMDSHELL is disabled, it temporarily turns it on then off when done.SET NOCOUNT ON
DECLARE @computer_name VARCHAR(500)
DECLARE @xp_cmdshell_status BIT
SELECT @xp_cmdshell_status = cast(value_in_use AS BIT)
FROM sys.configurations
WHERE name = 'xp_cmdshell'
IF @xp_cmdshell_status = 0
BEGIN
PRINT 'XP_CMDSHELL is disabled on this server, temporarily enabling it...'
EXEC sp_configure 'show advanced options','1'
RECONFIGURE
EXEC sp_configure 'xp_cmdshell','1'
RECONFIGURE
END
IF object_id('tempdb..#t1_xp_cmdshell_output') IS NOT NULL
DROP TABLE #t1_xp_cmdshell_output
CREATE TABLE #t1_xp_cmdshell_output (
id INT identity
,txt NVARCHAR(2000)
)
INSERT INTO #t1_xp_cmdshell_output
EXEC master..xp_cmdshell 'wmic computersystem get Name'
SELECT @computer_name = txt FROM #t1_xp_cmdshell_output WHERE id = 2
SELECT @computer_name computer_name
IF @xp_cmdshell_status = 0
BEGIN
PRINT 'Changing XP_CMDSHELL back to disabled state...'
EXEC sp_configure 'show advanced options','1'
RECONFIGURE
EXEC sp_configure 'xp_cmdshell','0'
EXEC sp_configure 'show advanced options','0'
RECONFIGURE
END
Quite a lengthy code!
Generally, when I'm using a DOS or PowerShell command, I prefer using PowerShell to populate the data in the SQL table, avoiding all the additional code I've used in the above T-SQL block.
And finally, there is one more way, sort of! If you execute a multi-server query with the "Add server name to the results" option set to true (default), it will display the physical server name in the results pane. However, I'm not aware of a way to capture it dynamically in a variable at this point.
If you think I missed something, please don't hesitate to provide feedback!