Field Notes: Implementing HA and DR for Microsoft SQL Server using Always On Failover Cluster Instance and SIOS DataKeeper
AWS Architecture BlogField Notes: Implementing HA and DR for Microsoft SQL Server using Always On Failover Cluster Instance and SIOS DataKeeperby Sudhir Amin | on11 AUG 2021| inAmazon EC2,Amazon VPC,Architecture|Permalink|ShareThis blog post was co-written by Sudhir Amin, Sr. Solution Architect, AWS, and David Bermingham, Senior Technical Evangelist, SIOS Technology, Inc.To ensure high availability (HA) of Microsoft SQL Server in Amazon Elastic Compute Cloud (Amazon EC2), there are two options: Always On Failover Cluster Instance (FCI) andAlways On availability groups. With a wide range of networking solutions such as VPN and AWS Direct Connect, you have options to further extend your HA architecture to another AWS Region to also meet your disaster recovery (DR) objectives.You can also run asynchronous replication between Regions, or a combination of both synchronous replication between Availability Zones and asynchronous replication between Regions.Choosing the right SQL Server HA solution depends on your requirements. If you have hundreds of databases, or are looking to avoid the expense of SQL Server Enterprise Edition, then SQL Server FCI is the preferred option. If you are looking to protect user-defined databases and system databases that hold agent jobs, usernames, passwords, and so forth, then SQL Server FCI is the preferred option. If you already own SQL Server Enterprise licenses we recommend continuing with that option.On-premises Always On FCI deployments typically require shared storage solutions such as a fiber channel storage area network (SAN). When deploying SQL Server FCI in AWS, a SAN is not a viable option. Although Amazon FSx for Microsoft Windows is the recommended storage option for SQL Server FCI in AWS, it does not support adding cluster nodes in different Regions. To build a SQL Server FCI that spans both Availability Zones and Regions, you can use a solution such asSIOS DataKeeper.In this blog post, we will show you how SIOS DataKeeper solves both HA and DR requirements for customers by enabling a SQL Server FCI that spans both Availability Zones and Regions. Synchronous replication will be used between Availability Zones for HA, while asynchronous replication will be used between Regions for DR.Architecture overviewWe will create a three-node Windows Server Failover Cluster (WSFC) with the configuration shown in Figure 1 between two Regions. Virtual private cloud (VPC) peering was used to enable routing between the different VPCs in each Region.Figure 1 – High-level architecture showing two cluster nodes replicating synchronously between Availability Zones.WalkthroughSIOS DataKeeper is a host-based, block-level volume replication solution that integrates with WSFC to enable what is known as a SANLess cluster. DataKeeper runs on each cluster node and has two primary functions: data replication and cluster integration through the DataKeeper volume cluster resource.The DataKeeper volume resource takes the place of the traditional Cluster Disk resource found in a SAN-based cluster. Upon failover, the DataKeeper volume resource controls the replication direction, ensuring the active cluster node becomes the source of the replication while all the remaining nodes become the target of the replication.After the SANLess cluster is configured, it is managed through Windows Server Failover Cluster Manager just like its SAN-based counterpart. Configuration of DataKeeper can be done through theDataKeeper UI,CLI, orPowerShell. AWS CloudFormation templates can also be used for automated deployment, as shown inAWS Quick Start.The following steps explain how to configure a three-node SQL Server SANless cluster with SIOS DataKeeper.PrerequisitesIAM permissions to create VPCs and VPC Peering connection between them.A VPC that spans two Availability Zones and includes two private subnets to host Primary and Secondary SQL Server.Another VPC with single Availability Zone and includes one private subnet to host Tertiary SQL Server Node for Disaster Recovery.Subscribe to the SIOS DataKeeper Cluster Edition AMI in the AWS Marketplace, or sign up for FTP download for SIOS Cluster Edition software.An existing deployment of Active Directory with networking access to support the Windows Failover Cluster and SQL Deployment. Active Directory can deployed on EC2 withAWS Launch Wizard for Active Directoryor through ourQuick Start for Active Directory.Active Directory Domain administrator account credentials.Configuring a three-node clusterStep 1: Configure the EC2 instancesIt’s good practice to record the system and network configuration details as shown below[SM1]. Indicate the hostname, volume information, and networking configuration of each cluster node. Each node requires a primary IP address and two secondary IP addresses, one for the core cluster resource and one for the SQL Server client listener.The example shown in Figure 2 uses a single volume; however, it is completely acceptable to use multiple volumes in your SQL Server FCI.Figure 2 – Shows Storage, Network and AWS Configuration details of all threes cluster nodesDue to Region and Availability Zone constructs, each cluster node will reside in a different subnet. A cluster that spans multiple subnets is referred to as amulti-subnet failover cluster. Refer toCreate a failover clusterandSQL Server Multi-Subnet Clusteringto learn more.Step 2: Join the domainWith properly configured networking and security groups, DNS name resolution, and Active Directory credentials with required permissions, join all the cluster nodes to the domain. You can use AWS Managed Microsoft AD or manage your own Microsoft Active Directory domain controllers.Step 3: Create the basic clusterInstall the Failover Clustering feature and its prerequisite software components on all three nodes using PowerShell, shown in the following example.Install-WindowsFeature -Name Failover-Clustering -IncludeManagementToolsRun cluster validation [CA2] [CA3] using PowerShell, shown in the following example.Test-Cluster -Node wsfcnode1.awslab.com, wsfcnode2.awslab.com, drnode.awslab.comNOTE: Windows PowerShell cmdletTest-Clustertests the underlying hardware and software, directly and individually, to obtain an accurate assessment of how well Failover Clustering can be supported in a given configuration with the following steps.Create the cluster using PowerShell, shown in the following example.New-Cluster -Name WSFCCluster1 -NoStorage -StaticAddress 10.0.0.101, 10.0.32.101, 11.0.0.101 -Node wsfcnode1.awslab.com, wsfcnode2.awslab.com, drnode.awslab.comFor more information using PowerShell for WSFC, view Microsoft’sdocumentation.It is always best practice to configure a file share witness and add it to your cluster quorum. You can use Amazon FSx for Windows to provide a Server Message Block (SMB) share, or you can create a file share on another domain joined server in your environment. See the following for moredocumentationfor more details.A successful implementation will show all three nodes UP (shown in Figure 3) in the Failover Cluster Manager.Figure 3 – Shows all three nodes status participating in Windows Failover ClusterStep 4: Configure SIOS DataKeeperAfter the basic cluster is created, you are ready to proceed with the DataKeeper configuration. Below are the basic steps to configure DataKeeper. For more detailed instructions, review theSIOS documentation.Step 4.1: Install DataKeeper on all three nodesAfter yousign up for a free demo, SIOS provides you with an FTP URL to download the software package of SIOS Datakeeper Cluster edition. Use the FTP URL to download and run the latest software release.ChooseNext, and read and accept the license terms.By Default, both componentsSIOS Datakeeper Server ComponentsandSIOS DataKeeper User Interfacewill be selected. ChooseNextto proceed.Accept the default install location, and chooseNext.Next, you will be prompted to accept the system configuration changes to add firewall exceptions for the required ports. ChooseYesto enable.Enter credentials for SIOS Service account, and chooseNext.Finally, chooseYes, and thenFinish, to restart your system.Step 4.2: Relocate the intent log (bitmap) file to the ephemeral driveRelocate the bitmap fileto the ephemeral drive (confirm the ephemeral drive uses Z:\ for its drive letter).SIOS DataKeeper uses an intent log (also referred to as a bitmap file) to track changes made to the source, or to target volume during times that the target is unlocked. SIOS recommends to use ephemeral storage as a preferred location for the intent log to minimize the performance impact associated with the intent log. By default, SIOS InstallShield Wizard will store the bitmap at: “C:\Program Files (x86) \SIOS\DataKeeper\Bitmaps”.To change the intent log location, make the following registry changes. Edit registry through regedit.“HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ExtMirr\Parameter”Modify the “BitmapBaseDir” parameter by changing to the new location (i.e.s, Z:\), and then reboot your system.To ensure the ephemeral drive is reattached upon reboot, specify volume settings in the DriveLetterMappingConfig.json, and save the file under the location.“C:\ProgramData\Amazon\EC2-Windows\Launch\Config\DriveLetterMappingConfig.json”.{ 
  "driveLetterMapping": [
  {
   "volumeName": "Temporary Storage 0",
   "driveLetter": "Z"
  },
  {
   "volumeName": "Data",
    "driveLetter": "D"
  }
 ]
}Next, open Windows PowerShell and use the following command to run the EC2Launch script that initializes the disks:C:\ProgramData\Amazon\EC2-Windows\Launch\Scripts\InitializeDisks.ps1 -ScheduleFor more information, seeEC2Launch documentation.NOTE: If you are using the SIOS DataKeeper Marketplace AMI, you can skip the previous steps to install SIOS Software and relocating bitmap file, and continue with the following steps.Step 4.3: Create a DataKeeper jobOpenSIOS DataKeeperapplication. From the right-side Actions panel, selectCreate Job.EnterJob nameandJob description, and then chooseCreate Job.A create mirror wizard will open. In this job, we will set up a mirror relationship betweenWSFCNode1(Primary)andWSFCNode2(Secondary)within us-east-1 using synchronous replication.Select the sourceWSFCNode1.awscloud.comand volumeD. ChooseNextto proceed.NOTE: The volume on both the source and target systems must be NTFS file system type, and the target volume must be greater than or equal to the size of the source volume.Choose the targetWSFCNODE2.awscloud.comand correctly-assigned volume driveD.Now, select how the source volume data should be sent to the target volume.For our design, choose Synchronous replication for nodes within us-east-1.After you chooseDone, you will be prompted to register the volume into Windows Failover Cluster as a clustered datakeeper volume as a storage resource. When prompted, chooseYes.After you have successfully created the first mirror you will see the status of the mirror job (as shown in Figure 4).If you have additional volumes, repeat the previous steps for each additional volume.Figure 4 – Shows the SIOS Datakeeper Job Summary of mirrored volume between WFSCNODE1 & WSFCNODE2If you have additional volumes, repeat the previous steps for each additional volume.Step 4.4: Add another mirror pair to the same job using asynchronous replication between nodes residing in different AWS Regions (us-east-1 and us-east-2)Open the create mirror wizard from the right-side action pane.Choose the sourceWSFCNode1.awscloud.comand volumeD.ForServer, enterDRNODE.awscloud.com.After you are connected, select the targetDRNODE.awscloud.comand assigned volumeD.For,How should the source volume data be sent to the target volume?, chooseAsynchronousreplication for nodes between us-east-1 and us-east-2.ChooseDoneto finish, and a pop-up window will provide you with additional information on action to take in the event of failover.You configured WSFCNode1.awscloud.com to mirror asynchronously to DRNODE.awscloud.com. However, during the event of failover, WSFCNode2.awscloud.com will become the new owner of the Failover Cluster and therefore own the source volume. This function of SIOS DataKeeper, calledswitchover, automatically changes the source of the mirror to the new owner of the Windows Failover Cluster. For our example, SIOS DataKeeper will automatically perform a switchover function to create a new mirror pair betweenWSFCNODE2.awscloud.comandDRNODE.awscloud.com. Therefore, selectAsynchronousmirror type, and chooseOK. For more information, seeSwitchover and Failover with Multiple Targets.A successfully-configured job will appear underJob Overview.If you prefer to script the process of creating the DataKeeper job, and adding the additional mirrors, you can use the DataKeeper CLI as described inHow to Create a DataKeeper Replicated Volume that has Multiple Targets via CLI.If you have done everything correctly, the DataKeeper UI will look similar to the following image.Furthermore, Failover Cluster Manager will show the Datakeeper Volume D, online, registered.Step 5: Install the SQL Server FCIInstall SQL Server on WSFCNode1 with theNew SQL Server failover cluster installationoption in the SQL Server installer.Install SQL Server on WSFCNode2 with theAdd node to a SQL Server failover cluster optionin the SQL Server installer.If you are using SQL Server Enterprise Edition, install SQL Server on DRNode using the “Add Node to Existing Cluster” option in the SQL Server installer.If you are using SQL Server Standard Edition, you will not be able to add the DR node to the cluster. Follow the SIOS documentation:Accessing Data on the Non-Clustered Disaster Recovery Node.For detailed information on installing a SQL Server FCI, visitSQL Server Failover Cluster Installation.At the end of installation, your Failover Cluster Manager will look similar to the following image.Step 6: Client redirection considerationsWhen you install SQL Server into a multi-subnet cluster,RegisterAllProvidersIPis set to true. With that setting enabled, your DNS Server will register three Name Records (A), each using the name that you used when you created the SQL Listener during the SQL Server FCI installation. Each Name (A) record will have a different IP address, one for each of the cluster IP addresses that are associated with that cluster name resource.If your clients are using .NET Framework 4.6.1 or later, the clients will manage the cross-subnet failover automatically. If your clients are using .NET Framework 4.5 through 4.6, then you will need to addMultiSubnetFailover=trueto your connection string.If you have an older client that does not allow you to add the MultiSubnetFailover=true parameter, then you will need to change the way that the failover cluster behaves. First, set the RegisterAllProvidersIP to false, so that only one Name (A) record will be entered in DNS. Each time the cluster fails over the name resource, the Name (A) record in DNS will be updated with the cluster IP address associated with the node coming online. Finally, adjust the TTL of the Name (A) record so that the clients do not have to wait the default 20 minutes before the TTL expires and the IP address is refreshed.In the following PowerShell, you can see how to change the RegisterAllProvidersIP setting and update the TTL to 30 seconds.Get-ClusterResource "sql name resource" | Set-ClusterParameter RegisterAllProvidersIP 0  
Set-ClusterParameter -Name HostRecordTTL 30CleanupWhen you are finished, you canterminateall three EC2 instances you launched.ConclusionDeploying a SQL Server FCI, that includes both Availability Zones and AWS Regions, is ideal for a solution that includes HA and DR. Although AWS provides the necessary infrastructure in terms of compute and networking, it is the combination of SIOS DataKeeper with WSFC that makes this configuration possible.The solution described in this blogpost works withall supported versionsof Windows Failover Cluster and SQL Server. Other configurations, such as hybrid-cloud and multi-cloud, are also possible. If adequate networking is in place, and Windows Server is running, where the cluster nodes reside is entirely up to you.Field Notes provides hands-on technical guidance from AWS Solutions Architects, consultants, and technical account managers, based on their experiences in the field solving real-world business problems for customers.David BerminghamDave Bermingham has worked for SIOS Technology since 2004 and has been a Microsoft MVP since 2010 with a current focus on high availability for SQL server and other applications running in AWS. He also maintains the blog Clustering for Mere Mortals where he writes many step-by-step guides and other resources in the area of his expertise. When not writing or speaking about high availability, you’ll often find him hanging out with his family at the Jersey shore or playing jazz guitar at local festivals and jazz clubs.TAGS:Field NotesSudhir AminSudhir Amin is a Sr. Solutions Architect at Amazon Web Services. In his role based out of New York, he provides architectural guidance and technical assistance to enterprise customers across different industry verticals, accelerating their cloud adoption. He is a big fan of snooker, combat sports such as boxing and UFC, and loves traveling to countries with rich wildlife reserves where he gets to see world's most majestic animals up close.
This blog post was co-written by Sudhir Amin, Sr. Solution Architect, AWS, and David Bermingham, Senior Technical Evangelist, SIOS Technology, Inc. To ensure high availability (HA) of Microsoft SQL Server in Amazon Elastic Compute Cloud (Amazon EC2), there are two options: Always On Failover Cluster Instance (FCI) and Always On availability groups. With a wide […]
Sudhir Amin
Amazon EC2 Amazon VPC Architecture