The official SQL Server AlwaysOn team blog.
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:
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!
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.
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:
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.
The next server to be upgraded is the mirror server in the database mirroring configuration (PRIMARY2).
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.
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.
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:
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.
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.
New-Cluster -Name SQLServer2012 -Node PRIMARY1,PRIMARY2,REMOTE1
Set-ClusterQuorum -NodeAndFileShareMajority \\WITNESS\WSFCQuorum
(Get-ClusterNode "REMOTE1").NodeWeight = 0
The quorum for the SQLServer2012 cluster should now look as follows:
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.
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.
You must first enable the AlwaysOn Availability Groups feature for each SQL Server instance.
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.
Perform the following steps to configure the availability group:
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.
netsh advfirewall firewall add rule name='SQL Server HADR (TCP-In)' dir=in action=allow protocol=TCP localport=5022 profile=domain
Configure the three server instances as shown below.
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.
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).
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 (http://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.
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).
const string connString = @"Server=PRIMARY1;Failover Partner=PRIMARY2;Initial Catalog=test1;Integrated Security=True";
const string connString = @"Server=tcp:AG1,10000;Database=test1;IntegratedSecurity=SSPI";
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).