Tuesday, 25 September 2012

How to Rename SQL Server

With the release of SQL 2012 most of the companies might have started migration of SQL server to new version, In case of server migrations most of the time we will end up in a situation to rename the physical server hosting SQL Server, Renaming SQL Server to host the new physical server name is pretty simple as done below

MSDN Reference for Add Server http://msdn.microsoft.com/en-us/library/ms174411.aspx
As said in the article, This procedure must be executed on all instances of the Database Engine hosted on the computer. The instance name of the Database Engine cannot be changed.
 
On the SQL Server instance, run the following query.
sp_dropserver '<oldservername>'
go

sp_addserver '<newservername>', local
go

Now restart the SQL Server related services. Then, run @@servername to verify if the new name has been updated.
select @@servername

21 comments :

  1. This is really valuable information! Are there any side effects to doing this? Just wondering because I've never seen this documented anywhere but it's definitely very useful.

    ReplyDelete
    Replies
    1. Hi, We have been using the procedure for many years now and we haven't faced any issue. sp_dropserver and sp_addserver are well documented sp's in MSDN.

      Delete
  2. sp_addserver discountined in 2012. fail

    ReplyDelete
    Replies
    1. Hi, The sp_addserver works fine for local server and is documented in SQL Server 2012

      http://msdn.microsoft.com/en-us/library/ms174411.aspx

      Microsoft has deprecated sp_addserver only for remote servers. More details explained on the Nakul's blog

      http://beyondrelational.com/modules/2/blogs/77/Posts/14883/0164-sql-server-2012-discontinued-features-spaddserver-remote-server-registration-msg-15663.aspx.

      Delete
    2. That link is dead. Please submit new link, thanks!

      Delete
    3. https://msdn.microsoft.com/en-us/library/ms143799.aspx

      Delete
  3. Hi. I have done this, changing from DBSERVER1 to DBSERVER. I used "select @@servername" to confirm it completed successfully. However, when I use Management Studio or Profiler, I cannot connect to DBSERVER, I can only connect to DBSERVER1. Is there something else I need to do? Thanks

    ReplyDelete
  4. A restart of SQL related services after SQL rename should take care of connection remotely. was the Services restarted?

    ReplyDelete
  5. question- I had 2008 R2 installed and by mistake when installing 2012 I had them use smae instance name (server name).
    now all DBs from 2008 showing up in 2012, how can I change ONLY the 2021 instance name without affecting the 2008?

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Thanks for sharing this such a great information about Chossing stream host. i really inspire your post i shared this link to my all facebook and twitter friends because this link helpfull for everyone

    ReplyDelete
  8. It worked perfectly for me, good thing that I searched it out first since I could not get it started. Thanks!

    ReplyDelete
  9. Great article! Very informative and simple easy to understand. Keep posting it really help for those people that needs this. I will refer your blog to my friends. Thanks a lot!

    ReplyDelete
  10. A good site for you guys to check out is text link adds, you should do a search in google and you will see what I’m talking about.
    cheap minecraft server hosting

    ReplyDelete
  11. Daily mild exercise, like playing in the garden or walking on a leash, is good enough to satisfy the Basset. The dog’s face, particularly the wrinkles and around the mouth should always be kept clean, while the coat does not require much grooming. This breed has a tendency to drool and it functions best indoors as a house pet.

    ReplyDelete
  12. It is amazing blog.Minecraft server hostingis an important feature for hosting websites or blogs since it offers a cheaper solution. You can easily upgrade it and provide more security than shared hosting.

    ReplyDelete
  13. There are some great tips on this blog that I plan to use.cloud hosting is best compared to all other type of hosting.Use cheap minecraft server hosting with better hardware support.

    ReplyDelete
  14. This procedure did not work for me with SQL Server 2012 Express on an SBS 2008 server. I originally had a type in the instance name. Connected to the instance using Management Studio 2012, opened a Query window, I corrected it using this procedure, and got the @@servername confirmation. I then restarted the server rather than just the services. After restart the services still showed the old instance name as well as the list of servers in Management Studio. If I go into the old instance name and loop, @@servername shows the new name. The named pipe shows the old name.

    As a stab, I tried changing it to the new name with no change in behavior.

    What am I missing?

    Thanks

    Joe M

    ReplyDelete
    Replies
    1. Sorry - Not gaining on typos:

      type -> typo
      loop -> look
      I changed the named pipe, not "it".

      Delete
    2. Hi,

      As i have already mentioned in the article i am afraid you can't change the instance name, This procedure is only change your computer name and not instance name.

      -Vinoth.

      Delete
  15. Typos on post:

    type -> typo
    loop -> look

    Sorry

    ReplyDelete