Sql Server machine name change

  • I have a situation where we are upgrading

    from sql 2000 with only the default db instance, which runs windows server 2000

    to a sql 2005, with default instance only, server witch runs windows 2003 server. Each database instance is on it's own machine. The question was posed to me, "Why can't we just rename the new server to the old server name and take the old server off line after migrating the databases?" I cringed at first but have read this can be done. I would be interested in hearing from others and their success / failures.

    One question I have is, if we only have one instance, the default instance on the new server, will the default instance take on the name of the new server's new name.

    Example. sql 2000 box is named server1. Sql 2005 box is named server2. If the databases are all migrated over to server2, and then server1 is taken off line and server2 is renamed server1, will the default instance then be server1? Will applications previously pointing to server1 now work on the new server, which is now named server1.

    NOTE: 1) No Reporting Services are being run. 2) No replication is being done.

    Thanks

  • Hey,

    I had to do ths years ago. I took backups of all the databases, including the system databases and moved the backups to the new drive system.

    I shut down the old server and updated the name & ip address of the new server and also the port number. I then restored the system databases and the user databases.

    There were one or two issuses along the way, but they were resolved fairly quickly. I can't remember all the details as this was done way back on boxing day in 2001!! I started around 7pm and finished at 4am. Manged to get back into the office for 8am and checked the systems were functional. A few more small issues which again were easily resolved.

    If I think of any of the issues, I'll add to the thread.

  • Yes, you can change the name of a server running SQL Server. I did it last month regularly when setting up a new development environment. Every time without any issue. After setting up the new server, we took the old server offline, rename the new server and change the IP, reboot and everything works fine.

    Just one thing to keep in mind: If you have connected SSMS (on the server itself) to the SQL-instance before you changed the name of the server, the old servername is default when you connect again. You have to select the new name, obviously...:P

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • To correctly let SQL 'know' and display the changed servername you have to run the following code to update the system metadata (sys.servers and @@ServerName):

    sp_dropserver {old_name}

    GO

    sp_addserver {new_name}, local

    GO

    See the article in this link: http://msdn.microsoft.com/en-us/library/ms143799.aspx

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply