Retrieving Server/Machine Name in T-SQL

Problem: Some automated backups run on a SQL Server 2008 instance which is sitting on a VM which is linked to a 2 node Physical Windows Cluster. We generate the backup location through UNC so the path can alter and we save the completed backup path to a backup_status table. This table is then used by non live servers to pick up the location of the latest backup so that they can automatically refresh.

image

Different options for selecting machine names

select HOST_NAME()

HOST_NAME will return the name of the workstation that you are logged in to. So if logged into the Virtual Server, then MYVMSERVER01 will be returned

select SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

The above server property will return the NetBIOS name of the local computer on which the instance of SQL Server is currently running. For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster.

For the example in the diagram, it will currently return MYSERVER01A when run on the MYVMSERVER01. If the SQL Cluster Fails over, then it will return MYSERVER01B.

select SERVERPROPERTY('MachineName')

MachineName returns the Windows computer name on which the server instance is running. For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.

For the exmaple in the diagram, It will retrun MYVMSERVER01 regardless of which physical server the VM is running off.

select SERVERPROPERTY('ServerName')
SELECT @@SERVERNAME

Will return the server and instance name. For the example in the diagram, it will return MYVMSERVER01\SQL01 regardless of where it is run from.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: