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

By: Edwin Sarmiento   |   Updated: 2018-11-19   |   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.

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

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

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.

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

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.

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.

4. You will be prompted to delete the computer account. Click Yes.

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.

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

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

5. Click Apply. You will be prompted to confirm the action taken. Click Yes.

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")}

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 

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 

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.

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

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

5. Click Apply. You will be prompted to confirm the action taken. Click Yes.

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")}

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 

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 

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

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

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

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.

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.

About the author

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