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.

Advertisements

About Michael Coates
I am a pragmatic evangelist. The products, services and solutions I write about fulfill real-world expectations and use cases. I stay up-to-date on real products I use and review, and share my thoughts here. I apply the same lens when designing an architecture, product or when writing papers. I am always looking for ways that technology can create or enhance a business opportunity .. not just technology for technology's sake. My CV says: Seasoned technology executive, leveraging years of experience with enterprise and integration architectural patterns, executed with healthy doses of business acumen and pragmatism. That's me. My web site says: Technology innovations provide a myriad of opportunities for businesses. That said, having the "latest and greatest" for its own sake isn't always a recipe for success. Business successes gained through exploiting innovation relies on analysis of how the new features will enhance your business followed by effective implementation. Goals vary far and wide: streamlining operations, improving customer experience, extending brand, and many more. In all cases, you must identify and collect the metrics you can apply to measure your success. Analysis must be holistic and balanced: business and operational needs must be considered when capitalizing on a new technology asset or opportunity.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: