Upgrade or Migrate a SQL Server Failover Clustered Instance While Using the Same Virtual Network Name and Virtual IP Address (Option 1)

By:   |   Comments (6)   |   Related: > Clustering


Problem

We are upgrading a SQL Server failover clustered instance (FCI) to a higher version and on newer hardware. We do not know how many applications are connecting to the database so we would like to keep the same virtual network name and virtual IP address to prevent applications from failing after the upgrade. We would also like to do this with the least amount of downtime. How do we go about upgrading a SQL Server FCI to a higher version and on newer hardware while keeping the same virtual network name and virtual IP address?

Solution

One challenge with a database upgrade and migration is making sure that applications can still connect to the new platform after the upgrade. Without proper documentation, it would be challenging to identify all of the applications that use the database to change the database connection string.  That's why most organizations opt to reuse the same server name and IP address. This eliminates the need to track every single application connecting to the database to make connection string changes.

It's a bit tricky to do this with a SQL Server FCI, mainly, because of the other external dependencies like Active Directory and DNS. A SQL Server FCI virtual network name is created as a virtual computer object (VCO) in Active Directory. Active Directory, then, triggers the creation of the corresponding DNS entry with the virtual IP address for the virtual network name. So, you have to consider a lot of Active Directory-related concerns as part of the upgrade and migration.

I'm not a big fan of in-place upgrades because of the risks associated with it, especially with high availability solutions like a SQL Server FCI. You need to consider the amount of allowable downtime (maintenance window) during the upgrade. If an upgrade or a migration fails, the amount of time necessary to implement your rollback plan should be included in the maintenance window.  I highly recommend doing a side-by-side migration. This is where your old and new environments exist side-by-side, reducing the amount of risks associated with the upgrade. You can also perform tests on the new environment prior to going live into production without causing any issues to the old environment.

However, with a side-by-side migration, you cannot have the same virtual network name and virtual IP address running at the same time on both environments. You will end up with a duplicate name and IP address on the network and won't be able to run both of the SQL Server FCI. Hence, you would need different virtual network names and virtual IP addresses.

In this tip, I will show you how to upgrade or migrate a SQL Server FCI while using the same virtual network name and virtual IP address via a side-by-side migration.

For this example, I will be performing a side-by-side upgrade and migration of a SQL Server 2012 with Service Pack 4 FCI to a SQL Server 2017 FCI. Below are the details of the implementation.

Old Environment New Environment
WSFC OS: Windows Server 2012 R2 OS: Windows Server 2016
Nodes: TDPRD211 and TDPRD212 Nodes: TDPRD221 and TDPRD222
Cluster Name Object: TDPRDSQLCLS12 Cluster Name Object: TDPRDSQLCLS16
Virtual IP Address: 172.16.0.213/16 Virtual IP Address: 172.16.0.223/16
SQL Server FCI Instance Name: DEFAULT Instance Name: DEFAULT
Version: SQL Server 2012 with SP4 Version: SQL Server 2017 RTM
Virtual Network Name: PRODDBSQL10 Virtual Network Name: PRODDBSQL02
Virtual IP Address: 172.16.0.210/16 Virtual IP Address: 172.16.0.225/16

The query below is used to display the properties of the two SQL Server FCIs.

:CONNECT PRODDBSQL10
SELECT nodeName, status_description, is_current_owner FROM sys.dm_os_cluster_nodes ORDER BY 1
SELECT @@SERVERNAME as InstanceName, @@VERSION as Version, 
SERVERPROPERTY('IsClustered') as [Is_FCI]
SELECT @@SERVERNAME as InstanceName, LOCAL_NET_ADDRESS AS [IP Address Of SQL Server FCI] 
FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID
GO
:CONNECT PRODDBSQL02
SELECT nodeName, status_description, is_current_owner FROM sys.dm_os_cluster_nodes ORDER BY 1
SELECT @@SERVERNAME as InstanceName, @@VERSION as Version, SERVERPROPERTY('IsClustered') as [Is_FCI]
SELECT @@SERVERNAME as InstanceName, LOCAL_NET_ADDRESS AS [IP Address Of SQL Server FCI] FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID
ssms sql server properties

The shared disk layouts are the same for both SQL Server FCIs to maintain consistency.

failover cluster manager

Here's a high-level overview of the process involved in upgrading or migrating a SQL Server FCI using the same virtual network name and virtual IP address via a side-by-side migration.

  1. Take the SQL Server FCI offline (old environment)
  2. Delete the virtual computer object of the SQL Server FCI virtual network name (old environment) in Active Directory
  3. Rename the SQL Server FCI virtual network name (new environment)
  4. Change the SQL Server FCI virtual IP address (new environment)
  5. Bring the SQL Server FCI online (new environment)

At the end of the process, the new environment should have the old virtual network name and virtual IP address. In this example, PRODDBSQL10 and 172.16.0.210, respectively.

You don't have to change the cluster name object (CNO) of the Windows Server Failover Cluster (WSFC) since the applications only need the SQL Server FCI virtual network name and virtual IP address to connect to the databases.

Take the SQL Server FCI offline (old environment)

The first step is to take the SQL Server FCI offline. This is to make sure that no other applications can connect to the databases during the migration. This also prepares you to move the virtual network name and virtual IP address from the old environment to the new environment.

NOTE: Be sure to complete everything you need to migrate the databases from the old environment to the new environment prior to performing this step. You won't be able to access the old environment after taking the SQL Server FCI offline. This is also when your maintenance window officially starts.

To take the SQL Server FCI offline using the Failover Cluster Manager:

  1. Select the role that contains the SQL Server FCI. In this example, it's SQL Server (MSSQLSERVER).
  2. Right-click on the role and select Stop Role. This will take the SQL Server FCI offline.
failover cluster manager

To take the SQL Server FCI offline via PowerShell, use the Stop-ClusterGroup PowerShell cmdlet, passing the role name.

Stop-ClusterGroup "SQL Server (MSSQLSERVER)" -Cluster TDPRDSQLCLS12
powershell command stop cluster

Once the SQL Server FCI is offline, you can proceed to delete the virtual computer object of the SQL Server FCI virtual network name (old environment) in Active Directory.

Delete the virtual computer object of the SQL Server FCI virtual network name (old environment) in Active Directory

This is something that your Active Directory administrators need to do. You need to provide them with the SQL Server FCI virtual network name so they can proceed to delete the computer account.

NOTE: A word of caution. The size and complexity of your Active Directory logical architecture can prevent you from reusing the same virtual computer object after deletion due to Active Directory replication. You may be able to delete the SQL Server FCI virtual computer object from one of the domain controllers but it may still exist in other domain controllers until after the replication has triggered the other domain controllers to cleanup deleted Active Directory objects.

To make sure that you properly delete the virtual computer object of the SQL Server FCI, identify the domain controller that the WSFC nodes use to authenticate. You can use the nltest /dsgetdc:domainName command to identify the domain controller that authenticated the WSFC nodes. This is the domain controller that you connect to for deleting the SQL Server FCI virtual computer object.

windows command window

To delete the virtual computer object of the SQL Server FCI:

  1. Log in to the domain controller identified using the nltest /dsgetdc:domainName command and open Active Directory Users and Computers management console.
  2. Select the virtual computer object of the SQL Server FCI in the corresponding Organizational Unit (OU). In this example, it's PRODDBSQL10 and it's in the default Computers OU. Note the description of the computer account: Failover cluster virtual network name account.
  3. Right-click on the virtual computer object and select Delete. This will delete the virtual computer object of the SQL Server FCI virtual network name.
active directory users and computers
  1. You will be prompted to delete the computer account. Click Yes.
active directory domain services

Rename the SQL Server FCI virtual network name (new environment)

After deleting the virtual computer object of the SQL Server FCI, you can proceed to reuse it on the new environment.

To rename the SQL Server FCI virtual network name (new environment) using the Failover Cluster Manager:

  1. Select the role that contains the SQL Server FCI. In this example, it's SQL Server (MSSQLSERVER).
  2. Select the Resources tab to display all of the clustered resources in the role.
failover cluster manager
  1. Under Server Name, select the SQL Server FCI virtual network name. In this example, it's PRDDBSQL02. Right-click on the resource and select Properties. This will open the Properties dialog box of the SQL Server FCI virtual network name.
failover cluster manager
  1. In the General tab of the Properties dialog box, under the DNS Name: text box, change the virtual network name to the value of the old environment since you want to reuse the same name. In this example, PRDDBSQL02 is changed to PRDDBSQL10.
failover cluster manager
  1. Click Apply. You will be prompted to confirm the action taken. Click Yes.
failover cluster manager confirm action

Renaming the SQL Server FCI virtual network name (new environment) via PowerShell requires several steps and PowerShell cmdlets. First, you need to identify the cluster resource name associated with the SQL Server FCI virtual network name. Use the Get-ClusterResource PowerShell cmdlet for this.

Get-ClusterResource -Cluster TDPRDSQLCLS16 | Where {($_.OwnerGroup -eq "SQL Server (MSSQLSERVER)") -and ($_.ResourceType -eq "Network Name")}
powershell command get cluster resource

Next, you need to take the SQL Server FCI virtual network name offline before you can make any changes. Unlike when using the Failover Cluster Manager, this has to be explicitly done when using PowerShell. Use the Stop-ClusterResource PowerShell cmdlet to do this.

Stop-ClusterResource PRODDBSQL02 -Cluster TDPRDSQLCLS16 
powershell command stop cluster resource

Once the SQL Server FCI virtual network name has been taken offline, you can proceed to rename it. Use the Set-ClusterParameter PowerShell cmdlet to change the DnsName parameter. The DnsName parameter refers to the SQL Server FCI virtual network name.

Get-ClusterResource PRODDBSQL02 -Cluster TDPRDSQLCLS16 | Set-ClusterParameter DnsName PRODDBSQL10 
powershell command get cluster resource

Change the SQL Server FCI virtual IP address (new environment)

After renaming the SQL Server FCI virtual network name, you can proceed to change its corresponding virtual IP address on the new environment.

To change the corresponding SQL Server FCI virtual IP address (new environment) using the Failover Cluster Manager:

  1. Select the role that contains the SQL Server FCI. In this example, it's SQL Server (MSSQLSERVER).
  2. Select the Resources tab to display all of the clustered resources in the role.
failover cluster manager
  1. Under Server Name, select the SQL Server FCI virtual IP address. In this example, it's IP Address: 172.16.0.225. Right-click on the resource and select Properties. This will open the Properties dialog box of the SQL Server FCI virtual IP address.
failover cluster manager
  1. In the General tab of the Properties dialog box, under the Address: text box of the Static IP address option, change the virtual IP address to the value of the old environment since you want to reuse the same IP address. In this example, 172.16.0.225 is changed to 172.16.0.210
failover cluster manager
  1. Click Apply. You will be prompted to confirm the action taken. Click Yes.
failover cluster manager confirm action

Changing the SQL Server FCI virtual IP address (new environment) via PowerShell requires several steps and PowerShell cmdlets. First, you need to identify the cluster resource name associated with the SQL Server FCI virtual IP address. Use the Get-ClusterResource PowerShell cmdlet for this.

Get-ClusterResource -Cluster TDPRDSQLCLS16 | Where {($_.OwnerGroup -eq "SQL Server (MSSQLSERVER)") -and ($_.ResourceType -eq "IP Address")}
upgrade sql server failover clustered instance 018

NOTE: The cluster resource name is the name when the SQL Server FCI was installed. Changing the SQL Server FCI virtual network name does not change the cluster resource name. You have to explicitly change it - both for the virtual network name and virtual IP address - for consistency.

Similar to when changing the SQL Server FCI virtual network name, you need to take the SQL Server FCI virtual IP address offline before you can make any changes. Use the Stop-ClusterResource PowerShell cmdlet to do this.

Stop-ClusterResource "SQL IP Address 1 (PRODDBSQL02)" -Cluster TDPRDSQLCLS16 
upgrade sql server failover clustered instance 019

Once the SQL Server FCI virtual IP address has been taken offline, you can proceed to rename it. Use the Set-ClusterParameter PowerShell cmdlet to change the Address parameter. The Address parameter refers to the SQL Server FCI virtual IP address.

Get-ClusterResource “SQL IP Address 1 (PRODDBSQL02)” -Cluster TDPRDSQLCLS16 | Set-ClusterParameter Address 172.16.0.210 
upgrade sql server failover clustered instance 020

Bring the SQL Server FCI online (new environment)

After all of the changes have been done, you can proceed to bring the SQL Server FCI online on the new environment.

To bring the SQL Server FCI (new environment) online using the Failover Cluster Manager,

  1. Select the role that contains the SQL Server FCI. In this example, it's SQL Server (MSSQLSERVER).
  2. Right-click on the role and select Start Role. This will take the SQL Server FCI back online
upgrade sql server failover clustered instance 021

To bring the SQL Server FCI back online on the new environment via PowerShell, use the Start-ClusterGroup PowerShell cmdlet, passing the role name.

Start-ClusterGroup "SQL Server (MSSQLSERVER)" -Cluster TDPRDSQLCLS16
upgrade sql server failover clustered instance 022

Confirm that the SQL Server FCI virtual network name and virtual IP address have been updated.

upgrade sql server failover clustered instance 023

The final test is to verify application connectivity. Notice that while SQL Server Management Studio is still connected to PRODDBSQL10 using the IP address 172.16.0.210, it is no longer the SQL Server 2012 with Service Pack 4 FCI but rather the SQL Server 2017 FCI. Also, the node names reflect the WSFC of the new environment.

upgrade sql server failover clustered instance 024

Keep in mind that only virtual network names can be modified, not instance names.

Next Steps
  • Wait for a few minutes for the changes to propagate across the network.
  • Verify that all applications can successfully connect to the new SQL Server FCI using the old virtual network name and virtual IP address. You may need to flush the ARP cache on the client machine connecting to the SQL Server FCI. Since the virtual IP address now points to a different WSFC, it will have a different MAC address.
  • Explore more knowledge on SQL Server Database Administration Tips.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

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

View all my tips



Comments For This Article




Tuesday, August 23, 2022 - 3:12:25 PM - Edwin M Sarmiento Back To Top (90403)
Raj,

Renaming the old environment so you can reuse it on the new environment is totally fine. This article just shows one of many ways to do it.

Tuesday, August 23, 2022 - 5:15:16 AM - Raj Sarao Back To Top (90400)
Hi Edwin,
Instead of deleting the Virtual IP Address and Network name from old environment, can we just rename them and then use the original virtual ip address and network name to new environment.
This way , the old environment can still be used if required.
Any thoughts?
Thanks

Wednesday, November 20, 2019 - 5:05:57 PM - bass_player Back To Top (83153)

Dennis,

Possible? Yes. Should you? That really depends on what you are trying to accomplish.


Thursday, August 15, 2019 - 9:01:53 PM - Dennis Anderson Back To Top (82071)

Is it possible to not only move the name and ip address but to also move the actual database files including the system databases as well?  Or is it better to duplicate the environment as much as possible and then do a final backup/restore just prior to moving the name and ip address?


Thursday, March 7, 2019 - 9:08:43 PM - bass_player Back To Top (79209)

hef,

This would be challenging without an understanding of the entire architecture and the reasons why the firewall rules were put in place. There could be security and compliance requirements that prompted them to implement the firewall rules. It would be better to have a conversation with the external group that implemented the firewall rules before moving forward with your plan to migrate and upgrade your SQL Server FCI


Wednesday, February 20, 2019 - 4:24:42 PM - hef Back To Top (79075)

This is a fantastic post. Thank you. For our case, we inherited an old SQL Server FCI with a mess of network firewall rules that are currently controlled by various external groups. To avoid the headache of engaging them to manually recreate all these firewall rules, we would like to re-use the existing names and IPs for our WSFC server nodes and cluster (in addition to SQL Server's virtual network name) when migrating from the old nodes to the new nodes.

Can you please share your thoughts regarding how this should be done, including the proper sequence of steps? I'm inferring we'll need to engage the AD Admins for additional cleanup with the the WSFC nodes/cluster entries?

Lastly, we'll also need to be able to roll back to the "old" cluster if this migration fails for whatever reason. To do this, I presume we'll need to revert all the changes in the reverse order. Appreciate your thoughts.















get free sql tips
agree to terms