Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to know if there are any dangers or relevant precautions before changing the IP address of a SQL Server 2008 R2 box.

We have a built a virtual PC with Windows Server 2008 R2 and SQL Server 2008 R2. The purpose of this machine is run a job that restores a backup file (copied by a different server), manipulates it, backs it up again and then copies it out to other servers. We've run it a few times in our test/dev DMZ and are now considering deployment.

The simplest option would be to re-IP it. The server name would remain intact. My problem is a colleague has suggested this is unsafe (to re-ip a SQL Server). My question is

a) Is this true for the OS and SQL I'm using? (Win 2008 R2 & SQL 2008 R2?)

b) Is it true for any other combinations of OS and/or SQL?

c) Is there anything special we should do in preparation?

My research thus far indicates it will be fine but I trust the folks of DBA StackExchange more than those social MSDN people.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9844e0c1-b57e-4e0c-88cf-7cb51cc55e07/change-sql-server-ip-address

http://www.sqlservercentral.com/Forums/Topic1164450-391-1.aspx

share|improve this question
    
If you Re-IP it things will break just as Wind Raven stated. Another thing you would have to do is check with Networking or Sys Admin to make sure the IP is available, if you decided to change it. –  3rd_Eye34689 Feb 24 at 18:27
    
I'm a DBA, I'd not change an IP myself that would be ludicrous! –  Paul Feb 26 at 10:13
    
Anyone reading in the future, the change went without issue. –  Paul Jun 4 at 8:15

3 Answers 3

up vote 2 down vote accepted

Changing the IP Address for a machine running SQL Server is not a problem at all. You'll need to restart the SQL Server after changing the IP Address so that the SQL Server will start listening on the new IP address. But that's it.

If no users actually connect to the machine then there's nothing else to do.

If users did connect to the machine they'd need to refresh their DNS cache on their workstations by running "ipconfig /flushdns" or by simply restarting their computers.

Unless you've done something very unusual with the TCP listeners within SQL there's nothing else that needs to be done with SQL Server other than the quick restart.

share|improve this answer

The only issue I see is if the server is referred to by IP address in any place (Job, Connection string, or other places). That reference will break once the IP changes. In my home lab my one of my SQL Server boxes is on DHCP and can get a different IP address and break things since its referred to by IP (this is intentional unintentional breakage for trouble shooting broken things, Production should use Static IP's).

Changing the IP address is actually my companies plan for disaster recovery (very simple plan). We are a small shop and clustering is more complicated than what we need. So we have 2 servers and if one dies we simply swap the IP addresses of the primary and backup. This works because we reference the specific IP address in every single connection string used in our application and not the server name.

share|improve this answer
    
Aye. Luckily this is a new server so references are minimal and by name. –  Paul Feb 18 at 10:02

You'll typically be fine if it's a standalone SQL Server - just make sure DNS updates happen quickly enough else users won't be able to get on.

If this is a SQL Cluster however you need to make changes to the failover cluster configuration as well; see Change the IP Address of a Failover Cluster Instance on MSDN for full details.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.