Share via

How To: Migrate from Failover Cluster Instances and Database Mirroring to SQL Server AlwaysOn – Part 2

  • Article

Writer: Cephas Lin (Microsoft)

Contributors: Oleg Bulanyy (Microsoft), Jimmy May (Microsoft)

NOTE: SQL Server Failover Clustering has been renamed AlwaysOn Failover Cluster Instances (FCI) in SQL Server 2012. For simplicity, the term FCI in this paper applies to either SQL Server Failover Clustering in SQL Server 2008 R2 or AlwaysOn FCI in SQL Server 2012.

This blog is the continuation of a blog series that shows you how to perform the following end-to-end HADR migration scenario of SQL Server inside a test environment:

  • From a SQL Server 2008 R2 solution: SQL Server Failover Clustering for high availability and database mirroring for disaster recovery
  • To a SQL Server 2012 solution: AlwaysOn Failover Cluster Instances for high availability and AlwaysOn Availability Groups for disaster recovery

In Part 1, you created the base configuration. In this installment, you will apply the migration steps to the base configuration you created. Without further delay, let's start!

Step 1: Deploy FCI3 as Intermediate FCI

Role: Database Administrator

First, you create a temporary FCI in 10.1.1.X/24. Following the procedures at Quickly Setup SQL Server AlwaysOn Failover Cluster Instance (FCI) in Hyper-V, deploy FCI3 in 10.1.1.X/24 using the specifications in the table below. The steps in the blog post can be applied to both physical and Hyper-V environments.

WSFC Cluster Name

Cluster3

SQL Server Version

SQL Server 2012

FCI Network Name

FCI3

Instance Name

TEMP_INST

Machine Names

TEMP1

TEMP2

File Server Name

FILE1

Cluster Service IP Address

10.1.1.133

SQL Service Account

CORP\SQLSvc3

SQL Agent Account

CORP\SQLAgent3

Figure 5 shows the details of FCI3 after you create it, as viewed in Failover Cluster Manager. Similar to FCI1 and FCI2, FCI3 has a quorum model of Node and Disk Majority.

Figure 5 FCI3 as Shown in Failover Cluster Manager

FCI3 is used in this walkthrough as a temporary replacement FCI to preserve the two-FCI configuration when you are reconfiguring FCI1 or FCI2. You can perform migration without the temporary FCI and still preserve the high availability of your databases, but note that when FCI1 or FCI2 is being reconfigured or upgraded using this alternate approach, the high availability of your database instance rests upon the single remaining FCI.

In addition, FCI3 is used to set up the availability group in the SQL Server AlwaysOn solution, and the WSFC cluster used to host FCI3 (Cluster3) will become a permanent fixture in the final configuration.

Step 2: Redirect Database Mirroring to FCI3

Role: Database Administrator

In this section, you will remove the existing database mirroring session between FCI1\LIVE_INST and FCI2\LIVE_INST and configure a new database mirroring session between FCI1\LIVE_INST and FCI3\TEMP_INST. These actions effectively remove FCI2 from the HADR solution so that it can be reconfigured, while still maintaining two FCIs in your HADR database system. However, removing FCI2 also means that the system does not have any disaster recovery capability until FCI2 is reintroduced to your HADR system.

Perform the following steps while connected to FCI1\LIVE_INST and FCI3\TEMP_INST.

  1. Prepare the mirror databases for DB1 and DB2 on FCI3\TEMP_INST.
  2. On FCI1\LIVE_INST, remove the existing database mirroring sessions for DB1 and DB2.
  3. On FCI1\LIVE_INST, establish the database mirroring sessions on DB1 and DB2 using the specifications in Table 7.

Primary FCI\Instance

FCI1\LIVE_INST

Mirror FCI\Instance

FCI3\TEMP_INST

Primary Service Account

CORP\SQLSvc1

Mirror Service Account

CORP\SQLSvc3

Primary Endpoint

TCP://fci1.corp.contoso.local:5022

Mirror Endpoint

TCP://fci3.corp.contoso.local:5022

At this point, FCI2 is no longer being used, and it can be reconfigured to be part of Cluster3.

Step 3: Reconfigure FCI2 with SQL Server 2012

Role: Database Administrator

Next, you reconfigure FCI2 by reinstalling Windows Server and SQL Server completely on the FCI2 machines. You cannot simply upgrade SQL Server 2012 on the existing configuration for FCI2 for the following reasons:

  • The SQL Server AlwaysOn solution requires that all the machines to be joined to the new WSFC cluster (Cluster3).
  • As each machine can be joined to only one WSFC cluster at the time, the existing WSFC cluster (Cluster2) must be destroyed.
  • Since FCI2 is clustered service hosted by Cluster2, destroying Cluster2 also renders FCI2 unusable.
  • Previously, FCI1 and FCI2 can share the same instance name in a database mirroring configuration because they run in separate WSFC clusters. However, the fact that they ultimately participate in the same availability group requires that they run in a single WSFC cluster, each having a unique instance name.

While you can manually delete both the FCI2 clustered service, the Cluster2 cluster, and the existing SQL Server installation, it can add unnecessary complication and troubleshooting to your migration process. Unless you have a compelling reason to preserve the existing Windows installation, it is simpler just to reinstall Windows.

Following the procedures at Quickly Setup SQL Server AlwaysOn Failover Cluster Instance (FCI) in Hyper-V, deploy FCI2 using the specifications in the table below. The steps in the blog post can be applied to both physical and Hyper-V environments.

Due to the reasons described previously, make sure you use the steps following the table to join machines to Cluster3.

WSFC Cluster Name

Cluster3*

SQL Server Version

SQL Server 2012

FCI Name

FCI2

Instance Name

LIVE_INST2

Machine Names

REMOTE1

REMOTE2

File Server Name

FILE2

Cluster Service IP Address

10.2.2.222

SQL Service Account

CORP\SQLSvc2

SQL Agent Account

CORP\SQLAgent2

*Instead of recreating Cluster2, you join the machines to the new cluster Cluster3. Follow the instructions below:

  1. When you initialize the shared disks, do not create a new iSCSI target for the quorum disk because the existing Cluster3 cluster already has a quorum disk. Instead, for the iSCSI target of the existing quorum disk, add the IP addresses of REMOTE1 and REMOTE2 to the iSCSI Initiators Identifiers page.
  2. When you configure REMOTE1, use the following steps instead of the ones in the Configure [MACHINE_NAME1] section:

    1. Log in as CORP\Install.

    2. Join the machine to the Cluster3 cluster with the following PowerShell command.

      Import-Module FailoverClusters

      Get-Cluster Cluster3 | Add-ClusterNode REMOTE1,REMOTE2

      Move-ClusterGroup "Available Storage" -Node REMOTE1

      Get-ClusterAvailableDisk | Add-ClusterDisk

      Test-Cluster

      After the Add-ClusterNode command runs, ignore the errors for the three missing SQL Server DLLs. These DLLs are added when you install FCI2 on the nodes.

      Note: The Move-ClusterGroup command moves the Available Storage resource group to REMOTE1. This resource group holds all newly added shared storage. Unless either REMOTE1 or REMOTE2 owns this resource group, you cannot bring the shared disk online after you add it to the cluster because it cannot be brought online on any other node in the cluster.

    3. Set the appropriate owners of the existing clustered service SQL Server (TEMP_INST), including all the service resources, with the following PowerShell commands. Setting the proper ownership ensures that FCI3 does not attempt to failover to REMOTE1 or REMOTE2, which cannot host the TEMP_INST SQL Server instance.

      Get-ClusterGroup "SQL Server (TEMP_INST)" | Set-ClusterOwnerNode -Owners TEMP1,TEMP2

      Get-ClusterGroup "SQL Server (TEMP_INST)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners TEMP1,TEMP2

    4. Remove the quorum votes for the remote nodes. Setting the proper node votes ensures that the WSFC cluster correctly determines the quorum health and makes proper decisions whether to fail over or take the cluster offline during a failure.

      (Get-ClusterNode "REMOTE1").NodeWeight = 0

      (Get-ClusterNode "REMOTE2").NodeWeight = 0

      Your Cluster3 quorum should now look as follows:

      • TEMP1: 1 vote
      • TEMP2: 1 vote
      • REMOTE1: 0 vote
      • REMOTE2: 0 vote
      • Quorum disk: 1 vote

      IMPORTANT: You should reconsider your quorum model carefully to maintain optimal WSFC quorum health both when and after adding the nodes. For more information, see the WSFC Quorum Modes and Voting Configuration section in Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery.

    5. Change to the directory that contains the setup.exe file for SQL Server 2012, then install FCI2 using the following command. Note that even though you initialize the data disk to use drive letter Z based on the blog instructions, the WSFC service changes it to a different drive letter when adding to because that drive letter is already used in the cluster (by FCI3). You must use the new drive letter that is assigned to your shared disk.

      .\Setup.exe /QS /ACTION=InstallFailoverCluster /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQL, ADV_SSMS /INSTANCENAME=LIVE_INST2 /SQMREPORTING=0 /FAILOVERCLUSTERDISKS="Cluster Disk 3" /FAILOVERCLUSTERIPADDRESSES="IPv4;10.2.2.222;Cluster Network 2;255.255.255.0" /FAILOVERCLUSTERNETWORKNAME=FCI2 /AGTSVCACCOUNT="CORP\SQLAgent2" /AGTSVCPASSWORD= [PASSWORD] /INSTALLSQLDATADIR=" [SHAREDDISK_DRIVELETTER] :\" /SQLSVCACCOUNT="CORP\SQLSvc2" /SQLSVCPASSWORD= [PASSWORD] /SQLSYSADMINACCOUNTS="CORP\Install" "CORP\DBAdmin"

    6. Run the following commands to open the firewall for SQL Server connectivity.

      netsh advfirewall firewall add rule name='SQL Server Browser (UDP-In 1434)' dir=in action=allow protocol=UDP localport=1434 profile=domain

      netsh advfirewall firewall add rule name='SQL Server (TCP-In)' program='C:\Program Files\Microsoft SQL Server\MSSQL11.LIVE_INST2\MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCP profile=domain

    7. Run the following commands to set the proper ownership for the clustered service, including all the service resources.

      Get-ClusterGroup "SQL Server (TEMP_INST)" | Set-ClusterOwnerNode -Owners TEMP1,TEMP2

      Get-ClusterGroup "SQL Server (TEMP_INST)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners TEMP1,TEMP2

      Get-ClusterGroup "SQL Server (LIVE_INST2)" | Set-ClusterOwnerNode -Owners REMOTE1,REMOTE2

      Get-ClusterGroup "SQL Server (LIVE_INST2)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners REMOTE1,REMOTE2

      Get-ClusterGroup "Cluster Group" | Get-ClusterResource | Set-ClusterOwnerNode -Owners TEMP1,TEMP2,REMOTE1,REMOTE2

      The resource group "Cluster Group" contains cluster-level resources for Cluster3, including the quorum disk. The last command ensures that that all nodes have access to the quorum disk.

Once FCI2 has been reconfigured, it should look similar to Figure 6 when viewed in Failover Cluster Manager. Note that Cluster3 now has two cluster networks and two clustered services.

Figure 6 FCI2 after Reconfiguration as Shown in Failover Cluster Manager

Step 4: Make FCI3 the New Primary FCI and Create Availability Group

In this section, you will perform a failover to make FCI3 the new primary partner and stop database mirroring. Then, you will establish an availability group with the temporary FCI as the primary replica and the remote FCI as the secondary replica. Ultimately, this availability group becomes a permanent fixture in the post-migration solution.

Enable AlwaysOn Availability Groups on FCI3 and FCI2

Role: Database Administrator

First, you enable the AlwaysOn Availability Groups feature on FCI3 and FCI2. You will perform this step now so that you do not have to restart FCI3 after it becomes the primary FCI. To do this, open a SQL Server PowerShell window from SSMS and run the following commands:

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\FCI3\TEMP_INST -Force

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\FCI2\LIVE_INST2 -Force

Create Availability Group on FCI3

Role: Database Administrator

Next, you will perform the database mirroring failover to FCI3 and configure the availability group that you will use for the SQL Server AlwaysOn solution.

IMPORTANT: Your client application will experience downtime in this part of the migration process. The duration of the downtime depends on your efficiency, the database size, the speed of file transfer, and other factors.

  1. On FCI1\LIVE_INST, perform a manual database mirroring failover for both DB1 and DB2, from FCI1 to FCI3.

    IMPORTANT: This is the beginning of downtime. The console application on CLIENT should begin to show Error:... for write access failures.

  2. For client access, connect to FCI3\TEMP_INST in SSMS, and then add CORP\SQLClient as a login and give it the following database memberships for both DB1 and DB2:

    • db_datareader
    • db_datawriter

  3. On FCI3\TEMP_INST, remove the existing database mirroring sessions for both DB1 and DB2.

  4. On FCI2\LIVE_INST2, prepare the secondary DB1 and DB2 databases for the availability group you will create, by taking both full and log backups of the databases from FCI3\TEMP_INST and restoring them on FCI2\LIVE_INST2. When restoring the databases, make sure that the WITH NO RECOVERY option is used.

  5. On FCI3\TEMP_INST, right-click AlwaysOn High Availability and click New Availability Group Wizard.

  6. In the New Availability Group Wizard, click Next.

  7. In the Availability group name box, type AG1, then click Next.

  8. Select DB1 and DB2, then click Next.

  9. In the Replicas tab, click Add Replica.

  10. In the Server name box, type FCI2\LIVE_INST2, then click Connect.

  11. Select Synchronous Commit (Up to 3) for FCI3\TEMP_INST.

  12. Select Readable Secondary=Yes for both FCI3\TEMP_INST and FCI2\LIVE_INST2.

  13. In the Backup Preferencestab, change the backup priority of the nodes as follows:

    • FCI3\TEMP_INST: 50
    • FCI2\LIVE_INST2: 60

  14. In the Listener tab, select Create an availability group listener, and then specify the following settings for the listener:

    • Listener DNS Name: AG1
    • Port: 10000
    • Network Mode: Static IP
    • IPv4 Address for 10.1.1.X/24: 10.1.1.121
    • IPv4 Address for 10.2.2.X/24: 10.2.2.242

  15. Click Next.

  16. Select Join only for data synchronization preference, and then click Next.

    NOTE: While the Full option automatically prepares the secondary replicas by taking the necessary backups, it is not designed for very large databases that may exist in a Tier-1 environment. For more information on the prerequisites for AlwaysOn Availability Groups, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).

  17. Once validation finishes without errors, click Next, then click Finish.

Once the availability group configuration is finished, you can monitor the availability group health in SSMS by right-clicking AlwaysOn High Availability and selecting Show Dashboard. The view of the AlwaysOn Dashboard is shown in Figure 7.

Figure 7 AlwaysOn Dashboard after Availability Group Creation

The Failover Cluster Manager view of AG1 is shown in Figure 8. The Cluster3 cluster now contains three clustered services: two FCI services and one availability group service. The WSFC service manages aspects the resource group ownership and monitors the quorum health for the availability group. Note that other than quorum model and health management, you should avoid modifying the clustered service properties of the availability group in the Failover Cluster Manager. For example, do not perform availability group failovers using the Failover Cluster Manager. You can use the AlwaysOn Dashboard to perform this task. For more information, see DO NOT use Windows Failover Cluster Manager to perform Availability Group Failover.

Figure 8 New Availability Group as Shown in Failover Cluster Manager

At this point, CORP\SQLClient still does not have access to the databases on FCI2\LIVE_INST2. If you must fail over to FCI2\LIVE_INST2 due to a failure during or after migration, you must perform the following steps immediately after the failover so that the client applications can recover database connectivity.    

  1. Add CORP\SQLClient as a login.
  2. Give CORP\SQLClient the following database memberships for both DB1 and DB2:
    • db_datareader
    • db_datawriter

Reconfigure the Client Application

Role: Application Developer

Next, you reconfigure your client application to re-establish database connectivity. In a real-life migration scenario, you can proceed with this step as soon as downtime begins so that your client applications can re-establish database connectivity as soon as the availability group is online.

  1. If you have not already done so, close the running console application on CLIENT.

  2. In Program.cs, locate the following line:

    const string connString = @"Server=FCI1\LIVE_INST;Failover Partner=FCI2\LIVE_INST;Initial Catalog=DB1;Integrated Security=True";

  3. Replace this line with the following code to use the availability group listener and port number you configured:

    const string connString = @"Server=tcp:AG1,10000;Database=DB1;IntegratedSecurity=SSPI";

  4. Run the application again by opening the Debug menu and clicking Start Debugging.

    IMPORTANT: The console application on CLIENT should now show Success:... for write access. This is the end of downtime.

You have now created the availability group, lost and recovered client connectivity, and recovered HADR capabilities. The only remaining tasks are reconfiguring FCI1, add it to the availability group, and remove the temporary FCI. I will cover that in the next installment. Stay tuned!