Renaming SQL Instance (SQL 2008 R2)

Question

Renaming SQL Instance (SQL 2008 R2)

kpwaran

SSC-Addicted

Points: 438
More actions
September 11, 2015 at 11:35 am

#311536

Please let me know how this can be achieved.
We are in the process of migrating WIN2003/SQL2005 to WIN2008/SQL2008
The server has been cloned and SQL server has been upgraded to 2008.
Now how to rename the SQL Server instance in 2008
1. RPT-SQL-DB ===> Running WIN2003/SQL2005 (SQL Instance name in this server is RPT-SQL-DB)
2. RPT-SQL-DB-CL ===> Clone of RPT-SQL-DB and upgraded to WIN2008/SQL2008 (SQL Instance Name is RPT-SQL-DB-CL)
3. Shutdown RPT-SQL-DB
4. Rename SQL Instance in RPT-SQL-DB-CL (RPT-SQL-DB-CL to RPT-SQL-DB), so no changes is required in the applications connection string.
Any help in this regard is highly appreciated.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it is not possible to rename an instance name. only the server itself.

so on my local machine named [LowellDev], i have LowellDev\SQL2014, LowellDev\SQL2012 and LowellDev\SQL2008R2 instances.

if i rename my box, they would have the new server name, ie NewDeveloper\SQL2014,NewDeveloper\SQL2012 and NewDeveloper\SQL2008R2; i might want to cleanup @@servername , but external connections would go to teh new machine name.

it is not possible to rename the instance portion, ie the .\SQL2014 to anything else.

from your description, it sound slike you just need to rename the original box[RPT-SQL-DB] to [RPT-SQL-DBOLD],

and rename [RPT-SQL-DB-CL] to [RPT-SQL-DB].

that's just right clicking on my computer, and selecting that spot where you rename/select domain

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kpwaran SSC-Addicted Points: 438 More actions · Answer 2

Hi Lowell,

Thank you so much for your response.

After renaming the server (RPT-SQL-DB-CL to RPT-SQL-DB), @@servername returns RPT-SQL-DB-CL.

As long as the existing connections goes to the new machine we are fine.

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 3

to fix @@servername, modify this example to the "right' name

while the changes are in the right tables, it will eventually require a stop and start of the SQL services to fix @@servername.

EXEC sp_dropserver 'GDC-SQL-P09OldName'

EXEC sp_addserver 'GDC-SQL-P09NewName', 'local'

select * from sys.servers where server_id = 0 --this server

select @@servername

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!