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
-
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
In the welcome page, we get deployments options – MS SQL Server and SAP.
Click on the Create Deployment.
Step 1: Choose application
Select Microsoft SQL Server Always On.
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.
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.
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
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.
It takes you to the EC2 key pairs section. You can see the existing key pairs in your account. Click on Create key pair.
Specify a key pair name and file format. You should save the generated key pairs to a secure and safe location.
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.
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.
In the Remote Desktop Gateway access, select option Custom IP from the drop-down and specify a CIDR(Classless Inter-Domain Routing) block.
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.
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.
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.
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.
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
- 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
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
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.
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).
Once you reviewed the AWS resource estimation, click Next, and review the overall configurations. You can go back in case any changes are required.
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.
Click on Deploy, and it starts resource deployment.
You can click on the deployment name and see progress messages.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023