May 14, 2012 at 11:28 am
How to rename a instance name, and let me know do we need to restart the server to be affected
May 14, 2012 at 11:34 am
you do this with sp_dropserver and sp_addserver
a restart will be required.
you will also have to handle the Admin login, msdb SQL Agent jobs and linked servers (if any)...
May 14, 2012 at 11:42 am
and by Admin login, i mean the NT SERVICE account.
and it its the default instance, you will have to change the server name too.
sometimes, it just easier to re-install. but not always.
May 14, 2012 at 11:57 am
Geoff A (5/14/2012)
you do this with sp_dropserver and sp_addservera restart will be required.
you will also have to handle the Admin login, msdb SQL Agent jobs and linked servers (if any)...
This information is incorrect. You cannot "rename" an instance. You can rename the server, but not the instance.
Jared
CE - Microsoft
May 14, 2012 at 12:04 pm
I successfully renamed instance with following steps
First collect the output of the current instance configuration
1. sp_helpserver
2. select @@servername
Execute the below query to change the instance name
1. sp_dropserver ‘old_name’
2. go
3. sp_addserver ‘new_name’ , ‘local’
4. go
RESTART SQL SEVER
Verify sql server instance configuration by running below queries
1. sp_helpserver
2. select @@servername
Thanks for the replies
May 14, 2012 at 12:08 pm
ndevr (5/14/2012)
I successfully renamed instance with following stepsFirst collect the output of the current instance configuration
1. sp_helpserver
2. select @@servername
Execute the below query to change the instance name
1. sp_dropserver ‘old_name’
2. go
3. sp_addserver ‘new_name’ , ‘local’
4. go
RESTART SQL SEVER
Verify sql server instance configuration by running below queries
1. sp_helpserver
2. select @@servername
Thanks for the replies
This did NOT rename the instance. It renamed the server. You cannot rename an instance. There is a big difference between the two. OLDSERVERNAME\INSTANCENAME becomes NEWSERVERNAME\INSTANCENAME.
Jared
CE - Microsoft
May 14, 2012 at 12:14 pm
@above yes you r right.. Please let me know to rename intance name
May 14, 2012 at 12:16 pm
re-install with new instance name is the recommened method.
May 14, 2012 at 12:16 pm
ndevr (5/14/2012)
@above yes you r right.. Please let me know to rename intance name
Again, you cannot rename an instance. You can only uninstall/re-install with a new named instance.
Jared
CE - Microsoft
May 14, 2012 at 12:42 pm
whats is the best practice for prod server Named Instance or Default instance? Please suggest (Pros and cons)
Thanks!
May 14, 2012 at 12:47 pm
ndevr (5/14/2012)
whats is the best practice for prod server Named Instance or Default instance? Please suggest (Pros and cons)Thanks!
Personally, I find no benefit to having a named instance if you only will have 1 instance. If you have more than 1 instance, it is up to you whether or not to have 1 default and other named or just to make them all named. Really, there aren't many general pros and cons.
Jared
CE - Microsoft
May 14, 2012 at 1:08 pm
I always use named instances, because I never know for sure if I may need to add another instance (permanently or temporarily) on the current server.
Since, as already noted, you cannot rename them, I find it easier to just give them all unique names. That way, if I ever need to have two instances temporarily reside on the same server, it's no problem.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2012 at 1:17 pm
RBarryYoung (5/14/2012)
I always use named instances, because I never know for sure if I may need to add another instance (permanently or temporarily) on the current server.Since, as already noted, you cannot rename them, I find it easier to just give them all unique names. That way, if I ever need to have two instances temporarily reside on the same server, it's no problem.
Just curious... What changes with your scenario if you have to add an instance? I, personally, still don't see any issues. If you have to have temporarily reside on the same server, there is still no issue with 1 named and 1 default.
Jared
CE - Microsoft
May 14, 2012 at 7:27 pm
SQLKnowItAll (5/14/2012)
RBarryYoung (5/14/2012)
I always use named instances, because I never know for sure if I may need to add another instance (permanently or temporarily) on the current server.Since, as already noted, you cannot rename them, I find it easier to just give them all unique names. That way, if I ever need to have two instances temporarily reside on the same server, it's no problem.
Just curious... What changes with your scenario if you have to add an instance? I, personally, still don't see any issues. If you have to have temporarily reside on the same server, there is still no issue with 1 named and 1 default.
If I am moving it from another server, then I don't have to give it a new instance name, it already has one. Since I name the instances (as opposed to the server names, which are under networking's control), then I always know what they are, no matter what server-name they are running on. And if two or more have to co-exist for a while (happens all the time), then I don't have to worry about changing the instance names.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2012 at 8:18 pm
RBarryYoung (5/14/2012)
SQLKnowItAll (5/14/2012)
RBarryYoung (5/14/2012)
I always use named instances, because I never know for sure if I may need to add another instance (permanently or temporarily) on the current server.Since, as already noted, you cannot rename them, I find it easier to just give them all unique names. That way, if I ever need to have two instances temporarily reside on the same server, it's no problem.
Just curious... What changes with your scenario if you have to add an instance? I, personally, still don't see any issues. If you have to have temporarily reside on the same server, there is still no issue with 1 named and 1 default.
If I am moving it from another server, then I don't have to give it a new instance name, it already has one. Since I name the instances (as opposed to the server names, which are under networking's control), then I always know what they are, no matter what server-name they are running on. And if two or more have to co-exist for a while (happens all the time), then I don't have to worry about changing the instance names.
I still don't understand, I guess. If you move a default instance to another machine, you can still make it the default instance. Are you saying, maybe, that in your case you probably already have a default instance on the "new" server and therefore can't move a default instance to that machine easily or have to add a name to it? In that case, I understand. I am not arguing, I am just trying to understand since I know you have more experience than I.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply