I was at a client where they are using non-Microsoft clustering technology to achieve the high availability of SQL Server instances. This was party because of legacy reasons and partly because it supports clustering across all major hardware, operating system and applications including SQL Server. SQL Server instances are setup in either 2 or 3 nodes active/passive, active/active etc. configuration. There are about 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 connection to a sql server instance. Often I would need to know actual physical node name where a particular sql instance is active and, I needed to find it out programmatically.
You may have different reason/s where you are connected 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') ComputerNamePhysicalNetBIOS
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 every other text out of it to extract only the computer name, store it in a variable and/or in my own meta data table for further processing, reporting etc...
Then I thought, is there another way, maybe a better way? Well, I would not call my next approach any better but it is another way.
If you just want a one liner to look up computer name remotely, simply run the following command from the command prompt or powershell:
wmic /NODE:sqlservernode1 computersystem get Name
Here is a bit lenghty 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, if I am using a DOS or powershell command then I use the powershell to populate the data in sql table and avoid all that other extra code that I have used in the above tsql block.
And finally, there is one more way, sorta! If you execute a multi server query with the "Add server name to the results" option set to true (default), it would display the physical server name in the results pane. Though I am 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!