Rename SQL server in a VM cloned host

Question

Rename SQL server in a VM cloned host

sqlfriend

SSC Guru

Points: 52781
More actions
June 13, 2017 at 12:38 pm

#323979

We are trying to set up a test environment for testing upgrade.
We did a VM clone from another VM machine snapshot which is the server we want o clone. It has SQL server 2014 installed and all those databases. We all use default instances, no named instance.
I will call the two servers VMNew and VMOriginal.
After our network admin cloned the server for me, I tried to rename the SQL server name the same as new computer name.
Because after cloning, the new SQL server name in VMNew is the same as the SQL serverName in old VMOriginal.
So I remote in VMNew, connect to SSMS, and connect the instance called VMOriginal, note I cannot connect to VMNew because it does not exist yet. I am going to run:
sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO
But my concern is by doing this, am I also make any changes on the SQL server instance name on the original server VMOriginal?
I only want to change on the new server which is my testing environment, I don't want the original server to be touched.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

this is what i use:
with extremely rare exceptions, I want @@servername to return the name of the machine.
So if I clone VMOriginal as VMNew, when i connect to it and select @@servername, it will return the wrong name...'VMNEW'

i use this snippet to check if i need to rename:
DECLARE @ServerName varchar(128) = CONVERT(varchar(128),@@SERVERNAME), @BiosName varchar(128) = CONVERT(varchar(128),Serverproperty('ComputerNamePhysicalNetBIOS')) select CASE WHEN @ServerName <> @BiosName THEN ' EXEC sp_dropserver ''' + @ServerName + '''; EXEC sp_addserver ''' + @BiosName + ''', ''local''; --change is not complete until service is restarted. ' ELSE '' END AS cmd

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!

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thanks Lowell.

When you say:
So if I clone VMOriginal as VMNew, when i connect to it and select @@servername, it will return the wrong name...'VMNEW'
Do you mean it will return the wrong name 'VMOriginal'

Thanks

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

The thing I am confused about using the SSMS to connect the original SQL server name, is it the one on VMOriginal or is it the instance on VMNew?

I cannot connect to new SQL name VMNew yet, since I have not run the stored procedure.
But I am afraid if I run it, it will change the SQL server name on both hosts.

Michael L John One Orange Chip Points: 27059 More actions · Answer 4

No, no, no.

In the VMNew, you are starting SSMS, and connecting to VMOriginal???
That's your first mistake.
When you click connect, database engine, in the server name field, pick <browse for more>. It will default to the local instance of the server.
Connect to that, and run the scripts to drop and recreate the server.
That will have no effect on the original server.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Lowell SSC Guru Points: 323518 More actions · Answer 5

when you connect via SSMS, it is using the dns and wins services to resolve an name/alias to a specific IP address; therefor since the server already existed, it would resolve to the original machine;

when the machine is cloned, it would get a new name and DHCP would assign a new IP address; if the server was using a static IP, the moment the machine got online, errors would occur due to two machines trying to use the same IP.

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!

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

Thank you.

I followed what you said to browse more, it has a local server the server name is VMNew, but when I connect to it, it shows error.

I go to SQL server configuration manager , I fount the SQL service is stopped.

And I tried to start it, it gives error: the request failed or the servie did not respond I a timely fasion.

I guess it is because we have the same instance name on the network.

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

Lowell - Tuesday, June 13, 2017 2:02 PM
when you connect via SSMS, it is using the dns and wins services to resolve an name/alias to a specific IP address; therefor since the server already existed, it would resolve to the original machine;
when the machine is cloned, it would get a new name and DHCP would assign a new IP address; if the server was using a static IP, the moment the machine got online, errors would occur due to two machines trying to use the same IP.

Then what is the best solution? Thanks

Or clone SQL server is really not a good option?
I just wanted to save some time by coloning, but I don't want to cause more issues, then later need a lot of fix so that it may use the same amount of time

sqlfriend SSC Guru Points: 52781 More actions · Answer 8

sqlfriend

SSC Guru

Points: 52781

June 13, 2017 at 2:12 pm

#1946655

I did a nslookup, they are different IPaddress

sqlfriend SSC Guru Points: 52781 More actions · Answer 9

Michael is right. I found out the reason I cannot to connect the SQL server VMNew using SSMS is because of the SQL service is not started. And I tried to start it, the error shows in even log logon failure. I digged more and found because the SQL service account we used is only limited to some computers not including the new computers. My network admin says the restriction make the server more secure, but I really don't prefer this method, sometimes it cause troubles and hard to trouble shoot.
Now after we added the access, the SQL started, and I can connect to VMNew and can run the rename server stored procedure

Thanks