Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

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


By:   |   Last Updated: 2018-11-19   |   Comments   |   Related Tips: More > 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")}

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.


Last Updated: 2018-11-19


next webcast button


next tip button



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.

View all my tips
Related Resources





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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools