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.
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:
Then I went to change the name of the SQL Server.
In SQL Server Management Studio (SSMS):
Right click the top object
Change the name
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:
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:
– this is the same command in Linux
Windows 10 Environment Variables:
This is weird, I set the hostname to Acer8481Windows10, but these three environment variables were set to ACER8481WINDOWS. Odd.
Pinging ACER8481WINDOWS [192.168.2.107] with 32 bytes of data:
Reply from 192.168.2.107: bytes=32 time<1ms TTL=128
Pinging Acer8481Windows10 [fe80::8d83:6cae:a40b:4a47%9] with 32 bytes of data:
Reply from fe80::8d83:6cae:a40b:4a47%9: time<1ms
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]
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]
SQL Server Metadata:
Here are SQL Server commands that you can run to reveal various aspects about your databases.
SERVERPROPERTY(‘MachineName’) AS ComputerName,
SERVERPROPERTY(‘ServerName’) AS ServerName,
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
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:
I’m sure that this is the same as the name running in Windows 10 services.
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
Again, note that the original hostname is used, not the newer hostname.
SELECT database_id, name
FROM sys.databases ;
For connectivity, the best way to determine the correct name of your SQL Server installation, is probably to use SSMS.
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:
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.
How can I determine installed SQL Server instances and their versions?
How to find instance name
Docs on SERVERPROPERTY (Transact-SQL)
List of @@ variables: