Changing IP address of SQL Server 2008 R2

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

Good day to all,

In my case I did an IP address change on a production server due to a migration to a better server (more memory, SSD, several cores, etc..). When the change was applied none of the users or myself were able to connect remotely; The way I fixed it was by adding an exception on the firewall for inbound/outbound connections under port 1433 and everything worked fine after that.