Setting Up SQL Server Always On availability groups (AG) on AWS EC2 Instances

• Author
• Recent Posts

Mohamed Wali

Mohamed is a cloud infrastructure expert with experience in engineering and implementation in AWS and Azure. He currently works as a Cloud Infrastructure Architect at Amazon Web Services.

Latest posts by Mohamed Wali (see all)

• EC2 Image Builder: Build your golden VM images on AWS - Wed, Jan 19 2022
• Configuring DFS Namespaces for Amazon FSx for Windows file servers - Fri, Jan 7 2022
• AWS Systems Manager Session Manager: Securely connect EC2 instances - Wed, Dec 22 2021

Before we get started, make sure you have the prerequisites listed below in place.

Prerequisites

• Deploy the EC2 instances to two different subnets in the same VPC but in different availability zones.
• The EC2 instance guest OS should be Windows Server 2012 R2 or later.
• The SQL Server version should be 2014 or later.
• Ensure that the security groups assigned to the EC2 instances allow traffic between each other and any third party that the instances might need to communicate with (e.g., file share witness) via the right ports.
• Ensure that the EC2 instances are domain-joined.
• For each EC2 instance elastic interface, ensure one secondary IP has been added that will be used for the cluster and extra IPs equal to the number of listeners you are planning to provision.
• For each EC2 instance NIC, change the DNS settings to append primary and connection-specific DNS suffixes.

Create a file share witness

A file share witness is simply an SMB share that acts as a tiebreaker where the failover cluster can be used to vote in the cluster quorum. It is not mandatory to use it, but it is the best practice.

There are a couple of ways to create a file share witness in AWS:

• Create an SMB share on a server reachable by the EC2 instance on which the Always On availability group will operate.
• Create an SMB share using Amazon FSx for Windows File Server

In this article, we will use the latter because it is a fully managed shared file storage service and provides less management overhead compared to the former, where you would have to make sure that the server is always available, updated, and maintained properly.

Create an SMB Share using Amazon FSx for Windows File Server

To create an SMB Share using Amazon FSx for Windows File Server, do the following:

1. On the AWS Console search bar, search for "FSx" and click it to navigate to the FSx dashboard.
2. From the dashboard, click Create file system.
   

   FSx dashboard

3. In the wizard that pops up, choose Amazon FSx for Windows File Server and click Next.
   

   FSx file system options

4. Provide the following info about your file system details:
   

   FSx file system details

   And your network & security settings.

   

   FSx network security

Ensure that the security group attached to the FSx share allows incoming traffic from the EC2 instances (on which the AG will be set up) via port 445.

It is a best practice to make sure that the Preferred subnet is the same as the primary node subnet in the failover cluster and the Standby subnet is the same as the secondary node subnet.

Windows authentication: Specify whether you use an AWS managed AD or a self-managed AD. In this article, we use the former, where we can specify from the drop-down list as in the following screenshot. If you want to use the latter, you have to make sure that your AD domain controller and DNS server are reachable from the FSx subnets and that the required permissions have been delegated to the service account. If your AD has multiple sites, then ensure that your FSx subnets are associated with one specific site.

Also, you will have to specify a KMS encryption key, which is used to encrypt the data at rest. I chose the default one in this case.

FSx Windows Authentication Encryption

The rest of the configuration settings are optional, but it is a best practice to configure them.

Access: You can list any custom DNS names that you want to associate with the file system.

Backup and Maintenance: You can choose to enable or disable backups for this share. If enabled, you can configure the frequency and the maintenance windows in which the backup can take place.

Tags: Any metadata you would like to pass about the resource you're creating.

5. Click Next.
6. On the summary page, verify your configuration.
7. Click Create file system.

Configure secondary IPs

Up next is to add some secondary IPs to the cluster nodes' elastic network interface. To do so, do the following:

1. Navigate to the EC2 instance console.
2. Select the EC2 instance to which you need to add secondary IPs.
3. Select Actions > Networking > Manage IP addresses.
4. On the IPv4 addresses screen, click Assign new IP address to add extra IPs. You can either enter the IP manually or let it be automatically assigned. Afterward, click Save.
   

   Configure secondary IPs

5. Repeat the same steps for the other cluster node.

Change the DNS settings

To change the DNS settings, you need to RDP to the instance and do the following:

1. From the Start menu, open Run or cmd.
2. Run the following command: ncpa.cplThis opens a new window displaying the NICs you have in this EC2 instance.
3. Open the properties of this NIC.
4. Click Advanced.
5. Switch to the DNS tab.
6. Select Append primary and connection specific DNS suffixes.
   

   Append primary and connection specific DNS suffixes

7. Save the change by clicking OK for all the open dialog boxes.
8. Repeat the same steps for the other cluster node.

Create and configure Windows Server failover cluster

On each cluster node, we need to install the Windows Server failover cluster feature.

You can do so by running the following PowerShell cmdlet:

Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools -Restart 

Once the feature is installed and the server is restarted, you can start testing whether the servers are ready to be clustered by running the following PowerShell cmdlet:

Test-Cluster -Node <Node1>, <Node2>

If the test went well with no errors, you can start creating the cluster by running the following PowerShell cmdlet:

New-Cluster -Name <Cluster Name>, -Node <Node1>, <Node2> -StaticAddress <The first secondary IP of Node1>, < The first secondary IP of Node2>  -NoStorage 

Note: Once the cluster is created, ensure that you grant the cluster computer object "Read all properties" and "Create computer objects" permissions over the OU on which the cluster computer object exists, if you're planning to create listeners. For a step-by-step guide, you can read this article.

Finally, you need to configure the cluster file share witness by running the following PowerShell cmdlet:

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

Notes:

• You can get the Windows Remote PowerShell Endpoint by navigating to the previously created FSx share under the Network & Security section.
• By default, the FSx share name is share.
• If you are using an SMB share on another server, you need to make sure that the computer objects of the cluster nodes and the cluster itself have write permissions on the shared folder.

Install SQL Server

Installing SQL Server is not a tricky job. In this step, all you need to do is just install SQL Server with the features/configuration you want in every cluster node.

Insure that you're using domain accounts in the setup for the service accounts and SQL admins.

Create an Always On availability group

Once you have SQL Server installed on the cluster nodes, you can get started with creating the Always On availability group.

Enable Always On availability group feature

To do this with PowerShell, you need to install the following package provider and module:

Install-PackageProvider -Name "Nuget" -MinimumVersion "2.8.5.201" -Force 
Install-Module -Name SqlServer -Force

Once installed, you can import the module by running the following cmdlet:

Import-Module SqlServer 

Then, you can run the following PowerShell cmdlet to enable the AG feature and restart the instance without being prompted for confirmation:

Enable-SqlAlwaysOn -Path "SQLSERVER:\Sql\Computer\<Instance_Name>" -Force 

Once done, repeat the same steps on the other cluster node.

Create and start the SQL AG endpoint

To create and start the SQL availability group endpoint, you need to run the following PowerShell cmdlets:

New-SqlHADREndpoint -Path "SQLSERVER:\SQL\$env:computername\<Instance_Name>" -Name <Endpoint_Name> -Port <Port_Number> -EncryptionAlgorithm Aes -Encryption Required
Set-SqlHADREndpoint -Path "SQLSERVER:\SQL\$env:computername\<Instance_Name>\Endpoints\<Endpoint_Name>" -State Started 

The service account needs to have connect permissions over the endpoint that you create. You can do this by running the following commands, which will create a SQL login for the service account and grant it connect permissions over the endpoint.

$Login = “CREATE LOGIN [<service_account>] FROM WINDOWS;” 
$ConnectPermissions = “GRANT CONNECT ON ENDPOINT::[<Endpoint_Name>] TO [<service_account>];”

Invoke-Sqlcmd -ServerInstance "$env:computername\<Instance_Name>" -Query $Login
Invoke-Sqlcmd -ServerInstance "$env:computername\<Instance_Name>)" -Query $ConnectPermissions 

Repeat the same steps on the other cluster node.

Create, back up, and recover a Test DB

To create a database, you need to run the following commands:

$CreateDB = "CREATE DATABASE TestDB"
Invoke-Sqlcmd -ServerInstance "$env:computername\<Instance_Name>" -Query $CreateDB 

To back up the database, you need a place in which to store the backup. You can create a shared folder anywhere locally and grant the service account full control over it by running the following commands:

New-Item -Path <Backup_Path> -ItemType Container
New-SmbShare -Path <Backup_Path> -Name <Backup_Share_Name>
Grant-SmbShareAccess -Name <Backup_Share_Name> -AccountName <service_account> -AccessRight Full -confirm:$false 

Then, you can run the backup by running the following commands:

$DBBackup = "BACKUP DATABASE TestDB TO DISK ='\\$env:computername\<Backup_Share_Name>\$DBName.bak'"
Invoke-Sqlcmd -ServerInstance "$env:computername\<Instance_Name>" -Query $DBBackup 

Finally, you can recover the DB to the second node by running the following commands:

$RecoverDB="RESTORE DATABASE TestDB FROM DISK = '\\$env:computername\<Backup_Share_Name>\$DBName.bak' WITH NORECOVERY"
Invoke-Sqlcmd -ServerInstance "<Second_Node_FQDN>\<Instance_Name>" -Query $RecoverDB 

Create the SQL Server Always On availability group

To create an AG, you need to run the following commands:

$SQL_AG = "CREATE AVAILABILITY GROUP [<group_name>]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DTC_SUPPORT = NONE)
FOR DATABASE [TESTDB]
REPLICA ON N'$env:computername\<Instance_Name>' WITH (ENDPOINT_URL = N'TCP://<Node1_FQDN>:<AG_Port>', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),N'<Node2_FQDN>\<Instance_Name>' WITH (ENDPOINT_URL = N'TCP://<Node2_FQDN>:<AG_Port>', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
    "
Invoke-Sqlcmd -ServerInstance "$env:computername\<Instance_Name>" -Query $SQL_AG
$JoinServers = "ALTER AVAILABILITY GROUP [<group_name>] JOIN;"
$DBSync = "ALTER DATABASE [TestDB] SET HADR AVAILABILITY GROUP = [<group_name>];"

Invoke-Sqlcmd -ServerInstance "<Node2_FQDN>\<Instance_Name>" -Query $JOINServers
Invoke-Sqlcmd -ServerInstance "<Node2_FQDN>\<Instance_Name>" -Query $DBSync 

Note: You can configure the availability group settings, such as availability mode, failover mode, seeding mode, etc., in a different way using T-SQL. Check the documentation for more information.

Create listeners

You can create a listener by running the following PowerShell cmdlet:

New-SqlAvailabilityGroupListener -Name <Listner_Name> -staticIP <Primary_Node_Secondary_IP>,<Secondary_Node_Secondary_IP> -Port <Instance_Port> -Path "SQLSERVER:\SQL\$env:computername\<Instance_Name>\AvailabilityGroups\AG_Name"

Conclusion

In this article, we've gone through the implementation of an Always On availability group on EC2 instances. If you have any further questions, please mention them in the comments.