Changing the name of a MSSQL Server

Renaming a system ‘beneath’ a SQL server causes a variety of problems; SQL goes into an identity crisis, MSDTC develops amnesia and chaos reigns. As a general rule, I don’t like to rename servers upon which SQL is installed, but sometimes, there’s no choice. Note: you must have an SA-level account or be in the System Administrators group on the SQL Server to perform this operation.

Here are the steps to restore order; again, use at your own risk, no warranty, etc., etc.:

Allow modifications to system catalogs:

• In SQL Enterprise Manager, Right-click the SQL Server and select Properties.
• Click the "Server Settings" tab
• In the "Server Behavior" panel, check "Allow modifications to be made directly to the system catalogs".
• Click OK.

Update fields in “sysservers“:

• Navigate to Master, Tables and open table "sysservers".
• Set the values of field “srvname“ and field “datasource“ to the new server name (you’ll note they contain the old server name).
• Note: Do not modify "srvnetname"; it modifies itself based on an update to the row.
• Cycle the SQL Server and SQL Agent services.

Once the services have been cycled, confirm the change has occurred in Query Analyzer:

SELECT @@SERVERNAME

Restore Order (aka, revoke updates to system catalogs):

• In SQL Enterprise Manager, Right-click the SQL Server and select Properties.
• Click the "Server Settings" tab
• In the "Server Behavior" panel, uncheck "Allow modifications to be made directly to the system catalogs".
• Click OK.

Reboot the server and monitor the event log. You will likely see this error:

COM+, Error 4440:

The CRM log file was originally created on a computer with a different name. It has been updated with the name of the current computer. If this warning appears when the computer name has been changed then no further action is required. (original server name).

This error is benign IF the current name of the system is in the “Computer“ field AND the original name of the built system is in the above message. If these are not both correct (as described), retrace the SQL Server steps and ensure you updated both files in table “sysservers“.

Note: If you registered your SQL and AS servers in the Enterprise Manager and Analysis Manager as the original server name (as opposed to ‘localhost’), you will need to re-register the new server name.