Set up Multi-AZ infrastructure for a SQL Server Always On FCI by using Amazon FSx

Sign in to your AWS account, and open the AWS Management Console.

Navigate to the CloudFormation console, and create the Systems Manager CloudFormation stack by uploading the ssm.yaml template. Provide values for the following parameters:

Deploy the Systems Manager CloudFormation  stack by launching the ssm.yaml CloudFormation template. The template will create the Systems Manager Atomation runboook that is initiated when a new EC2 instance with the tag ADJoined: FSXADD launches. The Automation runbook will add the instance to AWS Managed Microsoft AD directory.

Navigate to the CloudFormation console and launch the infra-cf.yaml template. To deploy this stack, the following parameters are required:

Deploy the infra stack. The stack will create all the infrastructure components that are required to set up Windows SQL Server FCI.

After the EC2 instance nodes are launched, the Systems Manager Automation document will be invoked to join these instances to AWS Managed Microsoft AD. You can track the progress on the Systems Manager console Automation page.

Log in to the primary EC2 instance, which is node 1. To install the Windows features (Active Directory and FCI Tools), run the following PowerShell script.

Install-WindowsFeature -Name RSAT-AD-Powershell,Failover-Clustering -IncludeManagementTools
Install-WindowsFeature -Name RSAT-Clustering,RSAT-ADDS-Tools,RSAT-AD-Powershell,RSAT-DHCP,RSAT-DNS-Server

Log in to the secondary EC2 instance, which is node 2, and run same script to enable features on node 2.

Log in to the primary EC2 instance, which is node 1. To create the Amazon FSx file share and grant full access to the listed AD service account, run the following code.

Invoke-Command -ComputerName "<FSx Windows Remote PowerShell Endpoint>" -ConfigurationName FSxRemoteAdmin -scriptblock {
New-FSxSmbShare -Name "SQLDB" -Path "D:\share" -Description "SQL Databases Share" -ContinuouslyAvailable $true -FolderEnumerationMode AccessBased -EncryptData $true
grant-fsxsmbshareaccess -name SQLDB -AccountName "<domain\user>" -accessRight Full
}

This command will also create the continuously available (CA) file share, which is optimized for use by Microsoft SQL Server.

To create the failover cluster on the primary instance (node 1), run the following command.

New-Cluster -Name <CNO Name> -Node  <Node1 Name>, <Node2 Name> -StaticAddress <Node1 Secondary Private IP>, <Node2 Secondary Private IP>

The command requires the following parameters:

Important: A domain administrator or regular user must have administrator permission on both the nodes to create the Windows Server Failover Clustering (WSFC) cluster. Otherwise, the previous command will fail and return the message, You do not have administrator privilege on servers.

After the cluster is created, run the following command to attach the file share witness.

Set-ClusterQuorum -FileShareWitness \\<FSx Windows Remote PowerShell Endpoint>\share\witness

In the T drive on both nodes, create tempdb and log folders. The folders are used in the PowerShell commands.

After you copy the SQL Server media files for SQL Server installation on both nodes, run the following PowerShell command on node 1 to install SQL Server on node 1.

On the Windows instance for one of the nodes, in Administrative Tools, launch the Failover Cluster Manager.

Navigate to Nodes, and confirm that the node status is Status Running.

Select Roles, open the context (right-click) menu for SQL Server (MSSQLSERVER), and select Move and Select Node.

After the node selection, SQL Server should be running on the other node.

Open the AWS CloudFormation console.

On the Stacks page, select the infra stack. The stack must be currently running.

In the stack details pane, choose Delete.

Select Delete stack when prompted.

Repeat steps 2-4 for the ssm stack.