Menu
SQL Server with WSFC on AWS
Quick Start Reference Deployment Guide

Implementation Details

SQL Server Enterprise Edition

Amazon Machine Images (AMIs) for the SQL Server 2014 and 2016 Enterprise edition are available for launch on AWS, with the limitations discussed in the Costs and Licenses section. If you keep the default (no) setting for the Amazon-Provided SQL License (SQLLicenseProvided) parameter, 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, 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. If you have to re-run the installation, make sure you select Run as Administrator to start the installation.

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 administrators groups on each WSFC node.

Note

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 at http://www.microsoft.com/evalcenter/.

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 r4.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 R4 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 4 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.


          WSFC node disk layout

Figure 4: WSFC node disk layout

IP Addressing on the WSFC Nodes

In order to support WSFC and Always On Availability Group listeners, each node hosting the SQL Server instances participating in the cluster will need to have a total of 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.


          Defining WSFC node IP addresses

Figure 5: Defining WSFC node IP addresses

Windows Server Failover Clustering

Once your Windows Server 2012 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 parameters settings, the Quick Start executes the following PowerShell commands to complete this task:

Copy
Install-WindowsFeature failover-clustering -IncludeManagementTools New-Cluster -Name WSFCluster1 -Node WSFCNODE1,WSFCNODE2 -StaticAddress 10.0.0.101,10.0.64.101

The first command runs on each instance during the bootstrapping process. It installs the required components and management tools for the failover clustering services. The second command runs near the end of the bootstrapping process on the second node and is responsible for creating the cluster and for defining the server nodes and IP addresses.

By default, the Quick Start configures an even number of servers in the cluster. You’ll need a third resource to maintain a majority vote to keep the cluster online in the event of an individual server failure. For this, the Quick Start uses a dedicated file share witness instance, which requires modifying the cluster settings to NodeAndFileShareMajority. The first step in making this configuration change is to create the share. By default, the Quick Start creates a dedicated instance in the first Availability Zone to host this share. For production environments, you can also set the Third AZ parameter to witness to create a dedicated instance with a file share in a third Availability Zone. Alternatively, you can use any domain-joined server for this task. (This isn’t included in the Quick Start.) If you set the Third AZ parameter to full, the Quick Start will keep the quorum settings to the default node majority and will create a third SQL Server node in the third Availability Zone. Note that some AWS Regions support only two Availability Zones; for a current list, see the AWS Global Infrastructure webpage.

By default, the witness file share is \\WSFCFileServer\witness. The Active Directory computer account that is created when forming the cluster (e.g., WSFCNODE1) is given NTFS permissions to access the share. After that, the cluster is updated to use the share as the file share witness resource.

Copy
Set-ClusterQuorum -NodeAndFileShareMajority \\WSFCFileServer\witness

Always On Configuration

After SQL Server Enterprise edition has been installed and the Windows Server failover cluster has been built, the Quick Start enables SQL Server Always On with the following PowerShell command:

Copy
Enable-SqlAlwaysOn -ServerInstance WSFCNODE1

The Quick Start runs this command on each node, and the proper server name is provided as a value for the ServerInstance parameter.

The Quick Start automated solution ends after enabling SQL Server Always On. When the deployment is complete, you can create your databases and make them highly available by creating an Always On Availability Group. This process is covered in step 3 of the deployment instructions.

When you create an availability group, you’ll need to provide a network share used to perform an initial data synchronization. As you progress through the New Availability Group wizard, a full backup for each selected database is taken and placed in the share. The secondary node connects to the share and restores the database backups before joining the availability group.

To accommodate this initial synchronization, the Quick Start creates a folder called C:\replica on the first domain controller, using the share name replica. By default, the file share is defined as \\WSFCFileServer\replica. If you set the Third AZ parameter to full, the Quick Start create the replica share on the first WSFC node in the first Availability Zone. Since SQL services run in this account, the sqlsa Active Directory user account is given NTFS permissions to this share.