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

  • 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.

  • 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.

  • 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.

  • Thanks..

    Is it going to be very troublesome ?..

    Anyone did it before by any chance ?.

  • 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.

  • 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

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

  • 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...

  • 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...

  • 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.

  • 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" 😉

  • 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.

  • if running under local system you shouldnt need to do anything

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

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

  • 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.

  • 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.

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

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