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.
database, How To, NAT, Network, SQL Server, T-SQL
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!!!
No problem.
Thank you so much, it really helped me
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
The “# select @@servername” tells it is changed, but the ssms not, and I can only connect to it with the old name.
regards
Hi
Are you using SQL express edition?
regards
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
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
Thanks Nick,
It’s great!
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.
Great it work fine.
Thnx … It’s work for me.
This is exactly what I needed. There were a ton of other things I found on Google but all were more complex and none worked. Thanks. By the way I’m adding this in case it helps others in searches: Amazon Web Services AWS EC2. Thanks.
Does it affect all the database objects, e.g. databases, users, maintenance plans, etc.? Thanx!
Just a clarification of the question… will I still have everything working afterward that works now?
This might help some of you:
When you have more than one instance of SQL Server on the computer, change the sysservers system table information by running the stored procedures this way:
sp_dropserver old_servername\instancename
GO
sp_addserver new_servername\instancename, local
GO
http://msdn.microsoft.com/en-us/library/Aa197071
peace
This is NOT guaranteed to work, especially when you’re doing things like replication with the server. It will work in a pinch, but I’m about to tear down and rebuild a server rather than go through that again. You will see weird little errors.
It worked for me..
Thanks,
Akshat
for testing purposes, i have cloned a production VM that contains SQL server, as a result of this clone the result of
select @@ServerName (which is ‘production’) is not the same as running cmd hostname (which is ‘testing’)
and when i tried to run
EXEC Sp_dropserver @@ServerName,’droplogins’
GO
EXEC Sp_addserver ‘testing’ , ‘local’
GO
i got the following error:
Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 31
Cannot drop server ‘production’ because it is used as a Publisher in replication.
Msg 15028, Level 16, State 1, Procedure sp_addserver, Line 74
The server ‘testing’ already exists.
i tried to run exec sp_droppublication @publication =’all’ and it was completed successfully, but when i tried to run the above script i’m still getting the same error
I was struggling to figure out why the publisher is not recognizing the subscriber although i put the host file entry with the server name and IP and i was able to connect through management studio to the subscriber without any issues but when i wanted to add a new subscriber to setup replication , i was not able to do that .
Thumbs up to you Nick . Very good post . Keep up the good work .
I remember reading this article about a year ago, and I even submitted it to Dot Net Kicks. Only today did I have to actually use it! Thanks for keeping this post up on the web. I just couldn’t remember the syntax, I could remember it was possible and I knew this post was somewhere on the web !!
Hi Nick,
I have changed the name of the server before with now issues but only where I have default instance.
Will this work for named instances too?
Thank you
Ariana
Hi,
Thank you for taking the time to read this.
I have a remote machine on which there are several instance of sql server running (SQL 2008). From my local sql server I would like to be able to query one of the remote instances of sql server. To do so, I understand that I need to specify a 4-part name.
…
Since there are multiple instances running on the remote machine, how do I specify the remote machine’s IP/name and the instance name when I have just one slot available in the above 4-part name.
Thank you for your help.
RM, you can do it like this:
select * from [hostname\instancename].databasename.dbo.tablename
Be sure to add the linked server using sp_addlinkedserver and sp_addlinkedsrvlogin, or use SSMS under Server Objects.
Dylan