SQL Server with Windows Server Failover Clustering on the AWS Cloud

Quick Start Reference Deployment

QS

October 2020
Aaron Lima and Dave May, Amazon Web Services

Visit our GitHub repository for source files and to post feedback, report bugs, or submit feature ideas for this Quick Start.

This Quick Start was created by Amazon Web Services (AWS). Quick Starts are automated reference deployments that use AWS CloudFormation templates to deploy key technologies on AWS, following AWS best practices.

Overview

This Quick Start deployment guide provides step-by-step instructions for deploying SQL Server on the AWS Cloud, using AWS CloudFormation templates and AWS Systems Manager Automation documents that automate the deployment.

This Quick Start is for IT infrastructure architects, administrators, and DevOps professionals who are planning to implement or extend their Windows Server Failover Clustering (WSFC) and SQL Server workloads on AWS.

SQL Server with Windows Server Failover Clustering on AWS

This Quick Start implements a highly available environment that includes Windows Server and SQL Server running on Amazon Elastic Compute Cloud (Amazon EC2), using the Always On availability groups feature of SQL Server Enterprise edition. This infrastructure provides the underpinnings for many Microsoft technology-based solutions for the enterprise, including SharePoint and .NET Framework applications.

The automation in this deployment uses AWS Systems Manager Automation, AWS CloudFormation, and Windows PowerShell Desired State Configuration (DSC) to deploy a multi-node SQL Always On availability group. A prerequisite for deploying a SQL Server Always On availability group is Windows Server Failover Clustering (WSFC). SQL Server Always On uses WSFC to increase application availability. WSFC provides infrastructure features that complement the high availability and disaster recovery scenarios supported in the AWS Cloud.

Implementing WSFC in the AWS Cloud is very similar to deploying it in an on-premises setting as long as you meet two key recommendations:

  • You must deploy the cluster nodes inside a virtual private cloud (VPC).

  • We recommend that you deploy the cluster nodes in separate subnets to provide high availability across multiple Availability Zones.

This Quick Start provides an automated deployment of WSFC that meets these requirements and handles the configuration steps for you.

This guide provides step-by-step instructions for deploying a reference architecture for SQL Server on AWS. It doesn’t provide general configuration and usage information for WSFC and SQL Server. For general guidance and best practices, consult the Microsoft product documentation and the Best Practices for Deploying Microsoft SQL Server on AWS whitepaper.

This Quick Start supports SQL Server 2016, SQL Server 2017, and SQL Server 2019 Enterprise edition running on Windows Server 2019.

Cost

You are responsible for the cost of the AWS services used while running this Quick Start. There is no additional cost for using the Quick Start.

The AWS CloudFormation templates for Quick Starts include configuration parameters that you can customize. Some of the settings, such as the instance type, affect the cost of deployment. For cost estimates, see the pricing pages for each AWS service you use. Prices are subject to change.

After you deploy the Quick Start, create AWS Cost and Usage Reports to deliver billing metrics to an Amazon Simple Storage Service (Amazon S3) bucket in your account. These reports provide cost estimates based on usage throughout each month and aggregate the data at the end of the month. For more information, see What are AWS Cost and Usage Reports?

Software licenses

The AWS CloudFormation template for this Quick Start includes configuration parameters that you can customize. Some of these settings, such as instance type, volume size, or opting to use the Amazon-provided image for SQL Server, will affect the cost of deployment. See the pricing pages for each AWS service you will be using for cost estimates.

Tip After you deploy the Quick Start, we recommend that you enable the AWS Cost and Usage Report to track costs associated with the Quick Start. This report delivers billing metrics to an Amazon Simple Storage Service (Amazon S3) bucket in your account. It provides cost estimates based on usage throughout each month and finalizes the data at the end of the month. For more information about the report, see the AWS documentation.

The Quick Start provides three licensing options for SQL Server Enterprise edition. You can:

  • Install a trial version of SQL Server, SQL Server 2017, or SQL Server 2019 Enterprise edition from the Microsoft Evaluation Center.

  • Use the Amazon Machine Image (AMI) for Windows Server with license costs and SQL Server Enterprise edition included.

  • Use your volume licensing software and mobilize the license. For development and test environments, you can leverage your existing MSDN licenses for SQL Server by using Amazon EC2 Dedicated Instances. For more information about these options, see the Microsoft Licensing on AWS webpage.

This Quick Start launches one of the Windows Server AMIs listed in the following table, depending on the parameters you set when you deploy the Quick Start.

Quick Start parameter settings Windows Server AMI used SQL Server version
(SQLServerVersion)

Amazon-provided SQL Server license (SQLLicenseProvided)

2016

Yes

Windows Server 2019 with SQL Server 2016 SP2

2017

Yes

Windows Server 2019 with SQL Server 2017

2019

Yes

Windows Server 2019 with SQL Server 2019

2016, 2017, or 2019

The AMI includes the license for Windows Server and is updated on a regular basis with the latest service pack for the operating system.

Architecture

Deploying this Quick Start for a new virtual private cloud (VPC) with default parameters builds the following SQL Server environment in the AWS Cloud.

image
Figure 1. WSFC and SQL Server architecture on AWS (default configuration)

As shown in Figure 1, the Quick Start sets up the following:

  • A VPC configured with public and private subnets across two Availability Zones. This provides the network infrastructure for your SQL Server deployment. You can optionally choose a third Availability Zone for the file share witness or for an additional SQL Server cluster node, as shown in Figure 2.*

image
Figure 2. WSFC and SQL Server architecture on AWS with three Availability Zones
  • In the public subnets, Windows Server-based Remote Desktop Gateway (RD Gateway) instances and network address translation (NAT) gateways for outbound internet access.*

  • In the private subnets, Active Directory Domain Services (AD DS) domain controllers, which are configured by AWS Systems Manager Automation documents.*

    The Quick Start uses AWS Directory Service to provision AD DS and to manage tasks such as monitoring domain controllers, and configuring backups and snapshots. You can also choose to add AD DS domain controllers as EC2 instances to the architecture and manage these yourself, as shown in Figures 3 and 4.

image
Figure 3. WSFC and SQL Server architecture with AD DS on Amazon EC2
image
Figure 4. WSFC and SQL Server architecture with three Availability Zones and AD DS on Amazon EC2
  • In the private subnets, Windows Server-based instances as WSFC nodes, and SQL Server Enterprise edition with SQL Server Always On availability groups on each node. This architecture provides redundant databases along with a witness server to ensure that a quorum can vote for the node to be promoted to primary; the witness server can optionally be replaced with an Amazon FSx Windows file share. The default architecture mirrors an on-premises architecture of two SQL Server instances spanning two subnets placed in two different Availability Zones.

  • AWS Secrets Manager for password storage.

  • Security groups to ensure the secure flow of traffic between the instances deployed in the VPC.

*The template that deploys the Quick Start into an existing VPC skips the components marked by asterisks and prompts you for your existing VPC configuration.

Planning the deployment

Specialized knowledge

This deployment requires a moderate level of familiarity with AWS services. If you’re new to AWS, visit Getting Started with AWS and Training and Certification. These sites provide materials for learning how to design, deploy, and operate your infrastructure and applications on the AWS Cloud.

This deployment guide also requires a moderate level of familiarity with AWS services. If you’re new to AWS, visit the Getting Started Resource Center and the AWS Training and Certification website for materials and programs that can help you develop the skills to design, deploy, and operate your infrastructure and applications on the AWS Cloud. For more information about the AWS services that are used in this Quick Start, see the Additional resources section.

AWS account

If you don’t already have an AWS account, create one at https://aws.amazon.com by following the on-screen instructions. Part of the sign-up process involves receiving a phone call and entering a PIN using the phone keypad.

Your AWS account is automatically signed up for all AWS services. You are charged only for the services you use.

Technical requirements

Before you launch the Quick Start, your account must be configured as specified in the following table. Otherwise, deployment might fail.

Resource quotas

If necessary, request service quota increases for the following resources. You might request quota increases to avoid exceeding the default limits for any resources that are shared across multiple deployments. The Service Quotas console displays your usage and quotas for some aspects of some services. For more information, see What is Service Quotas? and AWS service quotas.

Resource This deployment uses

VPCs

1

Elastic IP addresses

3

AWS Identity and Access Management (IAM) security groups

6

IAM roles

5

Auto Scaling groups

1

T2 or T3 instances

2

R4 or R5 instances

2

M4 or M5 instances

2

Supported Regions

This deployment includes AWS Secrets Manager, which isn’t currently supported in all AWS Regions. For a current list of supported regions, see Service Endpoints and Quotas in the AWS documentation. Also, if you’re planning to deploy an architecture with three Availability Zones, choose an AWS Region that supports three zones.

If using non-default settings, including dedicated hosts for the SQL servers and an FSx file share for the file share witness, see the AWS documentation to ensure regional compatibility.

Certain Regions are available on an opt-in basis. See Managing AWS Regions.

EC2 key pairs

Make sure that at least one Amazon EC2 key pair exists in your AWS account in the Region where you plan to deploy the Quick Start. Make note of the key pair name. You need it during deployment. To create a key pair, see Amazon EC2 key pairs and Linux instances.

For testing or proof-of-concept purposes, we recommend creating a new key pair instead of using one that’s already being used by a production instance.

IAM permissions

Before launching the Quick Start, you must sign in to the AWS Management Console with IAM permissions for the resources that the templates deploy. The AdministratorAccess managed policy within IAM provides sufficient permissions, although your organization may choose to use a custom policy with more restrictions. For more information, see AWS managed policies for job functions.

The architecture built by this Quick Start supports AWS best practices for high availability and security.

High Availability and disaster recovery

Amazon EC2 provides the ability to place instances in multiple locations composed of AWS Regions and Availability Zones. Regions are dispersed and located in separate geographic areas. Availability Zones are distinct locations within a Region that are engineered to be isolated from failures in other Availability Zones and that provide inexpensive, low-latency network connectivity to other Availability Zones in the same Region.

By launching your instances in separate Regions, you can design your application to be closer to specific customers or to meet legal or other requirements. By launching your instances in separate Availability Zones, you can protect your applications from the failure of a single location. WSFC provides infrastructure features that complement the high availability and disaster recovery scenarios supported in the AWS Cloud.

Automatic failover

Deploying the Quick Start with the default parameters configures a two-node automatic failover cluster with a file share witness. On this cluster, it deploys a SQL Server Always On availability group with two availability replicas.

image
Figure 5. SQL Server Always On availability groups and automatic failover

The Quick Start implementation supports the following scenarios:

  • Protection from the failure of a single instance

  • Automatic failover between cluster nodes

  • Automatic failover between Availability Zones

However, the Quick Start default implementation doesn’t provide automatic failover in every case. For example, the loss of Availability Zone 1, which contains the primary node and file share witness, would prevent automatic failover to Availability Zone 2. This is because the cluster would fail as it loses quorum. In this scenario, you could follow manual disaster recovery steps that include restarting the cluster service and forcing quorum on the second cluster node (e.g., WSFCNode2) to restore application availability. The Quick Start also provides an option to deploy into three Availability Zones. This deployment option can mitigate this loss of quorum in the case of a failure of a single node. However, you can select this option only in AWS Regions that include three or more Availability Zones; for a current list, see the AWS Global Infrastructure webpage.

We recommend that you consult the Microsoft SQL Server documentation and customize some of the steps described in this guide or follow additional steps (e.g., deploy additional cluster nodes and configure them as readable secondary replicas) to deploy a solution that best meets your business, IT, and security requirements.

Security groups and firewalls

When the EC2 instances are launched, they must be associated with a security group, which acts as a stateful firewall. You have complete control over the network traffic entering or leaving the security group, and you can build granular rules that are scoped by protocol, port number, and source or destination IP address or subnet. By default, all traffic egressing a security group is permitted. Ingress traffic, on the other hand, must be configured to allow the appropriate traffic to reach your instances.

The Securing the Microsoft Platform on Amazon Web Services whitepaper discusses the different methods for securing your AWS infrastructure. Recommendations include providing isolation between application tiers by using security groups. We recommend that you tightly control ingress traffic in order to reduce the attack surface of your EC2 instances.

Domain controllers and member servers require several security group rules to allow traffic for services such as AD DS replication, user authentication, Windows Time services, and Distributed File System (DFS), among others. The WSFC nodes running SQL Server will need to permit several additional ports to communicate with each other as well. Finally, instances launched into the application server tier will need to establish SQL client connections to the WSFC nodes.

The Quick Start creates a number of security groups and rules for you. For a detailed list of port mappings, see the Security section of the Active Directory deployment guide.

In addition to security groups, the Windows firewall also needs to be modified on the SQL Server instances. During the bootstrapping process, a script will run on each instance that opens the TCP ports 1433, 1434, 4022, 5022, 5023, and 135 on the Windows firewall.

SQL Server Enterprise edition

Amazon Machine Images (AMIs) for the SQL Server 2016 and 2017 Enterprise edition are available for launch on AWS, with the limitations discussed in the Costs and licenses section. If you set the Amazon-provided SQL Server license (SQLLicenseProvided) parameter to no, this Quick Start automatically connects to the Microsoft download site and installs the trial software for SQL Server Enterprise edition. If you set the parameter to yes (which is the default setting), the Quick Start uses the Amazon-provided AMI, which includes a license for SQL Server Enterprise edition.

You’ll find the installation software on each node in the C:\sqlinstall\ folder and the SQL media in the C:\SQLMedia\ folder.

The SQL services are configured to run under the sqlsa account that is created in Active Directory. This account is also added to the local administrator groups on each WSFC node.

AWS does not provide installation media for Microsoft software. If you are not using the AWS CloudFormation templates, you can set up a test or evaluation environment by downloading a trial version of SQL Server from the Microsoft Evaluation Center.

Storage on the WSFC nodes

Storage capacity and performance are key aspects of any production SQL Server installation. Although capacity and performance will vary from one deployment to the next, this Quick Start provides a reference configuration that you can use as a starting point. The AWS CloudFormation template deploys the WSFC nodes using the memory-optimized r5.2xlarge instance type by default.

In an effort to provide highly performant and durable storage, we’ve also included Amazon Elastic Block Store (Amazon EBS) volumes in this reference architecture. EBS volumes are network-attached disk storage, which you can create and attach to EC2 instances. Once these are attached, you can create a file system on top of these volumes, run a database, or use them in any other way you would use a block device. EBS volumes are placed in a specific Availability Zone, where they are automatically replicated to protect you from the failure of a single component.

Provisioned IOPS EBS volumes offer storage with consistent and low-latency performance. They are backed by solid state drives (SSDs) and are designed for applications with I/O-intensive workloads such as databases.

Amazon EBS-optimized instances, such as the R5 instance type, deliver dedicated throughput between Amazon EC2 and Amazon EBS. The dedicated throughput minimizes contention between Amazon EBS I/O and other traffic from your EC2 instance, and provides the best performance for your EBS volumes.

By default, on each WSFC node, the Quick Start deploys three 500-GiB General Purpose SSD volumes to store databases, logs, tempdb, and backups. This is in addition to the root General Purpose SSD volume used by the operating system. This volume type delivers a consistent baseline of 3 IOPS/GiB, which provides a total of 1,500 IOPS per volume for SQL Server database and log volumes. You can customize the volume size, and you can also switch to using dedicated IOPS volumes with the volume you specify. If you need more IOPS per volume, consider using Provisioned IOPS SSD volumes by changing the SQL Server volume type and SQL Server volume IOPS parameters, or use disk striping within Windows.

The default disk layout for SQL Server in this Quick Start uses the following EBS volumes:

  • One General Purpose SSD volume (100 GiB) for the operating system (C:)

  • One General Purpose SSD volume (500 GiB) to host the SQL Server database files (D:)

  • One General Purpose SSD volume (500 GiB) to host the SQL Server log files (E:)

  • One General Purpose SSD volume (500 GiB) to host the SQL Server tempdb and backup files (F:)

Figure 6 shows the disk layout on each SQL Server node. The Z: drive is instance storage that can be used for ephemeral data, such as the operating system page file. Keep in mind that data on instance storage will be lost when you stop your EC2 instance.

image
Figure 6. WSFC node disk layout

IP addressing on the WSFC nodes

In order to support WSFC and Always On availability group listeners, each node that hosts the SQL Server instances participating in the cluster will need to have three IP addresses assigned:

  • One IP address is used as the primary IP address for the instance.

  • A second IP address acts as the WSFC IP resource.

  • A third IP address is used to host the Always On availability group listener.

When you launch the AWS CloudFormation template, you can specify the addresses for each node. By default, the 10.0.0.0/19, 10.0.32.0/19, and 10.0.64.0/19 CIDR blocks are used for the private subnets.

image
Figure 7. Defining WSFC node IP addresses

Windows Server Failover Clustering

Once your Windows Server instances have been deployed and domain-joined, you’re ready to build the cluster. The AWS CloudFormation templates carry out this task when deploying the second node. If you use the default template parameter settings, the Quick Start executes Windows PowerShell commands to complete this task by using the xFailOverCluster PowerShell DSC module.

You can observe the default configuration for the failover cluster and the availability group by looking at the Node1Config.ps1 and CreateAGNode1.ps1 scripts in the GitHub repository for the Quick Start.

Deployment options

This Quick Start provides two deployment options:

  • Deploy SQL Server into a new VPC. This option builds a new AWS environment consisting of the VPC, subnets, NAT gateways, security groups, bastion hosts, and other infrastructure components. It then deploys SQL Server into this new VPC.

  • Deploy SQL Server into an existing VPC. This option provisions SQL Server in your existing AWS infrastructure.

The Quick Start provides separate templates for these options. It also lets you configure Classless Inter-Domain Routing (CIDR) blocks, instance types, and SQL Server settings, as discussed later in this guide.

Deployment steps

Sign in to your AWS account

  1. Sign in to your AWS account at https://aws.amazon.com with an IAM user role that has the necessary permissions. For details, see Planning the deployment earlier in this guide.

  2. Make sure that your AWS account is configured correctly, as discussed in the Technical requirements section.

Launch the Quick Start

You are responsible for the cost of the AWS services used while running this Quick Start reference deployment. There is no additional cost for using this Quick Start. For full details, see the pricing pages for each AWS service used by this Quick Start. Prices are subject to change.
  1. Sign in to your AWS account, and choose one of the following options to launch the AWS CloudFormation template. For help with choosing an option, see deployment options earlier in this guide.

Deploy SQL Server into a new VPC on AWS

View template

Deploy SQL Server into an existing VPC on AWS

View template

If you’re deploying SQL Server into an existing VPC, make sure that your VPC has two private subnets in different Availability Zones for the workload instances, and that the subnets aren’t shared. This Quick Start doesn’t support shared subnets. These subnets require NAT gateways in their route tables, to allow the instances to download packages and software without exposing them to the internet. Also make sure that the domain name option in the DHCP options is configured as explained in DHCP options sets. You provide your VPC settings when you launch the Quick Start.

Each deployment takes about 2 hours to complete.

  1. Check the AWS Region that’s displayed in the upper-right corner of the navigation bar, and change it if necessary. This is where the network infrastructure for SQL Server will be built. The template is launched in the us-east-1 Region by default.

  1. On the Create stack page, keep the default setting for the template URL, and then choose Next.

  2. On the Specify stack details page, change the stack name if needed. Review the parameters for the template. Provide values for the parameters that require input. For all other parameters, review the default settings and customize them as necessary.

In the following tables, parameters are listed by category and described separately for the deployment options. When you finish reviewing and customizing the parameters, choose Next.

Unless you are customizing the Quick Start templates for your own deployment projects, keep the default settings for the parameters Quick Start S3 bucket name, Quick Start S3 bucket Region, and Quick Start S3 key prefix. Changing these settings automatically updates code references to point to a new Quick Start location. For more information, see the AWS Quick Start Contributor’s Guide.

Launch into a new VPC

Table 1. Network configuration
Parameter label (name) Default value Description

Availability Zones (AvailabilityZones)

Requires input

List of Availability Zones to use for the subnets in the VPC. Note: The logical order is preserved and 2 zones must be provided unless the Third Availability Zone parameter is specified, in which case 3 zones must be provided.

Third Availability Zone (ThirdAZ)

no

Enable a deployment with three Availability Zones. The third Availability Zone can either be used just for the witness, or it can be a full SQL cluster node. Note that if witness is chosen, the file server private IP address parameter must be set to an IP in the third subnet range.

VPC CIDR (VPCCIDR)

10.0.0.0/16

CIDR block for the VPC.

Public subnet 1 CIDR (PublicSubnet1CIDR)

10.0.128.0/20

CIDR block for the public subnet 2 located in Availability Zone 2.

Public subnet 2 CIDR (PublicSubnet2CIDR)

10.0.144.0/20

CIDR block for the optional public subnet 3 located in Availability Zone 3.

Public subnet 3 CIDR (PublicSubnet3CIDR)

10.0.160.0/20

(Optional) CIDR block for the optional public subnet 3 located in Availability Zone 3.

Private subnet 1 CIDR (PrivateSubnet1CIDR)

10.0.0.0/19

CIDR block for private subnet 1 located in Availability Zone 1.

Private subnet 2 CIDR (PrivateSubnet2CIDR)

10.0.32.0/19

CIDR block for private subnet 2 located in Availability Zone 2.

Private subnet 3 CIDR (PrivateSubnet3CIDR)

10.0.64.0/19

(Optional) CIDR block for optional private subnet 3 located in Availability Zone 3.

Table 2. Amazon EC2 configuration
Parameter label (name) Default value Description

Key pair name (KeyPairName)

Requires input

Public/private key pairs allow you to securely connect to your instance after it launches.

Tenancy (HostType)

Shared

Host type. If Dedicated or Dedicated Host is selected, hosts will be created in each Availability Zone.

BYOL AMI to use on dedicated host (DedicatedHostAMI)

Blank string

If host type is set to "Dedicated" or "Dedicated Host", you need to specify your imported BYOL AMI ID.

Table 3. Active Directory configuration
Parameter label (name) Default value Description

AD scenario type (ADScenarioType)

AWS Directory Service for Microsoft AD (Enterprise Edition)

Select the type of AD DS deployment to use: AWS Directory Service for Microsoft AD or managing your own Amazon EC2 AD instances.

Domain DNS name (DomainDNSName)

example.com

Fully qualified domain name (FQDN) of the forest root domain.

Domain NetBIOS name (DomainNetBIOSName)

example

NetBIOS name of the domain (up to 15 characters) for users of earlier versions of Windows.

Domain admin user name (DomainAdminUser)

Admin

User name for the account that will be added as domain administrator. This is separate from the default administrator account. Note: This user is used for Self-Managed AD Scenario, when using AWS Directory Service for Microsoft AD "Admin" is the default regardless of the value provided. provided.

Domain admin password (DomainAdminPassword)

Requires input

Password for the domain admin user. Must be at least 8 characters containing letters, numbers, and symbols.

Table 4. Self-managed Active Directory configuration
Parameter label (name) Default value Description

Domain controller 1 instance type (ADServer1InstanceType)

m5.xlarge

Amazon EC2 instance type for the first Active Directory instance.

Domain controller 1 NetBIOS name (ADServer1NetBIOSName)

DC1

NetBIOS name of the first Active Directory server (up to 15 characters).

Domain controller 1 private IP address (ADServer1PrivateIP)

10.0.0.10

Fixed private IP for the first Active Directory server located in Availability Zone 1.

Domain controller 2 instance type (ADServer2InstanceType)

m5.xlarge

Amazon EC2 instance type for the second Active Directory instance.

Domain controller 2 NetBIOS name (ADServer2NetBIOSName)

DC2

NetBIOS name of the second Active Directory server (up to 15 characters).

Domain controller 2 private IP address (ADServer2PrivateIP)

10.0.32.10

Fixed private IP for the second Active Directory server located in Availability Zone 2.

Table 5. Remote Desktop Gateway configuration
Parameter label (name) Default value Description

Allowed RDGW external access CIDR (RDGWCIDR)

Requires input

Allowed CIDR block for external access to the Remote Desktop Gateway instances.

Number of RDGW hosts (NumberOfRDGWHosts)

1

Enter the number of Remote Desktop Gateway hosts to create.

RDGW instance type (RDGWInstanceType)

t2.large

Amazon EC2 instance type for the Remote Desktop Gateway instances.

Table 6. SQL Server configuration
Parameter label (name) Default value Description

SQL Server version (SQLServerVersion)

2019

Version of SQL Server to install on failover cluster nodes.

Service account name (SQLServiceAccount)

sqlsa

User name for the SQL Server service account. This account is a domain user.

Service account password (SQLServiceAccountPassword)

Requires input

Password for the SQL Server service account. Must be at least 8 characters, containing letters, numbers and symbols.

Amazon-provided SQL Server license (SQLLicenseProvided)

yes

License SQL Server from AWS Marketplace.

Availability group name (AvailabiltyGroupName)

SQLAG1

NetBIOS name of the Availablity Group (up to 15 characters).

SQL Server volume IOPS (VolumeIops)

1000

Provisioned IOPS for the SQL data, logs and tempDb volumes. This parameter is applicable only when the SQL Server volume type is set to "io1".

SQL Server volume size (VolumeSize)

500

Volume size for the SQL data, logs and tempDb volumes, in GiB.

SQL Server volume type (VolumeType)

gp2

Volume type for the SQL data, logs and tempDb volumes.

SQL Server 2016 installation media location (SQL2016Media)

https://download.microsoft.com/download/9/0/7/907AD35F-9F9C-43A5-9789-52470555DB90/ENU/SQLServer2016SP1-FullSlipstream-x64-ENU.iso

SQL Server 2016 installation media location

SQL Server 2017 installation media location (SQL2017Media)

https://download.microsoft.com/download/E/F/2/EF23C21D-7860-4F05-88CE-39AA114B014B/SQLServer2017-x64-ENU.iso

SQL Server 2017 installation media location

SQL Server 2019 installation media location (SQL2019Media)

https://go.microsoft.com/fwlink/?linkid=866664

SQL Server 2019 installation media location

Table 7. Failover cluster configuration
Parameter label (name) Default value Description

Cluster NetBIOS name (ClusterName)

WSFCCluster1

NetBIOS name of the cluster (up to 15 characters).

File share witness type (WitnessType)

Windows file share

Type of file share to use for failover cluster witness.

Instance type for cluster nodes (WSFCNodeInstanceType)

r5.2xlarge

Amazon EC2 instance type for the failover cluster nodes.

Cluster node 1 NetBIOS name (WSFCNode1NetBIOSName)

WSFCNode1

NetBIOS name of the first failover cluster node (up to 15 characters).

Cluster node 1 private IP address 1 (WSFCNode1PrivateIP1)

10.0.0.100

Primary private IP for the first cluster node located in Availability Zone 1.

Cluster node 1 private IP address 2 (WSFCNode1PrivateIP2)

10.0.0.101

Secondary private IP for failover cluster on first cluster node.

Cluster node 1 private IP address 3 (WSFCNode1PrivateIP3)

10.0.0.102

Third private IP for SQL Server availability group listener on first cluster node.

Cluster node 2 NetBIOS name (WSFCNode2NetBIOSName)

WSFCNode2

NetBIOS name of the second WSFC node (up to 15 characters).

Cluster node 2 private IP address 1 (WSFCNode2PrivateIP1)

10.0.32.100

Primary private IP for the second cluster node located in Availability Zone 2.

Cluster node 2 private IP address 2 (WSFCNode2PrivateIP2)

10.0.32.101

Secondary private IP for failover cluster on second cluster node.

Cluster node 2 private IP address 3 (WSFCNode2PrivateIP3)

10.0.32.102

Third private IP for SQL Server availability group listener on second cluster node.

Cluster node 3 NetBIOS name (WSFCNode3NetBIOSName)

WSFCNode3

NetBIOS name of the optional third WSFC node (up to 15 characters).

Cluster node 3 private IP address 1 (WSFCNode3PrivateIP1)

10.0.64.100

Primary private IP for the optional third cluster node located in Availability Zone 3.

Cluster node 3 private IP address 2 (WSFCNode3PrivateIP2)

10.0.64.101

Secondary private IP for failover cluster on optional third cluster node.

Cluster node 3 private IP address 3 (WSFCNode3PrivateIP3)

10.0.64.102

Third private IP for SQL Server availability group listener on optional third cluster node.

File server NetBIOS name (WSFCFileServerNetBIOSName)

WSFCFileServer

NetBIOS name of the witness file server (up to 15 characters).

File server instance type (WSFCFileServerInstanceType)

m5.large

Amazon EC2 instance type for a fileserver for witness and replication folders.

File server private IP address (WSFCFileServerPrivateIP)

10.0.0.200

Primary private IP for the fileserver located in Availability Zone 1.

Table 8. AWS Quick Start configuration
Parameter label (name) Default value Description

Quick Start S3 bucket name (QSS3BucketName)

aws-quickstart

S3 bucket name for the Quick Start assets. This name can include numbers, lowercase letters, uppercase letters, and hyphens (-). It cannot start or end with a hyphen (-).

Quick Start S3 bucket region (QSS3BucketRegion)

us-east-1

The AWS Region where the Quick Start S3 bucket (QSS3BucketName) is hosted. When using your own bucket, you must specify this value.

Quick Start S3 key prefix (QSS3KeyPrefix)

quickstart-microsoft-sql/

S3 key prefix for the Quick Start assets. This prefix can include numbers, lowercase letters, uppercase letters, hyphens (-), and forward slash (/).

Launch into an existing VPC

Table 9. Network configuration
Parameter label (name) Default value Description

Third Availability Zone (ThirdAZ)

no

Choose full or witness to enable a deployment with three Availability Zones. The third zone can be used for the witness, or it can be a full SQL cluster node.

VPC ID (VPCID)

Requires input

ID of the VPC (e.g., vpc-0343606e).

Private subnet 1 ID (PrivateSubnet1ID)

Requires input

ID of the private subnet 1 in Availability Zone 1 (e.g., subnet-a0246dcd).

Private subnet 2 ID (PrivateSubnet2ID)

Requires input

ID of the private subnet 2 in Availability Zone 2 (e.g., subnet-a0246dcd).

Private subnet 3 ID (PrivateSubnet3ID)

Blank string

(Optional) ID of the private subnet 3 in Availability Zone 3 (e.g., subnet-a0246dcd).

Table 10. Amazon EC2 configuration
Parameter label (name) Default value Description

Key pair name (KeyPairName)

Requires input

Public/private key pair, which allows you to securely connect to your instance after it launches.

Table 11. Active Directory configuration
Parameter label (name) Default value Description

AD scenario type (ADScenarioType)

AWS Directory Service for Microsoft AD (Enterprise Edition)

Select the type of AD DS deployment to use: AWS Directory Service for Microsoft AD or managing your own Amazon EC2 AD instances.

Domain DNS name (DomainDNSName)

example.com

Fully qualified domain name (FQDN).

Domain NetBIOS name (DomainNetBIOSName)

EXAMPLE

NetBIOS name of the domain (up to 15 characters) for users of earlier versions of Windows.

Domain admin user name (DomainAdminUser)

Admin

User name for the account that will be used as domain administrator. This is separate from the default "Administrator" account.

Domain admin password (DomainAdminPassword)

Requires input

Password for the domain admin user. Must be at least 8 characters, containing letters, numbers, and symbols.

Security group ID for AD domain members (DomainMemberSGID)

Requires input

ID of the domain member security group (e.g., sg-7f16e910).

Domain controller 1 IP address (ADServer1PrivateIP)

10.0.0.10

The IP address of the first Domain Controller. For AWS Managed AD, use the first DNS address.

Domain controller 2 IP address (ADServer2PrivateIP)

10.0.32.10

The IP address of the first Domain Controller. For AWS Managed AD, use the second DNS address.

Table 12. SQL Server configuration
Parameter label (name) Default value Description

SQL Server version (SQLServerVersion)

2019

Version of SQL Server to install on failover cluster nodes.

Service account name (SQLServiceAccount)

sqlsa

User name for the SQL Server service account. This account is a domain user.

Service account password (SQLServiceAccountPassword)

Requires input

Password for the SQL Server service account. Must be at least 8 characters, containing letters, numbers and symbols.

Amazon-provided SQL Server license (SQLLicenseProvided)

yes

License SQL Server from AWS Marketplace.

Availability group name (AvailabiltyGroupName)

SQLAG1

NetBIOS name of the availability group (up to 15 characters).

Data volume size (Volume1Size)

500

Volume size for the SQL Server data drive, in GiB.

Data volume type (Volume1Type)

gp2

Volume type for the SQL Server data drive.

Data volume IOPS (Volume1Iops)

1000

IOPS for the SQL Server data drive (only used when volume type is io1).

Logs volume size (Volume2Size)

500

Volume size for the SQL Server logs drive, in GiB.

Logs volume type (Volume2Type)

gp2

Volume type for the SQL Server logs drive.

Logs volume IOPS (Volume2Iops)

1000

IOPS for the SQL Server logs drive (only used when volume type is io1).

TempDB volume size (Volume3Size)

500

Volume size for the SQL Server TempDB drive, in GiB.

TempDB volume type (Volume3Type)

gp2

Volume type for the SQL Server TempDB drive.

TempDB volume IOPS (Volume3Iops)

1000

IOPS for the SQL Server TempDB drive (only used when volume type is io1).

SQL Server 2016 installation media location (SQL2016Media)

https://download.microsoft.com/download/9/0/7/907AD35F-9F9C-43A5-9789-52470555DB90/ENU/SQLServer2016SP1-FullSlipstream-x64-ENU.iso

SQL Server 2016 installation media location

SQL Server 2017 installation media location (SQL2017Media)

https://download.microsoft.com/download/E/F/2/EF23C21D-7860-4F05-88CE-39AA114B014B/SQLServer2017-x64-ENU.iso

SQL Server 2017 installation media location

SQL Server 2019 installation media location (SQL2019Media)

https://go.microsoft.com/fwlink/?linkid=866664

SQL Server 2019 installation media location

Table 13. Failover cluster configuration
Parameter label (name) Default value Description

Cluster NetBIOS name (ClusterName)

WSFCCluster1

NetBIOS name of the cluster (up to 15 characters).

File share witness type (WitnessType)

Windows file share

Type of file share to use for failover cluster witness.

File server instance type (WSFCFileServerInstanceType)

m5.large

Amazon EC2 instance type for a file server used to share install media, witness, and replication folders.

File server NetBIOS name (WSFCFileServerNetBIOSName)

WSFCFileServer

NetBIOS name of the WSFC file server (up to 15 characters).

File server private IP address (WSFCFileServerPrivateIP)

10.0.0.200

Primary private IP for the file server located in Availability Zone 1.

Instance type for cluster node 1 (WSFCNode1InstanceType)

r5.2xlarge

Amazon EC2 instance type for the first WSFC node.

Cluster node 1 NetBIOS name (WSFCNode1NetBIOSName)

WSFCNode1

NetBIOS name of the first WSFC node (up to 15 characters).

Cluster node 1 private IP address 1 (WSFCNode1PrivateIP1)

10.0.0.100

Primary private IP for the first WSFC node located in Availability Zone 1.

Cluster node 1 private IP address 2 (WSFCNode1PrivateIP2)

10.0.0.101

Secondary private IP for WSFC cluster on first WSFC node.

Cluster node 1 private IP address 3 (WSFCNode1PrivateIP3)

10.0.0.102

Third private IP for availability group listener on first WSFC node.

Instance type for cluster node 2 (WSFCNode2InstanceType)

r5.2xlarge

Amazon EC2 instance type for the second WSFC node.

Cluster node 2 NetBIOS name (WSFCNode2NetBIOSName)

WSFCNode2

NetBIOS name of the second WSFC node (up to 15 characters).

Cluster node 2 private IP address 1 (WSFCNode2PrivateIP1)

10.0.32.100

Primary private IP for the second WSFC node located in Availability Zone 2.

Cluster node 2 private IP address 2 (WSFCNode2PrivateIP2)

10.0.32.101

Secondary private IP for WSFC cluster on second WSFC node.

Cluster node 2 private IP address 3 (WSFCNode2PrivateIP3)

10.0.32.102

Third private IP for availability group listener on second WSFC node.

Instance type for cluster node 3 (WSFCNode3InstanceType)

r5.2xlarge

Amazon EC2 instance type for the third WSFC node.

Cluster node 3 NetBIOS name (WSFCNode3NetBIOSName)

WSFCNode3

NetBIOS name of the third WSFC node (up to 15 characters).

Cluster node 3 private IP address 1 (WSFCNode3PrivateIP1)

10.0.64.100

Primary private IP for the optional third WSFC node located in Availability Zone 3.

Cluster node 3 private IP address 2 (WSFCNode3PrivateIP2)

10.0.64.101

Secondary private IP for WSFC cluster on optional third WSFC node.

Cluster node 3 private IP address 3 (WSFCNode3PrivateIP3)

10.0.64.102

Third private IP for availability group listener on optional third WSFC node.

Table 14. AWS Systems Manager AMI configuration
Parameter label (name) Default value Description

Windows Server 2019 full base AMI (WS2019FULLBASE)

/aws/service/ami-windows-latest/Windows_Server-2019-English-Full-Base

NO_DESCRIPTION

Windows Server 2019 full locale English with SQL Enterprise 2019 AMI (WS2019FULLSQL2019ENT)

/aws/service/ami-windows-latest/Windows_Server-2019-English-Full-SQL_2019_Enterprise

NO_DESCRIPTION

Windows Server 2019 Locale English with SQL Enterprise 2017 AMI (WS2019FULLSQL2017ENT)

/aws/service/ami-windows-latest/Windows_Server-2019-English-Full-SQL_2017_Enterprise

NO_DESCRIPTION

Windows Server 2019 Locale English with SQL Enterprise 2016 AMI (WS2019FULLSQL2016ENT)

/aws/service/ami-windows-latest/Windows_Server-2019-English-Full-SQL_2016_SP2_Enterprise

NO_DESCRIPTION

Table 15. AWS Quick Start configuration
Parameter label (name) Default value Description

Quick Start S3 bucket name (QSS3BucketName)

aws-quickstart

S3 bucket name for the Quick Start assets. This name can include numbers, lowercase letters, uppercase letters, and hyphens (-). It cannot start or end with a hyphen (-).

Quick Start S3 bucket region (QSS3BucketRegion)

us-east-1

The AWS Region where the Quick Start S3 bucket (QSS3BucketName) is hosted. When using your own bucket, you must specify this value.

Quick Start S3 key prefix (QSS3KeyPrefix)

quickstart-microsoft-sql/

S3 key prefix for the Quick Start assets. This prefix can include numbers, lowercase letters, uppercase letters, hyphens (-), and forward slash (/).

  1. On the Configure stack options page, you can specify tags (key-value pairs) for resources in your stack and set advanced options. When you’re finished, choose Next.

  2. On the Review page, review and confirm the template settings. Under Capabilities, select the two check boxes to acknowledge that the template creates IAM resources and might require the ability to automatically expand macros.

  3. Choose Create stack to deploy the stack.

  4. Monitor the status of the stack. When the status is CREATE_COMPLETE, the SQL Server deployment is ready.

  5. Use the values displayed in the Outputs tab for the stack, as shown in Figure 8, to view the created resources.

cfn_outputs
Figure 8. SQL Server outputs after successful deployment

Post-launch task

There is a known issue with the Microsoft SQL Quick Start wherein the secondary cluster IP address is not added to the cluster.

image
Figure 9. WSFC Cluster with only one IP address

To add the secondary cluster IP address, do the following.

  1. On the first cluster node, launch an elevated Windows PowerShell window and run the following commands.

    1. Import-Module FailoverClusters

    2. Get-ClusterGroup

      The name of the Cluster Group should be “Cluster Group.”

    3. Add-ClusterResource –Name NewIP –ResourceType “IP Address” –Group “Cluster Group”

image
Figure 10. Creating a new IP address cluster resource in PowerShell
  1. From Windows Administrative Tools, open the Failover Clustering management GUI, select the cluster in the left-hand column, and expand the cluster in Core Cluster Resources. The new IP address should be populated.

image
Figure 11. New IP address cluster resource
  1. Open the context (right-click) menu for the new IP address, and choose Properties. Select the secondary network, and enter an IP address (i.e. 10.0.32.101), then choose OK.

image
Figure 12. Setting the IP address cluster resource value
  1. Open the context (right-click) menu for the new IP address, and choose Bring Online.

image
Figure 13. Bringing the new IP address online
  1. To create an “OR” dependency for the cluster, under Core Cluster Resources, open the context (right-click) menu for the cluster name, and choose Properties.

  2. On the Dependencies tab, add the new IP address as a resource, change the dependency type to OR, and then choose OK.

image
Figure 14. Adding the new IP address as a cluster dependency

Test the deployment

Note If you’re using a third Availability Zone as a full SQL Server cluster node (that is, if you set the Third Availability Zone parameter to full), take that into consideration when following the steps in this section.

Before you put the availability group into production, you should test your deployment and familiarize yourself with the cluster’s behavior during a high availability automatic failover or a disaster recovery event.

  1. Open the Remote Desktop Connection application (mstsc.exe), connect to the Remote Desktop Gateway instance, and then connect to the WSFC node (e.g., WSFCNode1) in that zone.

  1. On the first cluster node instance, open the Failover Cluster Manager to view the cluster core resources. Make sure that the cluster, one of the two listed IP addresses, and the file share witness are online.

image
Figure 15. Viewing the Failover Cluster Manager
  1. Open SQL Server Management Studio. In Object Explorer, open the context (right-click) menu for the AlwaysOn High Availability node, and then launch the dashboard for the availability group (e.g., SQLAG1).

  2. In the dashboard, view the availability replicas and make sure that their synchronization state is *Synchronized. *

image
Figure 16. Viewing the Always On High Availability dashboard with all nodes synchronized
  1. Make sure that the primary instance and the IP address in the Cluster Core Resources pane of Failover Cluster Manager are coordinated. That is, if the primary instance is WSFCNode1, the IP address 10.0.0.101 should be online. If you need to move the cluster core resources to WSFCNode1, you can do so through PowerShell by using the command:

Get-ClusterGroup 'Cluster Group' | Move-ClusterGroup -Node WSFCNode1

  1. Sign in to the AWS Management Console, and open the Amazon EC2 console at https://console.aws.amazon.com/ec2/.

  2. Stop the primary instance (e.g., WSFCNode1).

  3. Open the Remote Desktop Connection application (mstsc.exe), and then connect to the second cluster node (e.g., WSFCNode2) in Availability Zone 2.

  4. On the second cluster node instance, use the Failover Cluster Manager to view the cluster core resources. Note that the IP address that was previously offline (e.g., 10.0.32.101) is now online.

image
Figure 17. Viewing the Failover Cluster Manager with WSFCNode1 offline
  1. Open SQL Server Management Studio. In Object Explorer, open the context (right-click) menu for the AlwaysOn High Availability node, and launch the dashboard for the availability group (e.g., SQLAG1).

  2. In the dashboard, view the availability replicas. Note that now the primary instance has switched to WSFCNode2, and that the synchronization state of WSFCNode1 is Not Synchronizing.

image
Figure 18. Always On High Availability dashboard with the first cluster node offline
  1. At this point, you can start the WSFCNode1 instance again in the Amazon EC2 console. When the instance is online, use the Failover wizard in the Availability Group dashboard and switch the primary instance back to WSFCNode1.

Note We recommend that you use MultiSubnetFailover=true in your SQL client connection string. This property enables faster failover for all availability groups in SQL Server and will significantly reduce failover time for single and multi-subnet Always On topologies. If you have legacy clients that need to connect to an availability group listener and cannot use MultiSubnetFailover, we recommend that you change the RegisterAllProvidersIP setting to 0 by using the Set-ClusterParameter cmdlet.

FAQ

Q. I encountered a CREATE_FAILED error when I launched the Quick Start.

A. If AWS CloudFormation fails to create the stack, we recommend that you relaunch the template with Rollback on failure set to Disabled. (This setting is under Advanced in the AWS CloudFormation console, Options page.) With this setting, the stack’s state is retained and the instance is left running, so you can troubleshoot the issue. (For Windows, look at the log files in %ProgramFiles%\Amazon\EC2ConfigService and C:\cfn\log.)

When you set Rollback on failure to Disabled, you continue to incur AWS charges for this stack. Delete the stack when you finish troubleshooting.

For additional information, see Troubleshooting AWS CloudFormation on the AWS website.

Q. I encountered a size limitation error when I deployed the AWS CloudFormation templates.

A. We recommend that you launch the Quick Start templates from the links in this guide or from another S3 bucket. If you deploy the templates from a local copy on your computer or from a location other than an S3 bucket, you might encounter template size limitations. For more information, see AWS CloudFormation quotas on the AWS website.

Additional Resources

Send us feedback

To post feedback, submit feature ideas, or report bugs, use the Issues section of the GitHub repository for this Quick Start. To submit code, see the Quick Start Contributor’s Guide.

Quick Start reference deployments

GitHub repository

Visit our GitHub repository to download the templates and scripts for this Quick Start, to post your comments, and to share your customizations with others.


Notices

This document is provided for informational purposes only. It represents AWS’s current product offerings and practices as of the date of issue of this document, which are subject to change without notice. Customers are responsible for making their own independent assessment of the information in this document and any use of AWS’s products or services, each of which is provided “as is” without warranty of any kind, whether expressed or implied. This document does not create any warranties, representations, contractual commitments, conditions, or assurances from AWS, its affiliates, suppliers, or licensors. The responsibilities and liabilities of AWS to its customers are controlled by AWS agreements, and this document is not part of, nor does it modify, any agreement between AWS and its customers.

The software included with this paper is licensed under the Apache License, version 2.0 (the "License"). You may not use this file except in compliance with the License. A copy of the License is located at http://aws.amazon.com/apache2.0/ or in the accompanying "license" file. This code is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either expressed or implied. See the License for specific language governing permissions and limitations.