Change the Virtual IP Address for a SQL Server Failover Cluster

By:   |   Updated: 2016-04-14   |   Comments (5)   |   Related: > Clustering


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

SQL Server Failover Cluster Manager

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

IP address properties window

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.

Property Window of network name

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

Virtual IP address change processing

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.

disconnected

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.

virtual IP 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".

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.

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

failover testing

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.

failover testing

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.

validate failover testing
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2016-04-14

Comments For This Article




Monday, July 16, 2018 - 7:55:36 AM - Khai Back To Top (76649)

Hi there,

Hope you can help me with a SQL cluster issue. We have changed the network subnet mask from 255 .255.255.0 to 255.255.252.0. we made the changes on the 2 nodes' nic to reflect the new subnet mask. But the SQL role will not come up on the cluster failover manager. I check the IP address resource and it still show the old subnet mask. Is there a way to fix this.

Thanks,

Khai


Friday, December 29, 2017 - 10:11:42 AM - william santana Back To Top (74568)

I have a MS SQL Failover Cluster 2012. When I stop the SQL services I lost all my shared drives. How could I stop the SQL services without losing the share drives?

 

Thank You,

 

William


Monday, July 17, 2017 - 4:59:57 PM - Stefano Back To Top (59494)

Hi. Can you describe same procedure for SQL 2016 AlwayOn DAG please! with AlwaysOn I only see the Listener in the Cluster GUI. However the virtual Cluster IP I assigned while create the FoC I see is assigned one of the Cluster Nodes, but not to the primary, but to the secondary.

thanks. 

.


Tuesday, April 19, 2016 - 1:15:02 PM - Sureshkumar Back To Top (41270)

 Hi Sir,

 

Thanks for the article.

I have doubt like , can we assign two ip address for sql cluster name. Why because to take backup from external tool they were asking for this configurations?

 

Thanks in advance.

Suresh

 

 

 


Saturday, April 16, 2016 - 3:32:20 AM - Perry whittle Back To Top (41241)

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

regards Perry 















get free sql tips
agree to terms