How to Change the SQL Server Instance Name after Renaming the Windows Host

Question

Post reply

How to Change the SQL Server Instance Name after Renaming the Windows Host

Shashank Srivastava

Mr or Mrs. 500

Points: 584
More actions
May 15, 2013 at 9:51 pm

#277544

Comments posted to this topic are about the item How to Change the SQL Server Instance Name after Renaming the Windows Host
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply

sqlpro Hall of Fame Points: 3859 More actions · Answer 1

if we drop server what happens to any existing user databases ? i assume they re-appear automatically after rename.

Shashank Srivastava Mr or Mrs. 500 Points: 584 More actions · Answer 2

Hi,

Nothing happens to existing databases. All databases and configuration will not be affected.

Shashank

Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/

kevaburg SSCoach Points: 18131 More actions · Answer 3

A strange phenomenon that I came across was based on the following error that occured directly after a server rename that was performed exactly as described in this article:

Server 'test-sql2' is not configured for DATA ACCESS

In order to rectify the problem I ran the following statement:

exec sp_serveroption 'test-sql2', 'data access', true;

And that was the problem resolved.

My only advice to those planning to rename a SQL Server is to test the renaming process on a disposable server so that there is nothing in your environment that could be adversely affected. The resolution to this problem took an hour of zero productivity on the server to resolve!

Shashank Srivastava Mr or Mrs. 500 Points: 584 More actions · Answer 4

Thanks for the advice. Learnt a new scenario and issue today.

Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/

OldFashionGang SSC Enthusiast Points: 121 More actions · Answer 5

Let me reduce original article to this: http://msdn.microsoft.com/en-us/library/ms143799(v=sql.110).aspx

Shashank Srivastava Mr or Mrs. 500 Points: 584 More actions · Answer 6

Nice connection OldFashionGang.

Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/

steve.france SSC Journeyman Points: 93 More actions · Answer 7

Hi

Can someone point me in the right direction for doing this procedure for a named instance in a cluster?

Forgot to add...

I usually just create a new instance and move eveything over, but if its possible to do a rename it would save a lot of time.

thank you

Steve

Shashank Srivastava Mr or Mrs. 500 Points: 584 More actions · Answer 8

Hi Steve,

As far as I think, change of named instance is not possible. You need to uninstall and install again.

Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/

steve.france SSC Journeyman Points: 93 More actions · Answer 9

Shashank Srivastava (5/16/2013)
Hi Steve,
As far as I think, change of named instance is not possible. You need to uninstall and install again.

Thank you Shashank.

For 2012 it looks possible

http://msdn.microsoft.com/en-GB/library/ms178083.aspx

But for 2008 I can't find anything.

thanks

Steve

steve.france SSC Journeyman Points: 93 More actions · Answer 10

steve.france (5/16/2013)
Shashank Srivastava (5/16/2013)
Hi Steve,
As far as I think, change of named instance is not possible. You need to uninstall and install again.
Thank you Shashank.
For 2012 it looks possible
http://msdn.microsoft.com/en-GB/library/ms178083.aspx
But for 2008 I can't find anything.
thanks
Steve

Actually I misread that.. on the virtual name can be ammended.

Oh well.. a lot of work coming up then!

thanks

S

Shashank Srivastava Mr or Mrs. 500 Points: 584 More actions · Answer 11

Hi Steve,

Please have a look at below article.

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

We can change the SQL Server virtual name but not the instance name.

Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/

monjearse Grasshopper Points: 21 More actions · Answer 12

Nice trick, I thought I would lost my db's and re-install SQLServer again.

Regards

Shashank Srivastava Mr or Mrs. 500 Points: 584 More actions · Answer 13

Hi Steve,

You can change the SQL server virtual name and not the instance name.

Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/

Phil Brammer Ten Centuries Points: 1135 More actions · Answer 14

steve.france (5/16/2013)
Shashank Srivastava (5/16/2013)
Hi Steve,
As far as I think, change of named instance is not possible. You need to uninstall and install again.
Thank you Shashank.
For 2012 it looks possible
http://msdn.microsoft.com/en-GB/library/ms178083.aspx
But for 2008 I can't find anything.
thanks
Steve

No, it is not possible. In the article you linked:

"The name of the virtual server is always the same as the name of the SQL Network Name (the SQL Virtual Server Network Name). Although you can change the name of the virtual server, you cannot change the instance name. For example, you can change a virtual server named VS1\instance1 to some other name, such as SQL35\instance1, but the instance portion of the name, instance1, will remain unchanged."