Pruebas Varias

Archives

Company

Managed Fusion

Resume

Work History

Contact

Get In Touch

11

FEB

2008

How To: Change Instance Name Of SQL Server

27 CommentsUncategorized

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:

1

2

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:

1

2

3

4

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:

1

2

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: database, How To, NAT, Network, SQL Server, T-SQL

share this post

Share what you have learned here with other developers.

written by

Nick Berardi subscribe

If you found this post valuable and would like to see more like it you can follow me.

27 Responses to “How To: Change Instance Name Of SQL Server”

ReplyGreg Harrissays:April 25, 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!!!

ReplyNick Berardisays:April 25, 2008 at 8:35 am

No problem.

ReplyRamy Mahroussays:November 5, 2008 at 1:17 am

Thank you so much, it really helped me

ReplySatishsays:December 9, 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

Replyblasesays:June 10, 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

ReplyRaosays:June 25, 2009 at 12:48 pm

Hi

Are you using SQL express edition?

regards

Replyamjsays:June 30, 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

ReplyNick Berardisays:July 1, 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

ReplyThangncsays:July 10, 2009 at 2:04 am

Thanks Nick,

It’s great!

ReplyRelentlessMikesays:October 13, 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.

ReplyJiteshsays:December

...