Identifying the Correct Name Of Your SQL Server


Intro:

One of the key concepts in relational databases is that of the unique identifier. You would hope that this would apply to technical terms also.

But I’ve recently discovered that, after a simple installation of SQL Server 17, on my Windows 10 laptop, I now have three different names for my host machine. And two different names for my SQL Server.

These have caused me a lot of grief and wasted time.

Here are some methods to identify the different names, and help you troubleshoot the associated issues.

Installation History:

Recently, I installed Windows 10 on an older laptop’s free partition (without entering a license key).

When I installed the OS, the name of the laptop defaulted to DESKTOP-CHLGNPN, but I was unable to change the name.

Next I installed SQL Server 17, and the SQL Server name defaulted to DESKTOP-CHLGNPN.

Having established that SQL Server worked, I then changed the Windows 10 license key of the laptop.

Having entered the Windows OS license key, I could then change the name of the laptop, and I changed it to:
Acer8481Windows10

Then I went to change the name of the SQL Server.

In SQL Server Management Studio (SSMS):

SSMS_Name

 

Object Explorer
Right click the top object
Properties
Change the name

SQLServerDBProperties01

I’m sure that I changed this name, to the same as the hostname, but later I discovered that the name was in fact different.

The name in SQL Server was actually:
ACER8481WINDOWS

Not:
Acer8481Windows10


This ended up causing a LOT of issues with the connectivity between R Studio and SQL Server.

So, here are a number of commands to discover the different names on your Windows 10 machine, and various aspects of SQL Server.

Windows 10 OS Commands:

C:\>hostname
Acer8481Windows10

– this is the same command in Linux

Windows 10 Environment Variables:

C:\>set

COMPUTERNAME=ACER8481WINDOWS

LOGONSERVER=\\ACER8481WINDOWS

USERDOMAIN=ACER8481WINDOWS

USERDOMAIN_ROAMINGPROFILE=ACER8481WINDOWS

This is weird, I set the hostname to Acer8481Windows10, but these three environment variables were set to ACER8481WINDOWS. Odd.

Ping:
C:\>ping ACER8481WINDOWS

Pinging ACER8481WINDOWS [192.168.2.107] with 32 bytes of data:
Reply from 192.168.2.107: bytes=32 time<1ms TTL=128

C:\>ping ACER8481WINDOWS10

Pinging Acer8481Windows10 [fe80::8d83:6cae:a40b:4a47%9] with 32 bytes of data:
Reply from fe80::8d83:6cae:a40b:4a47%9: time<1ms

 

Windows tracert:

C:\>tracert ACER8481WINDOWS

Tracing route to ACER8481WINDOWS [192.168.2.107]
over a maximum of 30 hops:

1 <1 ms <1 ms <1 ms Acer8481Windows10.Belkin [192.168.2.107]
C:\>tracert Acer8481Windows10

Tracing route to Acer8481Windows10 [fe80::8d83:6cae:a40b:4a47%9]
over a maximum of 30 hops:

1 <1 ms <1 ms <1 ms Acer8481Windows10.Belkin [fe80::8d83:6cae:a40b:4a47]

Interesting results.
——

 

SQL Server Metadata:

Here are SQL Server commands that you can run to reveal various aspects about your databases.

SERVERPROPERTY:
SELECT
SERVERPROPERTY(‘MachineName’) AS ComputerName,
SERVERPROPERTY(‘ServerName’) AS ServerName,
SERVERPROPERTY(‘InstanceName’) instancename

ComputerName ServerName instancename
ACER8481WINDOWS ACER8481WINDOWS NULL
Note: in this case, hostname=Acer8481Windows10, but the SERVERPROPERTY(‘MachineName’) is different.

In my case, it is the same value as is found in the Windows environment variables:
COMPUTERNAME, LOGONSERVER, and USERDOMAIN=ACER8481WINDOWS

 

SYS.SERVERS:
SELECT
server_id
, name
, product
, provider
, data_source
FROM sys.servers

server_id name product provider data_source
0 DESKTOP-CHLGNPN SQL Server SQLNCLI DESKTOP-CHLGNPN
Note that for name, and data_source, the original hostname is used, not the newer hostname!  Confusing?

 

SQL Server Global Variables:

SELECT @@SERVICENAME
MSSQLSERVER

I’m sure that this is the same as the name running in Windows 10 services.

ServicesSQLServer

SELECT @@VERSION
Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) – 14.0.2002.14 (X64) Jul 21 2018 07:47:45
Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17763: )
Select @@servername as servername

servername
DESKTOP-CHLGNPN
Again, note that the original hostname is used, not the newer hostname.

SYS.Databases:

SELECT database_id, name
FROM sys.databases ;

database_id name
1 master
2 tempdb
3 model
4 msdb
5 Flightdata1
6 tpcxbb_1gb

—-

For connectivity, the best way to determine the correct name of your SQL Server installation, is probably to use SSMS.

SSMS_Name

 

—-
Conclusion:

After a simple installation of SQL Server 17, on my Windows 10 laptop, I now have three different names for my host machine:
DESKTOP-CHLGNPN
ACER8481WINDOWS
Acer8481Windows10

and two different names for my SQL Server:
DESKTOP-CHLGNPN
ACER8481WINDOWS

The different names can be really confusing, especially with connectivity.
Hopefully these commands will help out any other poor soul who has the same trouble.
—–

Useful Links:
How can I determine installed SQL Server instances and their versions?
https://stackoverflow.com/questions/141154/how-can-i-determine-installed-sql-server-instances-and-their-versions

How to find instance name
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/eb58a7fe-4647-4eea-91e3-6ee369362d64/how-to-find-instance-name
Docs on SERVERPROPERTY (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-2017
List of @@ variables:
https://docs.microsoft.com/en-us/sql/t-sql/functions/servername-transact-sql?view=sql-server-2017

 

 

 

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: