Menu & Search
How to Rename a Computer that Hosts SQL Server

How to Rename a Computer that Hosts SQL Server

This post contains a demo on how to rename a computer that hosts SQL Server.

Changing a hostname of a running SQL Server host is most often required soon after a fresh MSSQL installation. You’ve installed SQL Server but you now need to amend the Windows Server hostname.

When changing the hostname of a computer that is running SQL Server, we have to update system metadata so that remote connections and applications do not lose connectivity. The hostname system metadata is stored in sys.servers and reported by @@SERVERNAME.

Considerations when changing a SQL Server hostname include:
# Failover Clusters, the renaming process differs.
# If Replication is configured on the host, I’d question why you are doing this.
# If Mirroring, turn off Mirroring pre-change and then re-establish with new computer name. This could be high risk.
# If the host is running SSRS, a configuration change may be required.
# Hardcoded hostnames for all scripts/apps will have to be updated.

This is all as described on Microsoft’s Documentation page. As always, give MS Docs a proper read and test your changes before performing in a Production environment. Hopefully, this is a new install and a single instance host you’re renaming.

The demo below includes:
# Change Windows Computer Name
# Update MSSQL with the new Hostname
# Verify Change


Change Windows Computer Name

To change the hostname of a Windows computer, we can do it via GUI or command (PowerShell).

Rename Computer

The PowerShell cmdlet for this is Rename-Computer, ensure you are running PowerShell as Administrator:

PowerShell Rename-Computer

A reboot is required post-change as per the warning on the terminal.

See MS Docs as linked above for parameters required if you are connected to a Domain, or for an example of renaming remote hosts.

Update MSSQL with the new Hostname

Now that the computer has been renamed, when we try login into SQL Server via SSMS using the old hostname, the following error will appear:

We need to change the server name to ‘localhost‘ or the new server name for us to get connected.

SSMS Localhost

Next, I’m going to run a new query to get the currently registered hostname and actual hostname.

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

-- Get actual hostname (undocumented proc)
EXEC xp_getnetname
Get Hostname SQL Server

Next, drop the currently registered name (sp_dropserver) and then register the new hostname (sp_addserver).

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

-- Define name of local instance after computer rename
EXEC sp_addserver 'WIN-PW-01', local;  
GO
SQL Server Rename Host Procedure

For the change to be applied, we need to restart the SQL Server service.

Verify Change

Once the SQL Server Engine has been restarted, run the same command as before to return the currently registered hostname. It should display the new computer name:

SQL Server Hostname Change

Now you can verify any other apps or MSSQL features as per your test run.

Share

0 Comments