SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Joy Smith San
Joy Smith San
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6468 Visits: 3200
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.
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24928 Visits: 6519
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


DennisPost
DennisPost
SSChasing Mays
SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)

Group: General Forum Members
Points: 619 Visits: 560
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
Joy Smith San
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6468 Visits: 3200
Thanks..

Is it going to be very troublesome ?..
Anyone did it before by any chance ?.
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24928 Visits: 6519
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96465 Visits: 38981
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

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sreekanth B
Sreekanth B
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1148 Visits: 2154
Just Curious...Why the App team wants Database Server host name and IP to be changed?
Joy Smith San
Joy Smith San
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6468 Visits: 3200
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
Joy Smith San
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6468 Visits: 3200
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
Joy Smith San
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6468 Visits: 3200


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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search