Partager via

How To: Migrate to AlwaysOn from Prior Deployments Combining Database Mirroring and Log Shipping – Part 2

  • Article

Writer: Cephas Lin (Microsoft)

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

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: Database mirroring for high availability and log shipping for disaster recovery
  • To a SQL Server 2012 solution: Availability Group for both high availability and disaster recovery

In How To: Migrate to AlwaysOn AlwaysOn from Prior Deployments Combining Database Mirroring and Log Shipping – 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: Upgrade Log Shipping Secondary Database Server

Role: Database Administrator

The first step in the migration process is to upgrade the log shipping secondary database server (REMOTE1). If this server is a hot standby server (databases restored with the WITH STANDBY option), then you must first reconfigure all restore jobs to use the WITH NORECOVERY option before you can proceed. In this case, you have already configured the restore jobs to use the WITH NORECOVERY option, so you can simply proceed with the migration.

  1. Log in to REMOTE1 as CORP\Install.
  2. Run SQL Server Setup to upgrade the default SQL Server instance to SQL Server 2012. For more information, see Prerequisites.
  3. Verify that the log shipping copy and restore jobs continue to run.
  4. In Server Manager, add the Failover Clustering feature.
  5. Install KB 2494036.

Step 2: Upgrade the Witness Server

Role: Database Administrator

The next server to be upgraded is the witness server in the database mirroring configuration (WITNESS). Note that when upgrading the servers in the database mirroring configuration, you are asked to remove WITNESS from the database mirroring session and rejoin it to the session multiple times. This procedure is based on the assumption that the upgrade process takes multiple days due to the additional verification steps you may wish to take after each server is upgraded. In this case, it is best to retain the automatic failover capability offered by WITNESS in the database mirroring session. However, if you plan to complete the upgrade within a few hours, there are multiple possible paths:

  • Remove WITNESS from the session, then proceed to upgrade PRIMARY1 and PRIMARY2 while upgrading WITNESS, and then rejoin WITNESS to the session.
  • Remove WITNESS from the session permanently. Upgrade PRIMARY1 and PRIMARY2, and then configure the availability group immediately.

When making a decision on your specific upgrade path, consider carefully how your decision changes the HADR capabilities of your system before, during, and after each upgrade step.

Follow the steps below to upgrade WITNESS.

  1. Log in to WITNESS as CORP\Install.
  2. Run SQL Server Setup to upgrade the default SQL Server instance to SQL Server 2012. For more information, see Prerequisites.
  3. Verify that both PRIMARY1 and PRIMARY2 are reconnected to WITNESS in the database mirroring session.

Step 3: Upgrade the Mirror Database Server

Role: Database Administrator

The next server to be upgraded is the mirror server in the database mirroring configuration (PRIMARY2).

  1. On PRIMARY1, remove the WITNESS server from the database mirroring configuration.
  2. Log in to PRIMARY2 as CORP\Install.
  3. Run SQL Server Setup to upgrade the default SQL Server instance to SQL Server 2012. For more information, see Prerequisites.
  4. Verify that the database mirroring session is synchronized.
  5. Return the WITNESS server to the database mirroring configuration.
  6. In Server Manager, add the Failover Clustering feature.
  7. Install KB 2494036.

Step 4: Upgrade the Primary Database Server

Role: Database Administrator

The next server to be upgraded is the primary server in the database mirroring configuration (PRIMARY1). This step involves failing over the databases to PRIMARY2, which has been upgraded to SQL Server 2012. Once the failover happens, the database mirroring session is suspended because data cannot move down SQL Server versions, resulting in growth of the transaction logs on PRIMARY2 (risking disk space exhaustion) until PRIMARY1 is upgraded to the same SQL Server version. Furthermore, if an automatic failover to PRIMARY2 occurs due to a failure, you cannot fail over the databases back to PRIMARY1 (no high availability) until PRIMARY1 is upgraded to the same SQL Server version. Therefore, make sure that you are ready to complete this upgrade step immediately once you failover the databases.

  1. Perform a manual database mirroring failover of test1 and test2 from PRIMARY1 to PRIMARY2.
  2. Remove the WITNESS server from the database mirroring configuration.
  3. Log in to PRIMARY1 as CORP\Install.
  4. Run SQL Server Setup to upgrade the default SQL Server instance to SQL Server 2012.
  5. Resume the database mirroring session for the two databases, and then verify that the database mirroring session is synchronized.
  6. Return the WITNESS server into the database mirroring configuration for both databases
  7. Perform a manual database mirroring failover of test1 and test2 from PRIMARY2 to PRIMARY1.
  8. In Server Manager, add the Failover Clustering feature.
  9. Install KB 2494036.

At the end of these steps, you have the same HADR configuration that you started with, with the exception that all servers are running SQL Server 2012.

Step 5: Create a WSFC cluster

Domain Configuration

Role: Domain Administrator

You use CORP\Install to create and configure the WSFC cluster. In order for you to successfully create and run the cluster, this account needs to be configured to have the necessary permissions. Follow the steps below:

  1. On DC, open Active Directory Users and Computers.
  2. In the View menu, select Advanced Features.
  3. Right-click corp.contoso.com and click Properties.
  4. Click the Security tab. If you have not selected the Advanced Features option previously, this tab is not visible.
  5. Click Advanced.
  6. In the Advanced Security Settings for corp dialog, click Add, then type CORP\Install in the text box to add a security object for the account.
  7. Select the Allow check box for the Create Computer Objects permission.
  8. Click OK, then click OK again.

It is possible to set a more restricted permissions set for the WSFC cluster and clustered service. For more information, see Configure Windows Failover Clustering for SQL Server (Availability Group or FCI) with Limited Security.

Cluster Configuration

Role: Database Administrator

Before configuring the availability group, you must create a WSFC cluster that includes PRIMARY1, PRIMARY2, and REMOTE1. In this scenario, you use Node and File Share Majority for the WSFC quorum while removing the quorum vote from REMOTE1. This setup gives your WSFC quorum three votes at the primary site to ensure that the cluster is online in the event of a single-machine failure.

In a WSFC cluster, quorum health is paramount to the availability of the WSFC cluster and, consequently, of the availability group it hosts. Optimal quorum health not only determines whether an availability group has high availability, but also whether it is available at all. Make sure that you have full understanding of the WSFC quorum before attempting to configure an availability group on your production database servers. For more information, see Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery.

  1. Log in to any one of the following three servers as CORP\Install: PRIMARY1, PRIMARY2, or REMOTE1.

  2. Create a new failover cluster with the following PowerShell commands (run with administrative privileges).

    Import-Module FailoverClusters

    New-Cluster -Name SQLServer2012 -Node PRIMARY1,PRIMARY2,REMOTE1

  3. Create a share folder on WITNESS called \\WITNESS\WSFCQuorum and grant the SQLServer2012$ computer account Read/Write access to the folder. The SQLServer2012$ cluster account was created when you created the cluster in the previous step. It must be able to manipulate the file share witness before you can successfully configure the file share majority quorum.

  4. Set the quorum mode and test the cluster with the following PowerShell commands:

    Set-ClusterQuorum -NodeAndFileShareMajority \\WITNESS\WSFCQuorum

    Test-Cluster

  5. Remove the default quorum vote from REMOTE1 using the following PowerShell command:

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

    The quorum for the SQLServer2012 cluster should now look as follows:

    • PRIMARY1: 1 vote
    • PRIMARY2: 1 vote
    • File Share Witness: 1 vote
    • REMOTE1: 0 vote

    NOTE: The default quorum model, where each server has one vote without a file share witness, is technically an acceptable configuration. If intersite network fails, the primary site has node majority. Moreover, if any one of the three cluster nodes fails, the cluster still has node majority. However, the quorum model you configure here is a best-practice configuration that always gives the disaster recovery site zero votes while ensuring that the primary site has node majority in the event of a single-node failure. For more information, see Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery.

Step 6: Configure the Availability Group

Role: Database Administrator

Finally, you configure the availability group to complete your migration process.

While the New Availability Group Wizard gives you the option to prepare 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 very large databases, it is recommended that you manually prepare the secondary replicas in the same way you prepare mirror databases in a database mirroring configuration. This preparation involves taking a full backup and the most recent log backup of your databases and restore them with the WITH NO RECOVERY option on the intended secondary replica servers. However, PRIMARY2 already has a synchronized copy of the databases, and REMOTE1 is already restoring the shipped logs. The existing configuration reduces the amount of work required to fully configure the availability group.

Enable AlwaysOn Availability Groups Feature

You must first enable the AlwaysOn Availability Groups feature for each SQL Server instance.

  1. On REMOTE1, enable the AlwaysOn Availability Groups feature and restart the SQL Server instance.
  2. On PRIMARY2, enable the AlwaysOn Availability Groups feature and restart the SQL Server instance.
  3. On PRIMARY1, enable the AlwaysOn Availability Groups feature and restart the SQL Server instance. This action causes an automatic failover of the two databases to PRIMARY2.

The AlwaysOn Availability Group tab on each of your servers should look similar to the screenshot below.

At the end of these steps, PRIMARY2 is the principal database server in the database mirroring configuration and the primary server in the log shipping configuration.

Configure the Availability Group

Perform the following steps to configure the availability group:

  1. Remove the log shipping configuration on PRIMARY2 (and on REMOTE1, as a side effect) for both database test1 and test2.

  2. Perform a manual database mirroring failover of both databases back to PRIMARY1.

  3. Remove the log shipping configuration on PRIMARY1 for both databases. Make sure that the log tail is restored on REMOTE1.

  4. Remove the database mirroring sessions for both databases.

    The console application on CLIENT should continue to show Success:... for write access. The connection string currently used by the application continues to work because it points to PRIMARY1 in the Server parameter, which was the principal database server before you removed the database mirroring configuration.

  5. On REMOTE1, open the default port used by SQL Server for AlwaysOn Availability Groups, with the following command. Notice it is the same as the default database mirroring port. Remember that you already opened these ports on PRIMARY1 and PRIMARY2.

    netsh advfirewall firewall add rule name='SQL Server HADR (TCP-In)' dir=in action=allow protocol=TCP localport=5022 profile=domain

  6. While connected to PRIMARY1 in SSMS, right-click AlwaysOn High Availability and click New Availability Group Wizard.

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

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

  9. Select test1 and test2, then click Next.

  10. In the Replicas tab, click Add Replica.

  11. In the Server name box, type PRIMARY2, and then click Connect.

  12. In the Replicas tab, click Add Replica.

  13. In the Server name box, type REMOTE1, and then click Connect.

  14. Configure the three server instances as shown below.

    Server Instance

    Initial Role

    Automatic Failover

    Synchronous Commit

    Readable Secondary

    PRIMARY1

    Primary

    X

    X

    Yes

    PRIMARY2

    Secondary

    X

    X

    Yes

    REMOTE1

    Secondary

    		    

    Yes

  15. Accept default settings in the Endpoints tab. The ports used for the endpoints should be 5022, the same as the ones previously configured for database mirroring.

  16. In the Backup Preferences tab, keep the default Prefer Secondary option and configure the following priority settings for the replicas:

    • PRIMARY1: 50
    • PRIMARY2: 50
    • REMOTE1: 60

    By giving REMOTE1 higher priority, this configuration keeps the backup workload off the automatic failover set (PRIMARY1 and PRIMARY2).

    Note: This configuration alone does not actually cause any backup job to run. You still need to configure the desired backup jobs on each availability replica. For more information, see Configure Backup on Availability Replicas (SQL Server).

  17. 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/24: 10.1.1.111
    • IPv4 Address for 10.2.2/24: 10.2.2.222

  18. Click Next.

  19. Select Join only for data synchronization preference, and then click Next. You use the mirror databases and log shipping secondary databases from the previous configuration as secondary replicas, which all have recently restored log backups, so you do not need to synchronize data.

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

Note: The console application on CLIENT should continue to show Success:.... However, with the current availability group configuration, the Failover Partner parameter is only a dummy parameter. For conditions where the Failover Partner parameter can continue to work with an availability group, see Migration Guide: Migrating to AlwaysOn Availability Groups from Prior Deployments Combining Database Mirroring and Log Shipping Par I – Prescriptive Guidance (https://msdn.microsoft.com/en-us/library/jj635217). In this situation, you must reconfigure the client application to ensure that your application is highly available.

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. For an example, see the screenshot below.

You can also view the WSFC cluster information in the Failover Cluster Manager, as shown in the screenshot below.

The availability group (AG1) runs inside the WSFC cluster as a clustered service. PRIMARY1 and PRIMARY2, which are in the automatic failover set, are the preferred owners of AG1. The IP addresses for the availability group listener are in an OR configuration, where one IP address is online at any given time, depending on which subnet the primary replica resides. The WSFC service manages aspects the resource group ownership and monitors the quorum health for the availability group.

IMPORTANT: 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. SSMS is the user interface you should use for availability group management, such as performing failovers, adding and removing replicas and databases, and managing availability group listeners. For more information, see DO NOT use Windows Failover Cluster Manager to perform Availability Group Failover.

Reconfigure the Client Application

Role: Application Developer

Next, you reconfigure your client application to point to the new availability group listener. The availability group listener is not the only way to connect to the databases. You can also connect directly to the databases on any one of the server instances. For example, you can connect directly to test1 on PRIMARY2 for read-only workload using Server=PRIMARY2;Initial Catalog=test1. AlwaysOn Availability Groups also support new connection string keywords. For more information on client connectivity for AlwaysOn Availability Groups, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

  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=PRIMARY1;Failover Partner=PRIMARY2;Initial Catalog=test1;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=test1;IntegratedSecurity=SSPI";

  4. Run the application again by opening the Debug menu and clicking Start Debugging. The console application on CLIENT should now show Success:....

You're Done!

Congratulations! You have successfully migrated your HADR solution to AlwaysOn Availability Groups. Note that CORP\SQLClient still does not have access to the databases on the REMOTE1 instance. However, if you fail over the availability group to REMOTE1 in order to add the necessary permissions, you risk the possibility of data loss because REMOTE1 hosts an asynchronous replica. I would recommend that you simply incorporate this step into your disaster recovery plan.

Remember, the steps I present here may be much different from what you must implementyou're your particular migration scenario, but I hope it has given you a framework whereupon you can build the details. Whatever path you take to migrate your solution to SQL Server AlwaysOn, be sure to test your particular migration scenario extensively, including rehearsing failover scenarios and disaster recovery scenarios, before performing the migration on your production system.

Also, I have not shown you the new workloads that are possible with your new SQL Server AlwaysOn solution, such as using the read-only secondary replica for read workloads. For more information, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).