Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Changing NAME and IP ADDRESS of machine where SQL Server is hosted. Expand / Collapse
Author
Message
Posted Thursday, August 7, 2014 3:32 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:13 AM
Points: 1,532, Visits: 2,577
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.
Post #1600524
Posted Thursday, August 7, 2014 3:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:20 AM
Points: 5,231, Visits: 5,106
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
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

Post #1600532
Posted Thursday, August 7, 2014 7:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 2:56 AM
Points: 52, Visits: 237
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.
Post #1600615
Posted Thursday, August 7, 2014 8:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:13 AM
Points: 1,532, Visits: 2,577
Thanks..

Is it going to be very troublesome ?..
Anyone did it before by any chance ?.

Post #1600674
Posted Thursday, August 7, 2014 8:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:20 AM
Points: 5,231, Visits: 5,106
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
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

Post #1600685
Posted Thursday, August 7, 2014 10:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 23,396, Visits: 32,224
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




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)
Post #1600770
Posted Thursday, August 7, 2014 11:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 284, Visits: 1,145
Just Curious...Why the App team wants Database Server host name and IP to be changed?
Post #1600782
Posted Thursday, August 7, 2014 7:28 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:13 AM
Points: 1,532, Visits: 2,577
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...
Post #1600991
Posted Thursday, August 7, 2014 7:29 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:13 AM
Points: 1,532, Visits: 2,577
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...
Post #1600992
Posted Tuesday, September 2, 2014 10:48 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:13 AM
Points: 1,532, Visits: 2,577


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.

Post #1609875
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse