The current article series will focus on configuring the SQL Server Always-on failover cluster on AWS with the Amazon FSx Windows File Server.
AWS offers Elastic Block Storage (EBS) volumes that can be attached to the Amazon Elastic Compute Cloud (EC2) instances. These volumes are local to the EC2 instance they were attached to. As the Always-On availability group does not require shared storage, these volumes are a good fit for deploying Always-On availability groups.
On the other hand, SQL Server failover clusters need the shared storage accessible from all nodes participating in the failover cluster. Starting from SQL Server 2012, the Server Message Block (SMB) share can be used to install SQL Server and place system and user-defined databases in it.
Amazon FSx for Windows File Server is an SMB share that can be deployed to single or multiple availability zones. It is a storage option for deploying SQL Server Always-On Failover Clusters.
Note: There are a few limitations in installing SQL Server on an SMB share – please refer to the Install SQL Server with SMB file share storage article for more information.
Also, the SQL Server Always-On Failover Clusters can be configured using the EBS volumes with the help of third-party tools that replicate data between EBS volumes across availability zones.
Pre-requisites
To make use of the current article, you’ll need to ensure the following:
- AWS account with VPC and subnets configured.
- SQL Server installation media.
We will use the below configuration to deploy an SQL Server multi-subnet failover cluster:
- AWS Managed Microsoft Active Directory.
- Amazon FSx for Windows File Server for storage purposes.
- Amazon EC2 instances with Windows Server 2019 OS.
- SQL Server 2019.
Provisioning AWS Managed Microsoft Active Directory
We need a Microsoft Active Directory to join the nodes and FSx for Windows File Server to the domain. The active directory can be either the self-managed Microsoft Active Directory or the AWS-Managed Microsoft Active Directory. In this example, we are using the AWS-Managed Microsoft Active Directory. To provision it, follow the below steps:
Log into the AWS console, navigate to the directory service page, and click Set up directory:
Choose the AWS Managed Microsoft Active Directory and click Next. Select the edition that suits you best. In my example, I have chosen the Standard Edition.
Enter the domain name, the admin password, and click Next.
Select the Virtual Private Cloud (VPC) network and subnets which must be from two different availability zones. AWS Managed Microsoft Active Directory is deployed across the availability zones (different datacentres within the same region) to provide high availability of Active Directory. If you do not select the subnets, AWS will create the Active Directory in random availability zones available in the region.
Click Next, review the configuration and click Create directory. It takes a couple of minutes for the Active Directory to become available.
Alternatively, you can use AWS CLI to create an AWS Managed Microsoft Active Directory. Here, you need to set up AWS CLI and configure it with the user that has the necessary permissions to create resources (refer to Installing, updating, and uninstalling the AWS CLI version 1 for more information on AWS CLI).
Once you set up the AWS CLI, execute the following command – ensure to input your correct parameters into the string.
aws ds create-microsoft-ad --name sqldemo.com --password xxxxxxxxx --description "AWS DS Managed" --vpc-settings VpcId= vpc-xxxxxxxx,SubnetIds= subnet-xxxxxxxx, subnet-xxxxxxxxNow we have the AWS Managed Microsoft Active Directory to join the nodes which participate in the failover cluster and join the Amazon FSx for Windows File Server to the domain.
Provisioning FSx for Windows File Server
Amazon EC2 instances use the network bandwidth to access data in FSx for Windows File Server and not the EBS bandwidth.
These instances have a cap on EBS volume throughput. To achieve more throughput, we have to increase the instance size (refer to EBS optimization for throughput limits). With AWS FSx for Windows File Server, we can obtain higher throughput compared to EBS volumes without increasing instance size.
Before creating a file system, set up a security group and add the inbound rules to allow traffic from the Amazon EC2 instances. You can create security groups from the Amazon EC2 console.
Navigate to the FSx service page and click Create file system > Amazon FSx for Windows File Server > Next.
Enter the name of the file share and select the deployment type.
If you select the Multi-AZ option, it deploys the file share across the availability zones. It enables high availability and redundancy of the file share even though the primary availability zone is down.
Define the storage type, capacity, and throughput required for your SQL Server workloads. The throughput of the file share can be modified later, after provisioning the AWS FSx for Windows File Server.
In the network and security section, choose VPC, the security group that you created above, and the subnets from the availability zones that are the same as Amazon EC2 instances.
In the windows authentication section, select AWS Managed Microsoft Active Directory, then AWS Managed Microsoft Active Directory you created in the above steps. This will join the file system to the active directory selected earlier and create the required computer objects in it.
Figure 6. Joining Amazon FSx for Windows File Server to the Active DirectoryClick Next. In the Summary section, review all the configurations and click Create file system.
Once the file system is available, modify the permissions of the share and give the FULL CONTROL access to the SQL Server and SQL Server Agent service accounts that you are going to use.
Conclusion
We have learned how to provision the AWS Managed Microsoft Active Directory and the Amazon FSx for Windows File Server. Now we can install the SQL Server multi-subnet Always-On failover cluster instances. In the next part of this series, we will review provisioning Amazon EC2 instances and configuring the failover cluster.
Last modified: December 15, 2022
