When changing the hostname of a computer running SQL Server, system metadata must be updated so that remote connections and applications do not lose connectivity. Those that use @@SERVERNAME or query sys.servers for the hostname that is.
Microsoft’s documentation pages are a must read as always. Some considerations include; failover clustering, mirroring, replication, SSRS, local authentication and remote logins currently connected.
Updating SQL Server after a Hostname Change
(SQL Server has been installed – you now need to change the hostname of the server.)
1. Rename Computer & reboot.
2. Open SSMS & connect to your SQL Instance.
If the default Server name remains, you’ll receive a connection error:
Change the Server name to localhost to get connected.
3. Get SQL’s impression on hostnames.
xp_getnetname is an undocumented extended stored procedure that returns your current hostname. We want both to return the same result.
4. Drop currently registered name (sp_dropserver).
5. Register new hostname (sp_addserver).
6. For the change to be applied, restart the SQL Server service.
7. Get the SQL Server hostname info again.
That’s it done!
When you next open SSMS, ‘localhost’ should be shown in the Server name field. Changing this to your new hostname will now work.
-- Get currently registered hostname: SELECT @@SERVERNAME AS 'Old Server Name' -- Get EXEC xp_getnetname -- Drop current hostname registration. USE [master] GO sp_dropserver 'enter old hostname returned by @@SERVERNAME above' GO --Register new hostname. USE [master] GO sp_addserver 'enter hostname returned from xp_getnetname above',local -- having local updates the metadata for @@SERVERNAME GO -- Restart SQL to apply changes