Rajendra Gupta
Deployments options

AWS Launch Wizard for quick SQL Server Always On deployments

June 29, 2020 by

SQL Server Always On is a high-availability and disaster recovery solution. We can use multiple secondary replicas for configuring database backups, redirect read requests to offload primary instance load.

For on-premises infrastructure, you can follow articles here, AlwaysOn Availability Groups, and configure SQL Server always-on functionality.

In the AWS cloud, the overall logic remains the same; however, you need to use AWS resources to deploy Virtual machine, networking component, domain controller, failover clustering configurations. It is a multi-level task to build SQL Server Always On from scratch. You might face difficulty in deploying these solutions, and you might forget a few steps in the configuration.

Looking at these issues, Amazon provided an AWS Launch Wizard to deploy, configure SQL Server Always on in a single step. It also follows the best practices for AWS database solutions. It improves productivity, reduces the time, and less human intervention eliminates error chances as well. AWS Launch Wizard requires specific inputs such as the number of nodes, their compute capacity, connectivity. It also gives you a monthly estimate for using all resources deployed by the launch wizards. You can make instant changes in the resources based on their cost and approve configuration for AWS to deploy them automatically. It also works as a CloudFormation template that you can reuse to deploy resources anytime later.

Requirements

  • AWS launch wizard uses the Windows server 2019/2016/2002 R2 operating system for underlying resources
  • We can deploy Microsoft SQL Server 2019/2017/2016 versions

Let’s explore the configuration of the SQL Server always on using AWS Launch wizard.

AWS Launch Wizard configuration for SQL Server Always On

You can use the following two ways to open the AWS launch wizard.

  • Search for Launch Wizard in the AWS services, as shown below

AWS Launch Wizard  for SQL Server Always On

  • Alternatively, if we try to create a new EC2 instance and search for SQL related Amazon Machine Image(AMI), it gives you a hyperlink for AWS Launch Wizard

    Amazon Machine Image (AMI)

In the welcome page, we get deployments options – MS SQL Server and SAP.

Deployments options

Click on the Create Deployment.

Step 1: Choose application

Select Microsoft SQL Server Always On.

Choose application

Scroll down, and you can see a default IAM role AmazonEC2RoleForLaunchWizard. AWS wizard uses this role permissions to deploy various AWS services on the user’s behalf. This IAM role uses AmazonSSMManagedInstanceCore and AmazonEC2RolePolicyForLaunchWizard IAM policies.

You can click on the documentation link to get more information on this role.

Default IAM role

Step 2: Configure application settings

Deployment name

On the next page, specify a name for the deployment. You can use a maximum of 10 characters in the deployment name.

We must use alphanumeric characters for the deployment. You get the following error message in case of an invalid deployment name.

Deployment name

I specify the deployment name – SQLShack. You can configure notifications using the Simple Notification Service(SNS), but it is an optional configuration. We can skip this step or click on the Create New SNS Topic for the notification settings.

Configure application settings

Configure application settings

Connectivity

In the next part, we can create a new key pair or choose an existing key pair. Click on the Create new key pair name for new credentials. We use this key pair to connect with the AWS EC2 instances.

Connectivity

It takes you to the EC2 key pairs section. You can see the existing key pairs in your account. Click on Create key pair.

Create key pair

Specify a key pair name and file format. You should save the generated key pairs to a secure and safe location.

Specify file format for SQL Server Always On

Virtual Private Cloud (VPC)

AWS uses virtual private cloud (VPC) to define the networking components such as subnets, IP ranges. Your AWS resources become part of the VPC to communicate with each other. You can use the existing VPC or create a new VPC using this AWS Launch wizard.

Select the VPC from the drop-down and its associated public subnet. You should be careful in the VPC and subnet configurations else it might end up in having communication issues between multiple AWS services. VPC must consist of one public and two private subnets.

Virtual Private Cloud (VPC)

Scroll down and select availability zones and their private subnets. If you use more than two nodes in always-on, you need to specify private subnets accordingly.

Also, put a check on the public subnet has been set up. Each of the selected private subnets has outbound connectivity enabled.

Availability zones

In the Remote Desktop Gateway access, select option Custom IP from the drop-down and specify a CIDR(Classless Inter-Domain Routing) block.

Remote Desktop Gateway access

AWS managed Microsoft Active directory

We must join EC2 instances in an active directory domain for SQL Server always on. You get two options here.

  • Connect to existing active directory
  • Create and connect to new AWS managed Microsoft AD

If you have used the VPC option Create new Virtual Private Cloud (VPC), then we must use the second option – Create and connect to new AWS managed Microsoft AD.

AWS managed Microsoft Active directory

If you select the option to create a new AWS managed Microsoft AD, specify the password for the default administrator user and required a fully qualified DNS name for the active directory.

New AWS managed Microsoft AD for SQL Server Always On

We require a service account to run the SQL Services. In the new AWS managed Active directory, select to create a new SQL Server Service account.

Enter the required username for the service account. This account gets local admin and sysadmin fixed server role on each AG nodes. You should securely store the account credentials.

Service account

SQL Server install type

In this part, we can choose from the below options.

  • Use Amazon machine image (AMI): it includes a license for both Windows and SQL Server
  • Custom AMI: AWS also gives the option to bring your SQL license and build a custom AMI

We do not have any custom AMI, so select the option – Use license included AMI and select the AMI from the drop-down.

SQL Server install type

AWS launch wizard provides flexibility to configure additional settings and customize server names, cluster name, availability group name.

  • Nodes. You can specify primary and secondary SQL node names. You can specify up to 5 secondary SQL nodes

    AG nodes

  • Witness node: We should add a quorum witness to improve fault tolerance for failover clusters. It is an optional requirement. Let’s add a file share quorum witness
  • Additionally, specify the database, availability group, SQL Listener, and cluster name. If you do not specify any resource name, AWS automatically assigns the names as per their standard

Witness node

Step 3: Define the infrastructure requirement for SQL Server Always On resources

Storage and Compute

In the third step, we need to define the infrastructure requirements for your AWS resources. We can either use the AWS recommended resources or define the resources as per our requirement.

In the AWS recommended resources as well, you can customize the infrastructure.

  • Instance cores:- Select the number of CPU cores for EC2 instances. By default, it uses 4 cores
  • Network performance:-You can choose preferred network speed in Gbps. Its default value is 10Gbps
  • Memory(GB): It is the amount of RAM for the EC2 instances. It uses 4 GB by default
  • Storage and performance: We can select the storage type for the data, logs, and TempDB volumes. By default, it uses solid-state disk ( SSD) for high performance
  • SQL Server throughput: You should have estimates for the approximate throughput for your SQL instance. Select the required throughput from the drop-down

Storage and Compute

Drive letters and Volume Size

AWS launch wizard gives recommendations for the drive letters and the volume size for root drive, log, data, and backup drive. You cannot change the drive letter for the root drive, but you can make changes for other drive letters and their sizes.

Drive letters and Volume Size for SQL Server Always On instances

View Estimated price for AWS resources

If you scroll down on this page, you get an estimate for all resources AWS launch wizard deployment. It gives estimation for the resources monthly; however, the actual bill would depend upon the usage(pay as you go model).

View Estimated price for AWS resources

Once you reviewed the AWS resource estimation, click Next, and review the overall configurations. You can go back in case any changes are required.

Review Configuration

Before deployment, you required to accept that AWS will deploy the resources on your behalf. It again shows the estimated monthly cost for your configuration.

Acknowledgement

Click on Deploy, and it starts resource deployment.

Deployment progress

You can click on the deployment name and see progress messages.

Detailed status

It takes approx.2 hours for AWS to deploy all resources. At a high-level, it deploys the following resources:

  • Create an AWS managed active directory
  • Deploys AWS EC2 instances with specified storage type, size and join them in the AD domain
  • Configure failover clustering
  • Install SQL Server on each EC2 instance
  • Enables SQL Server always on feature
  • Add secondary replica and Availability group
  • Configure SQL listener

Conclusion

AWS Launch Wizard is a promising solution to deploy SQL Server always on solutions easily in the AWS cloud. It deploys all required resources using a quick and user-friendly interface sequentially. You do not require to deploy resources individually and configure AG replica. It is quick, easy to use, single interface for all deployments.

Rajendra Gupta
49 Views