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):
Object Explorer
Right click the top object
Properties
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:
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.
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.
—-
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