When undertaking the task of changing the hostname for a SQL Server-enabled computer, it’s imperative to seamlessly update system metadata to avoid disruptions in remote connections and application functionalities.

This post serves as a comprehensive guide on efficiently managing MS SQL metadata post a hostname alteration, a situation frequently encountered shortly after a fresh SQL Server installation, if you forgot the password while installing SQL Server.

The hostname system metadata in SQL Server is stored in sys.servers and reported by @@SERVERNAME which is covered within this guide. For more information you should refer to the Microsoft Docs: Rename a SQL Server Failover Cluster Instance

Key Considerations for Renaming SQL Server

• Failover Clusters: Please refer to the MS Docs link above, there are more steps involved.
• Replication: Evaluate the necessity for changes if configured as per documentation.
• Mirroring: Exercise caution, turn off Mirroring pre-change, and re-establish with the new computer name.
• SSRS: Potential configuration changes may be required for hosts running SSRS.
• Script/App Hardcoding: Update hardcoded hostnames in all scripts and applications.

All considerations are detailed on Microsoft’s Documentation page, emphasizing the importance of thoroughly reading MS Docs, and conducting comprehensive testing before implementing changes in a Production environment. Ideally suited for a new install and a single-instance host renaming scenario.

Step-by-Step Demo: Changing SQL Server Computer Name

1. Change Windows Computer Name:

Utilize the PowerShell cmdlet Rename-Computer (run as Administrator).

A post-change reboot is essential for the Computer Name change to apply.

For details on parameters for the Rename-Computer cmdlet you can refer to MS Docs as linked above. If in a Corporate environment, you may need to pass in a username & password with the -DomainCredential parameter.

2. Update MSSQL with the New Hostname

Following the computer rename, attempting to log into SQL Server via SSMS with the old hostname triggers an error.

Modify the server name to ‘localhost’ or the new server name for successful connections.

Execute queries to retrieve and update the registered hostname.

-- Get currently registered hostname 
SELECT @@SERVERNAME AS 'Old Server Name' 

-- Get actual hostname (undocumented proc)
EXEC xp_getnetname

-- Remove server from the list of known remote and linked servers on the local instance
EXEC sp_dropserver 'old-hostname';  
GO

-- Define the name of the local instance after computer rename
EXEC sp_addserver 'new-hostname', local;  
GO

The SQL Server procedures being used here are sp_dropserver (remove the SQL Server from known Linked Servers) and sp_addserver (register the new hostname).

Apply the changes by restarting the SQL Server service.

3. Verify Change in SQL Server

Post server restart, confirm the new computer name is displayed when checking the registered hostname by running the same commands as above.

-- Get currently registered hostname 
SELECT @@SERVERNAME AS 'Old Server Name' 

-- Get actual hostname (undocumented proc)
EXEC xp_getnetname

We can see here the currently registered SQL Server hostname is the same as the Server Net Name. All is good here. I hope your SQL Server host renaming goes successfully!