In recent past, I have renamed my VM and wanted to make sure that everything is fine. This renaming process is also needed for changing the server names called inside of SQL Server too. So I searched to know various places from where we can get host name and SQL Server name. Let us learn in this table how to change server name? Finally, I formed this query which can gather same details from various sources:
SELECT HOST_NAME() AS 'host_name()',
@@servername AS 'ServerName\InstanceName',
SERVERPROPERTY('servername') AS 'ServerName',
SERVERPROPERTY('machinename') AS 'Windows_Name',
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name',
SERVERPROPERTY('instanceName') AS 'InstanceName',
SERVERPROPERTY('IsClustered') AS 'IsClustered'
Most of these server properties have been there for a while and sometimes I think it requires a refresher like this to remember them again. When we run this query on the non-clustered machine, we should see only two distinguished names. Machine name and Instance name. If you have default instance, then Instance Name would be NULL in the query.
If there was a rename of host, then you would see host_name would be different and that’s not something which should exist.
Note that Instance Name rename is unsupported. In named instance the server name is in format of ServerName\InstanceName. So, you can change it to NewName\InstanceName but NOT to ServerName\ NewInstanceName. I am sure this is a fantastic trivia to know.
If you find any mismatch, then you need to follow below steps:
- Execute below to drop the current server name
EXEC sp_DROPSERVER 'oldservername'
- Execute below to add a new server name. Make sure local is specified.
EXEC sp_ADDSERVER 'newservername', 'local'
- Restart SQL Services.
- Verify the new name using:
SELECT @@SERVERNAME
SELECT * FROM sys.servers WHERE server_id = 0
I must point out that you should not perform rename if you are using:
- SQL Server is clustered.
- Using replication.
- Reporting Service is installed.
I would like to know if anyone has ever tried doing this in your environment? What is the typical reason for you doing the same? I hope this helps in case you get into a tangle like this. Let me know your thoughts via comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
the reason for doing is to maintain consistency especially if the host name where the SQL server sits has changed .@@servername
You will run into issues with Database mirroring monitoring if the ServerName property doesn’t match the hostname assigned to Windows.
I have this scenario happening right now:
– Vendor imaged servers and configured everything offsite, then shipped us the servers
– Infrastructure team deployed the servers and changed the hostnames to match our internal naming scheme
– The databases in the mirroring relationship say they’re synchronized and appear to be working as expected, but I can’t run any of the database mirroring monitoring tools to make sure mirroring isn’t behind, etc…
In my case, I also have Report Services installed. I’m working through testing this now to see what it does to Reporting Services. Dave – what was the outcome for SSRS when you changed the ServerName property? Did it break entirely and was it unresolvable?
if physical server name has changed
I changed SQL instance name and IP address in a SQL cluster with minimal issues. Name & IP address change occured in Failover Cluster Manager. Then the sp_dropserver/sp_addserver was issues with a sql restart, and all worked fine.
Nice Post!!
in my scenario suppose I have taken .(dot) as server name in config file of application because of my system name and now i have re-installed sql server using different server name like pc-name/sqlexpress so it will not work when i connect database with application, to run application successfully I have to rename server name as pc-name or have to change config (.(dot)/sqlexpress) , so is there anyway to change this servername??
in above query its simply change @@servername not ServerPriority(‘servername’)..
What about SSIS rename ?, will it be changed automatically or do we need to change it manually? how to change it?
if the server is part of replication you will need to script out all the replication details and drop everything and then change the servername and restart the services for the changes to take affect and later recreate the replication
Thanks, It’s working perfectly
Cool. Thanks.
We have a 3 node cluster with Always ON configuration.
When I execute the below query on all three servers @@servername gives a different name.
SELECT SERVERPROPERTY(‘ServerName’) as [InstanceName]
, SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as[ComputerNamePhysicalNetBIOS]
, @@SERVERNAME as ServerName
Can I use below code from your post to rename to its correct server name?
EXEC sp_dropserver ‘old_name’;
GO
EXEC sp_addserver ‘new_name’, ‘local’;
GO
I have one database in always on configuration which is set to have backups to happen on the primary server, but for some reason the backups are not happening.
Digging deep into the issue, I figured out that the server name is wrong. This is lower environment but want transaction log backups to occur on the server which uses ola Hallengren.
Can I change the server names in AlwaysOn Failover Clustering Instances (FCI)?
Please advise.
SELECT SERVERPROPERTY(N’servername’) showing old server name after running sp_dropserver and sp_addserver. @@servername showing correct name. But when i configure replication it is throwing below error
Could not connect to server ‘ ‘ because ‘distributor_admin’ is not defined as a remote login at the server. Verify that you have specified the correct login name. .
Changed database context to ‘master’. (Microsoft SQL Server, Error: 18483)
Hi Pinal, I have renamed the sql server using sp_dropserver , sp_addserver . SERVERPROPERTY(N’servername’) showing old name @@servername showing new name. when i configure replication it throwing below error
Could not connect to server ‘ ‘ because ‘distributor_admin’ is not defined as a remote login at the server. Verify that you have specified the correct login name. .
Changed database context to ‘master’. (Microsoft SQL Server, Error: 18483)
please advise.
Thanks
Sankar
I typically stage new SQL servers prior to an upgrade/migration of an existing server. This helps as there are a lot of VPNs and Third party tie-ins to SQL. Then on Go Live swap out the names and IP’s of the old/new servers, copy the db over, set appropriate settings (like the servername, trustworthy, etc.) and do the upgrade. between this and exporting SQL user with their hashed passwords the migrations go smoothly. Thanks for the Tips!
hi,
what if i want to Change Server Name when Reporting Service is installed? any advice from u sir?
As usual Pinal you saved the day, this worked for me perfectly.