Set up a Microsoft SQL Server failover cluster on Amazon EC2 using FSx for Windows File Server

Sweta Krishna and Ramesh Babu Donti, Amazon Web Services

Summary

Microsoft SQL Server Standard edition with a failover cluster instance (FCI) can provide a more cost-effective alternative to SQL Server Enterprise. Setting up SQL FCI requires shared file storage between nodes, and Amazon FSx for Windows File Server provides fully managed storage that automatically replicates synchronously across Availability Zones. Amazon FSx reduces storage costs by using built-in data deduplication for general-purpose file shares, which eliminates the need to maintain third-party solutions. Amazon FSx also supports the following:

• Pay only for what you use with no upfront fees or commitments.

• Set up FCI manually with FSx as your shared storage.

• Use FSx as the file share witness for your SQL cluster.

• Amazon FSx for Windows File Server supports Server Message Block (SMB) 3.0 for continuously available file shares, making it suitable for SQL Server FCI deployments.

Prerequisites and limitations

Prerequisites

• Active AWS account.

• Permissions to create and manage Amazon Virtual Private Cloud (Amazon VPC) resources, Amazon Elastic Compute Cloud (Amazon EC2) instance, security groups, and AWS Identity and Access Management (IAM) roles.

• AWS Managed Microsoft AD or your own on-premises Active Directory.

• An Active Directory domain user with necessary permission to set up a failover cluster.

• Security group rules for SQL Server FCI and Microsoft Active Directory ports for secure hybrid connectivity.

• A service account in Active Directory for SQL Server that’s configured with appropriate permissions across SQL nodes.

• Amazon FSx for Windows File Server in a failover cluster.

• SQL Server installation binaries.

Limitations

• Some AWS services aren’t available in all AWS Regions. For Region availability, see AWS services by Region. For specific endpoints, see the Service endpoints and quotas page, and choose the link for the service.

Product versions

• Amazon EC2 for Windows Server 2012 R2 or later

• Amazon FSx for Windows File Server with all current Windows Server versions

• Amazon FSx for NetApp ONTAP as an alternative for shared storage

• SQL Server 2012/2016/2019/2022

Architecture

Technology stack

• Amazon EC2

• Amazon FSx for Windows File Server

• Amazon VPC

• AWS Directory Service

• AWS Systems Manager

• IAM

Target architecture

The following diagram shows the high-level architecture of Microsoft SQL Server FCI on Amazon EC2 using Amazon FSx for Windows File Server.

Network infrastructure

• Amazon VPC provides a network container that spans three Availability Zones.

• Private subnets provide isolated subnets in each Availability Zones for deploying resources.

Compute layer

• Amazon EC2 contains an SQL Server cluster node 1, deployed in Availability Zone 1 as part of the Windows Server Failover Cluster (WSFC).

• Amazon EC2 contains an SQL Server cluster node 2, deployed in Availability Zone 2 as part of the WSFC.

• The WSFC cluster connects both SQL Server nodes for failover capability.

Storage layer for Amazon FSx for Windows File Server

Multi-AZ FSx deployment (spanning Availability Zones 1 and 2)

• A primary FSx file system in Availability Zone 1 hosts active SQL Server data and log files.

• A secondary FSx file system in Availability Zone 2 provides automatic failover capability.

• A shared SMB file share (\\fsx.domain\sqlshare), accessible by both cluster nodes for SQL Server databases.

Single-AZ FSx deployment (in AZ3)

• Amazon FSx file server witness in Availability Zone 3 serves as the cluster quorum witness.

• The file share witness (\\fsx.domain\witness) maintains the cluster quorum and prevents split-brain scenarios.

Directory services

• AWS Managed Microsoft AD provides Windows authentication and domain services that are required for cluster functionality.

High availability features

• Multi-AZ components provide fault tolerance across Availability Zones.

• FSx standby file server provides automatic failover if the primary server fails.

• File share witness provides cluster quorum management in Availability Zone 3 to help ensure proper cluster operation during failures.

• Domain is integrated with AWS Managed Microsoft AD for seamless Windows authentication.

Tools

AWS services

• Amazon Elastic Compute Cloud (Amazon EC2) provides scalable computing capacity in the AWS Cloud. You can launch as many virtual servers as you need and quickly scale them up or down.

• Amazon FSx provides file systems that support industry-standard connectivity protocols and offer high availability and replication across AWS Regions.

• Amazon Virtual Private Cloud (Amazon VPC) helps you launch AWS resources into a virtual network that you’ve defined. This virtual network resembles a traditional network that you’d operate in your own data center, with the benefits of using the scalable infrastructure of AWS.

• AWS Directory Service for Microsoft Active Directory enables your directory-aware workloads and AWS resources to use Microsoft Active Directory in the AWS Cloud.

• AWS Identity and Access Management (IAM) helps you securely manage access to your AWS resources by controlling who is authenticated and authorized to use them.

• AWS Systems Manager helps you manage your applications and infrastructure running in the AWS Cloud. It simplifies application and resource management, shortens the time to detect and resolve operational problems, and helps you manage your AWS resources securely at scale.

Best practices

• Place database instances in private subnets to protect them from being publicly accessible from the internet while still allowing them to connect to AWS services and perform updates.

• For general best practices, see Best Practices for Deploying Microsoft SQL Server on Amazon EC2.

• To use PowerShell to administer your Amazon FSx for Windows File Server, see Administering FSx for Windows file systems.

Epics

Create and configure Amazon EC2 nodes for SQL Server

Task	Description	Skills required
Add names and tags.	In the AWS Management Console, navigate to Amazon EC2. Choose Launch instance. Add the required tags.	DBA
Choose a Windows AMI.	Choose an Amazon Machine Image (AMI) for Windows that meets your SQL Server requirements.	DBA
Select an instance type.	Select an Amazon EC2 instance type that meets your requirements.	DBA
Use a key pair.	You can use a key pair to securely connect to your instance. Ensure that you have access to the selected key pair before you launch the instance.	DBA
Configure network settings.	Choose the VPC that you used to configure Active Directory. Choose the VPC and private subnet. Choose an existing security group. If you don’t have one, choose Create security group.	DBA
Configure advanced network settings.	Choose Advanced network settings. Select the secondary IP. Select the Automatically assign checkbox to assign the secondary IP from the subnet.	DBA
Configure storage.	Configure the required total storage and choose the required storage type.	DBA
Configure advanced details and launch the instance.	Choose the Active Directory domain from the directory list. Choose an IAM role that has the AmazonSSMManagedInstanceCore and AmazonSSMDirectoryServiceAccess policies attached. (Optional) Configure other available options. Launch the instance.	DBA
Create node 2.	Repeat these steps to create and configure node 2.	DBA

Install and configure Windows Server failover cluster on nodes 1 and 2

Task	Description	Skills required
Log in to node 1.	Log in to the Windows Amazon EC2 instance as an administrator.	DBA
Install FCI features on node 1.	In PowerShell, run the following script: Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools After installation, check for and install any outstanding Windows updates. Restart the instance.	DBA
Log in to node 2.	Log in to the Windows Amazon EC2 instance as an administrator.	DBA
Install FCI features on node 2.	In PowerShell, run the following script: Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools After installation, check for and install any outstanding Windows updates. Restart the instance.	DBA
Add nodes to the cluster.	Use Remote Desktop to connect to node 1 and choose Start server manager. In the Tools menu, choose Failover Cluster Manager. On the Failover cluster manager pane under Management, choose Create cluster to open the Create cluster wizard. On the Before you begin page, choose Next. If the Select servers page appears, in the Enter name field, choose Browse and search for your servers, and then choose Add. Repeat this step for each server that you want to add and then choose Next. On the Access point for administering the cluster page, choose the cluster and then choose Next. The Confirmation page shows the cluster name, node, and other domain information. Review these details and then choose Next. On the Summary page, verify that the FCI was successfully created. If there are any warnings or errors, view the summary output or choose View report. Choose Finish. When the cluster is configured, the system displays You have completed the create cluster wizard. Repeat these steps for node 2.	DBA
Bring the cluster online.	To bring the cluster online, update the static IP addresses of both nodes: Select the name of the cluster object. Open the context (right-click) menu and choose Properties. Select the subnet and an unused secondary IP address from your EC2 instance. After updating the secondary IP address, open the context (right-click) menu and select Cluster object name. Choose Bring online.	DBA
Validate the cluster.	Navigate to Failover cluster manager and verify that the cluster core resources are online.	DBA

Install SQL Server on nodes 1 and 2

Task	Description	Skills required
Log in to the server.	Log in to the Amazon EC2 instance as an administrator.	DBA
Mount the SQL binaries.	Start SQL Server installation center and then choose Installation. Choose New SQL Server failover cluster installation. Follow the prompts up to Setup files. In the Global rules dialog box, verify that all checks succeeded. In the Install failover cluster rules dialog box, verify that all checks succeeded. Address any warnings before proceeding. In the Feature selection dialog box, select the checkboxes for Database engine services and Client tools connectivity. In the Feature rules dialog box, verify that all rules passed. In the Instance configuration dialog box, enter the SQL Server network name. In the Cluster resource group, specify the name for the resource group. Either select a group from the dropdown list or enter a custom name. In the Cluster network configuration dialog box, select IPv4 and clear DHCP. Provide a secondary private IP address for each node. In the Server configuration dialog box: On the Service accounts tab, provide the required details for the SQL Service account name and credentials for the SQL Server. Verify that the Startup type for the agent and database engine is set to Manual. Select the checkbox for Grant perform volume maintenance task privilege to SQL Server database engine service. This enables instant file initialization for SQL Server. In the Database engine configuration dialog box: On the Server configuration tab under Windows authentication mode, verify that Authentication mode is selected. On the Data directories tab, specify the location of the Amazon FSx path for data, logs, tempdb, and backup files (for example, \\fsx.domain\sqlshare\data for .mdf files and \\fsx.domain\sqlshare\logs for .ldf files). In the Feature configuration rules dialog box, verify that all checks succeeded and then choose Next. In the Ready to install dialog box, verify all configuration settings. Choose Install to proceed with the installation. In the Complete dialog box, choose Close.
Add node 2 to the failover cluster.	Navigate to SQL Server installation center. Choose Installation. Choose Add node to a SQL Server failover cluster and follow the prompts up to Cluster node configuration. In the Cluster node configuration dialog box, check IPv4 and clear DHCP. Add an IP address for node 2. Under Service accounts, verify that the details match for nodes 1 and 2. Provide credentials for the corresponding SQL Server service accounts. In the Feature rules dialog box, verify that all checks succeeded. Choose Next and follow the remaining prompts. Choose Install to proceed with the installation. In the Complete dialog box, choose Close.	DBA

Configure the Amazon FSx file share witness

Task	Description	Skills required
Configure quorum settings.	Use RDP to connect to your Amazon EC2 instance. Navigate to Failover cluster manager. Open the context (right-click) menu on the cluster and choose More actions. Choose Configure cluster quorum settings. Configure the quorum settings.	DBA
Retrieve DNS details.	In the Amazon FSx console, choose Managed AD and then Attach. The DNS should have the following format: \\example.example.net\share	DBA
Configure the file share witness.	Choose Amazon FSx file share path and then Finish.	DBA

Related resources

AWS resources

• Amazon FSx for Windows File Server (video)

• Deep dive on Amazon FSx for Windows File Server (video)

• How to deploy a SQL Server failover cluster with Amazon EBS Multi-Attach on Windows Server (AWS blog post)

• Simplify your Microsoft SQL Server high availability deployments using Amazon FSx for Windows File Server (AWS blog post)

• SQL Server high availability deployments using Amazon FSx for NetApp ONTAP (AWS blog post)

• Using FSx for Windows File Server with Microsoft SQL Server

• What is FSx for Windows File Server?

Other resources

• Create a failover cluster

Additional information

Configuring the file share witness

Ensure that you’re connected to the file system from both nodes by adding rules in the Amazon FSx security group that allow inbound connections. The SMB port should be allowed. For example, if the DNS name is \\example.example.com\share, use \\example.example.com\share. Use the same value for the file share witness in the Always On availability cluster. Complete the following steps to configure the file share witness:

1. Use RDP to connect to your Amazon EC2 instance.

2. Navigate to Failover cluster manager.

3. Open the context (right-click) menu and choose More actions.

4. Choose Configure cluster quorum settings.

5. Choose Next.

6. Select Quorum configuration and configure a file share witness.

7. Provide the DNS name.

8. Review the summary and then choose Finish. The file share witness should be online in the Cluster core resources section.