Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Change the Virtual IP Address for a SQL Server Failover Cluster


By:   |   Read Comments (2)   |   Related Tips: More > 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


Last Update:






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.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 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

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 


Learn more about SQL Server tools