How To: Change Instance Name Of SQL Server
Recently I change the network name of one of my servers at work, because the box changed its job from a virtual machine server to the database server. Everything was going great until I decided to setup the server for replication and received the following error message.
New Publication Wizard
——————————SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ‘old_name’. (Replication.Utilities)
——————————
OK
——————————
So with a little hunting and SQL queries I found out that SQL Server doesn’t use the network name, it only excepts that as an alias. My SQL Server instance was still named “old_name”. I found that out by running these two queries:
sp_helpserver select @@servername
So in order to get the network name and the SQL Server instance name back in sync I had do these steps:
- Run this in Microsoft SQL Server Management Studio:
sp_dropserver 'old_name' go sp_addserver 'new_name','local' go
- Restart SQL Server service. I prefer the command prompt for this, but you can just as easily do it in Services under the Control Panel
net stop mssqlserver
net start mssqlserver
Then after that is done run this again, to make sure everything is changed:
sp_helpserver select @@servername
I don’t understand why SQL Server uses it’s own name versus the network name, might be due to the fact you can have multiple SQL Server instances install on one machine. It wasn’t too hard to change and probably stems from the days when SQL Server was known as Sybase, all in all, I learned something new and it only took 30 minutes of my day to fine the answer.
Tags: How To, NAT, Network, SQL, SQL Server, T-SQL
Social:
April 25th, 2008 at 8:22 am
Well lucky for me you found it a few months before I needed it. It only took me 5 minutes on Google to find your post.
Thanks!!!
April 25th, 2008 at 8:35 am
No problem.
November 5th, 2008 at 1:17 am
Thank you so much, it really helped me
December 9th, 2008 at 6:59 pm
Hi
I did previously but it won’t worked, and then i did it by cool mind using same procedure, then it worked for me.Thank you so much.
Thank you
Satish
June 10th, 2009 at 6:03 am
The “# select @@servername” tells it is changed, but the ssms not, and I can only connect to it with the old name.
regards
June 25th, 2009 at 12:48 pm
Hi
Are you using SQL express edition?
regards
June 30th, 2009 at 3:00 pm
You are not changing the instance name, you are only changing the computer’s name. You need to change the title of this post and change reference to “network name” to “computer name”.
http://msdn.microsoft.com/en-us/library/ms143799.aspx
July 1st, 2009 at 9:02 am
Thanks amj,
You are right, I changed my computer name, which is the same as my network name. So that is not wrong, because I was describing my situation. I clarified that later on, by saying “SQL Server doesn’t use the network name, it only excepts that as an alias”. And then when on to describe how to change the server name.
I make it a policy to not modify posts, I don’t consider my blog a wiki, so everything written is a glimpse in to a point of time in my life.
Thanks for you input,
Nick Berardi
July 10th, 2009 at 2:04 am
Thanks Nick,
It’s great!
October 13th, 2009 at 12:27 pm
Nick,
“amj” is correct, the title is misleading. I found my way to this post when looking for information on what the ramifications of changing the instance name of SQL Server, and the title definitely implies that this post has to do with changing the instance name. I mean look at it – “How To: Change Instance Name Of SQL Server”
Having said that, I like your below statement. A lot.
“I make it a policy to not modify posts, I don’t consider my blog a wiki, so everything written is a glimpse in to a point of time in my life.”
I like the honesty that this gives your post, even if it is misleading, and I like it.
December 9th, 2009 at 12:29 am
Great it work fine.
January 17th, 2010 at 9:16 pm
Thnx … It’s work for me.
January 18th, 2010 at 10:19 am
[...] How To: Change Instance Name Of SQL Server [...]