Changing NAME and IP ADDRESS of machine where SQL Server is hosted.

Question

Changing NAME and IP ADDRESS of machine where SQL Server is hosted.

Joy Smith San

SSC-Insane

Points: 24877
More actions
August 7, 2014 at 3:32 am

#291695

Hi All,
App team wants to change the NAME and IP ADDRESS of machine where SQL Server is hosted.
Apart from executing the below script, is there anything else that we need to do ?.
sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local
GO
Thanks,
Joy.

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

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

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

Only other one would be to test your TTL on the DNS entry, you will want to flush your DNS cache on all machines so that it picks up the new IP address quickly, otherwise if you can find a way to run "ipconfig /flushdns" on all machines in your environment at once then great, and please do let us know the solution as I have yet to find one.

You will then need a maintenance window which is larger than your TTL so that all machines in the environment can re-acquire the new IP address from your DNS servers.

DennisPost SSCrazy Points: 2691 More actions · Answer 2

anthony.green (8/7/2014)
Only other one would be to test your TTL on the DNS entry, you will want to flush your DNS cache on all machines so that it picks up the new IP address quickly, otherwise if you can find a way to run "ipconfig /flushdns" on all machines in your environment at once then great, and please do let us know the solution as I have yet to find one.
You will then need a maintenance window which is larger than your TTL so that all machines in the environment can re-acquire the new IP address from your DNS servers.

If you have a list of all the machines in your environmet you could script PsExec to run on each machine.

Or Group Policy with a startup or login script.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 3

Thanks..

Is it going to be very troublesome ?..

Anyone did it before by any chance ?.

Ant-Green SSC Guru Points: 113445 More actions · Answer 4

Sorry just re-read the post again, as your changing the name and IP of the server, it should create a new record in DNS which will not have been cached, so as long as you change the IP first, then change the Name, you should be good to go pretty quickly.

You could always pre-populate DNS with the new name and IP address.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Joy Smith San (8/7/2014)
Hi All,
App team wants to change the NAME and IP ADDRESS of machine where SQL Server is hosted.
Apart from executing the below script, is there anything else that we need to do ?.
sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local
GO
Thanks,
Joy.

One more thing:

sp_dropserver <old_name>;

GO

sp_addserver <new_name>, local

GO

Restart SQL Server

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 6

Just Curious...Why the App team wants Database Server host name and IP to be changed?

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 7

Thanks Anthony, Lynn... Good to go now.

And any IP cach related issues, we can take help of other teams like AD team.. So it should not trouble us much.

Thanks again for confirming...

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 8

sreekanth bandarla (8/7/2014)
Just Curious...Why the App team wants Database Server host name and IP to be changed?

That's a requirement under certain circumstances... It's inevitable...

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 9

One more thing:
sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local
GO
Restart SQL Server

Hi,

All went well and SQL Server is working fine as well. App team is able to connect to the DBs and is working fine.

However I observe plenty of below error in the error logs.

Error: 17806, Severity: 20, State: 14.

SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.

Error: 18452, Severity: 14, State: 1.

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

Do we need to reset SPN in AD ?. Tried googling, confused on the solution to go with in this particular case.

Please guide.

Thanks,

Smith.

Perry Whittle SSC Guru Points: 233852 More actions · Answer 10

You'll need to create a new SPN if you change the computername and the svc account does not have permission to do it automatically.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 11

Perry Whittle (9/3/2014)
You'll need to create a new SPN if you change the computername and the svc account does not have permission to do it automatically.

Thanks. I will need to create a new SPN for SQL Server in AD is it ?. I will follow the steps for the same.

By the way, SQL Server services are running under "LOCAL SYSTEM" account. Does that make any difference.

Thanks again.

Perry Whittle SSC Guru Points: 233852 More actions · Answer 12

if running under local system you shouldnt need to do anything

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 13

Perry Whittle (9/4/2014)
if running under local system you shouldnt need to do anything

OK.. Then why the error logged in errorlog ?

Thanks.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 14

Another thing to check... SQL Agent jobs. Are they all running OK? It has been some years ago but changing the server name used to break jobs because there was a column in one of the tables that holds the servername. When you change the servername it then will cause the jobs to fail because that servername no longer matches it.