The diagram above generally shows an active/source node (in green) synchronously replicating to a passive node, both existing in the same region (Region A). Synchronous replication can also be used for cross region support. However, asynchronous replication is recommended for mirrors spanning multiple regions. Through asynchronous replication the source node in Region A can replicate resources over to node 2 (prime) existing in another region (Region B).
This document describes the process of preparing your EC2 infrastructure to span two different regions for cross region support. For this walkthrough, we are going to use Windows Server 2019 and SQL Server 2019.
Note: For further clarification on terminology used throughout this document, please see the Amazon Web Services glossary currently located here.
Below is a GENERAL layout for enabling cross-region support:
Prepare the EC2 Infrastructure for WSFC
Description: Prepare the EC2 infrastructure to support business-critical applications clustered with Windows Server Failover Clustering (WSFC).
- Create the Virtual Private Cloud (VPC) and Subnets
- Provision Virtual Machines (Within the article we use Windows Server 2019 and SQL Server 2019)
- Provision DC1 (Domain Controller 1) in AZ1 (Availability Zone 1)
- Provision SQL1 (Designated SQL Server) in AZ2 (Availibilty Zone 2)
- Create Domain
- Join SQL1 and SQL2 to the Domain
- Prepare the Storage
- Edit the Advanced TCP/IP Setting
- Install Failover Clustering Feature (via Server Manager)
- Configure the Core Cluster
- Add the File Share Witness (Created on DC1)
Conclusion: You now have a basic two-node Windows Server failover cluster!
Configure SQL Server Cluster Instance
Description: Now that you’ve prepared your AWS virtual private cloud, instances and storage, it’s time to install and configure a SQL Server 2019 failover cluster instance.
- Configuring the Cluster Storage (via DataKeeper GUI)
- Install the First Node of SQL Server FCI on SQL1
- Install the Second Node of SQL Server FCI on SQL2
- Install SQL Server Management Studio
Note: Multi-Subnet considerations and other important information can also be found in the article as well.
Conclusion: You now have a configured a SQL Server 2019 failover cluster instance!
Prepare EC2 Infrastructure For Cross Region Support
- Create VPC (In a different region)
- In Part 1, we use (US East-Ohio)
- Create and Accept VPC Peering Connections
- Update Security Groups
- Modify VPC Peering Connection DNS Settings
- Deploy Windows AMI
- Create DHCP Option Set
- Edit TCP/IP Properties
Conclusion: You now have the AWS Infrastructure setup for AD (Active Directory) Replication between two regions!
Establish Cross Region Support
Step-by-Step: Configuring Amazon EC2 for Business Critical Application, Part 4
Description: This part will go over how to extend the existing SQL Server FCI to a third node Region 2 to protect your business-critical workload from the failure of the entire Region 1. If you are using SQL Server Enterprise, follow the steps below. If you are using SQL Server Standard Edition, Skip to “Adding a Node Outside the Cluster” below.
- Create new server/instance (SQL3) in Region 2
- Install DataKeeper and enable WSFC (Windows Server Failover Clustering)
- Add the newly created instance (Target) to the existing replication job (in Region 1)
- Replicate the volume(s) Asynchronously to SQL3 in Region 2
- Install SQL on SQL3 using the “Add Node to Existing Cluster” option
- Add additional IP addresses to the cluster to account for SQL3 which lives in a different subnet
- Check out Configuring Ip Addresses And Dependencies For Multi Subnet for more information
Conclusion: You now have the AWS Cross Region Support!
Adding a node outside the cluster
Description: If you are using SQL Server Standard Edition, rather than the Enterprise Edition, you will not be able to have more than two nodes in the cluster. In this scenario you can still replicate to a 3rd node, but it must reside outside the cluster.
- Create a single node SQL Server FCI cluster in Region B using the DataKeeper non-mirrored volume resource. Make sure you install it EXACTLY as the primary cluster is installed, with the exception of the host name, cluster names and IP addresses.
- Bring SQL Server offline on the cluster.
- Follow the guidance here to extend DataKeeper to that node in Site B. This process will overwrite the system databases that existed in the single node cluster.
- In the event of a disaster, follow the guidance here to make the 3rd node the source of the mirror.
- Bring the single node SQL Server FCI online in Site B. Depending upon your security configuration, you may need to add NTFS permissions to allow the database and log files to be mounted on the 3rd node. These permissions will persist and this step will not have to be taken in subsequent recoveries on the 3rd node.
- Manually redirect clients to the DR instance of SQL Server, or have DR applications pre-configured in Site B to point to the local SQL Server FCI.
- To failback to production, follow the procedures described here.
- Bring SQL Server online in production.