Server Name confusion

Question

Server Name confusion

NewBornDBA2017

SSCarpal Tunnel

Points: 4832
More actions
May 14, 2018 at 7:40 am

#328709

We had one of our database server which was moved to AWS couple of months ago. Old server was prod5 and the server in AWS is prod5t. What happens is I connect to prod5t but when I run select @@SERVERNAME, it shows me prod5 and not prod5t. Prod5 hasn't been decommissioned yet so it is still running but there is no activity. Will I have to change the server name? Am I not connecting to prod5t?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

NewBornDBA2017 - Monday, May 14, 2018 7:40 AM
We had one of our database server which was moved to AWS couple of months ago. Old server was prod5 and the server in AWS is prod5t. What happens is I connect to prod5t but when I run select @@SERVERNAME, it shows me prod5 and not prod5t. Prod5 hasn't been decommissioned yet so it is still running but there is no activity. Will I have to change the server name? Am I not connecting to prod5t?

Check what the server name property which would be the network name of the server:
SELECT SERVERPROPERTY('ServerName')

You can query one of the DMVs to see if you are connected - take a quick look at sys.dm_exec_sessions or sys.dm_exec_connections

Sue

NewBornDBA2017 SSCarpal Tunnel Points: 4832 More actions · Answer 2

Sue_H - Monday, May 14, 2018 8:10 AM
Check what the server name property which would be the network name of the server:
SELECT SERVERPROPERTY('ServerName')

SELECT SERVERPROPERTY('ServerName') shows prod5t. So just curious is to why select @@servername shows prod5?

Sue_H SSC Guru Points: 90860 More actions · Answer 3

NewBornDBA2017 - Monday, May 14, 2018 8:25 AM
Sue_H - Monday, May 14, 2018 8:10 AM
Check what the server name property which would be the network name of the server:
SELECT SERVERPROPERTY('ServerName')
SELECT SERVERPROPERTY('ServerName') shows prod5t. So just curious is to why select @@servername shows prod5?

It's seen as the old server name by SQL Server. You should be able to see the old name in sys.servers as well:
SELECT name FROM sys.servers WHERE server_id = 0

To rename it, you would just need to execute:
sp_dropserver 'old_name' go sp_addserver 'new_name', 'local' go

Make sure to add 'local' at the end when changing to the new name.

Sue

NewBornDBA2017 SSCarpal Tunnel Points: 4832 More actions · Answer 4

Sue_H - Monday, May 14, 2018 9:59 AM
To rename it, you would just need to execute:
sp_dropserver 'old_name' go sp_addserver 'new_name', 'local' go
Make sure to add 'local' at the end when changing to the new name.
Sue

Do I have to restart SQL Server services?

Sue_H SSC Guru Points: 90860 More actions · Answer 5

NewBornDBA2017 - Monday, May 14, 2018 10:08 AM
Sue_H - Monday, May 14, 2018 9:59 AM
To rename it, you would just need to execute:
sp_dropserver 'old_name' go sp_addserver 'new_name', 'local' go
Make sure to add 'local' at the end when changing to the new name.
Sue
Do I have to restart SQL Server services?

Yup. All should be fine after that.

Sue