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

16 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. 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