Papua New Guinea Finance

Jan 4 2018

Change the Virtual IP Address for a SQL Server Failover Cluster

#virtual #server #sql


#

Change the Virtual IP Address for a SQL Server Failover Cluster

Problem

If you have a requirement to change the virtual IP address of your SQL Server failover cluster, read this tip for step by step instructions.

Solution

The virtual IP address in a failover cluster is used to make a connection to SQL Server databases from your client applications instead of using the physical server name or physical IP address of the server. This allows failover to occur seamlessly. When a failover occurs, the ownership of the virtual IP address moves to the other node, so you don’t have to change the connection string for your application to work.

Change Virtual IP Address for a SQL Server failover cluster

Launch the Failover Cluster Manager to check the virtual IP address. You can see in the below screenshot where the virtual IP is highlighted along with SQL Server Network Name. Right click on the IP Address and choose “Properties”.

The IP Address Properties window will appear where you can see the static IP and subnet mask address (note: some of the info has been masked). You can see SQL Server virtual IP address is 10.XXX.XXX.X5 in the Static IP Address section. This is where you change the virtual IP for the SQL Server failover cluster. In our example, we will change the IP address from 10.XXX.XXX.X5 to 10.XXX.XXX.X7. Make sure that this new IP is unique on the network. Once you make the change, click “Apply”.

After you click “Apply”, a new window will appear and ask you to confirm the change. Click on “Yes” to proceed with the change as shown below.

After you click “Yes”, the resource will be recycled to apply the change.

If you were using a RDP session to connect to the server using the old virtual IP address, you will be disconnected at this step because of the IP change.

After you connect to the server again using the new virtual IP, you will find see the below info saying your new virtual IP is online.

Verify IP Address Change

Now that we have changed the virtual IP address of this failover cluster, you can verify this change by launching the failover cluster manager. You will be able to make a connection using the new virtual IP address, but Microsoft suggests taking this resources offline and then bringing back online post changes. To take offline, right click on the IP Address in failover cluster manager as shown below and click on “Take Offline”.

After the resources are offline, right click again and click on “Bring Online”. Once you bring the IP Address resource online, SQL Server will not come online automatically because the SQL Server services are set to Manual mode for a failover cluster environment, so you need to manually bring these services online.

Validate the IP Address Change with Failover Testing

The first step is to check the owner node where SQL Server is online. As you can see from the above screenshots, SQL Server is online from SQL-NODE1, but we can also run the below command to verify.

We can see SQL-NODE1 is the owner node for SQL Server. Now open the Failover Cluster Manager and do a failover. You can right click on the SQL Server Instance role and choose Move and then click on Best Possible Node. Since this is only a two-node cluster, it can only failover to the second node.

Once failover is successful, again connect to the SQL Server Instance by using the new virtual IP and run the T-SQL command below. We can see that SQL Server is now online from the second node which is SQL-NODE2.

Next Steps

Last Update: 2016-04-14

There is no need to constantly take the resources offline. When you changed the IP address the dialog asked you if you wanted to take the resource offline and you answered yes.

Constantly restarting your failover cluster instance is not going to make you popular so bear this in mind.

if you ever do offline the IP address you can bring all resources online by bringing the SQL agent resource online, the dependency list will bring all other resources online for you


Written by admin


Leave a Reply

Your email address will not be published. Required fields are marked *