Renaming the SQL Server

When my company upgrades servers (e.g. ProdDB), we start by building a new server (e.g.ProdDBNew). The new machine goes through the appropriate configuration & testing phases. When it is ready for production, the old server is renamed and retired (e.g. ProdDBOld) and the new server is renamed (ProdDB). The ip address is also changed as part of this process.

However, SQL Server does not automatically detect the name change and can continue to identify with the original name. Use SELECT @@SERVERNAME to check what SQL Server believes to be it’s name.

If it does not match, then the following steps can be used to correct

On the old server
exec sp_dropserver ‘ProdDB’
exec sp_addserver ‘ProdDBOld’, ‘local’
— restart SQL Server
SELECT @@SERVERNAME — to confirm the change

Similarly, on the new server
exec sp_dropserver ‘ProdDBNew’
exec sp_addserver ‘ProdDB’, ‘local’
— restart SQL Server
SELECT @@SERVERNAME — to confirm the change

We switch off the old server and remove from the network environment as extra security. The server is retained for a short period before undergoing a complete rebuild or disposal.

Advertisements
Leave a comment

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: