Quickly Deploy SQL Server on Linux using AWS Launch Wizard

By:   |   Updated: 2022-10-27   |   Comments   |   Related: > Amazon AWS


Problem

Most SQL Server installations have been typically done on Windows. However, there could be significant cost savings if SQL Server is run on Linux. The issue with deploying SQL Server on Linux is that the transition may be difficult from SQL Server on Windows to SQL Server on Linux. Also, in the case of a high-availability configuration, it increases the knowledge needed since Linux uses Pacemaker instead of a Windows Cluster.

Luckily, the AWS web console tool can quickly deploy SQL Server on an EC2 instance and provision infrastructure resources based on your requirements. This can be done for either Windows or Linux, as well as build standalone or highly available solutions for SQL Server. This tip will explore the AWS Launch Wizard for SQL Server deployments and focus on how this can be accomplished for Linux.

Solution

AWS Launch Wizard is a fantastic service that can quickly help size, configure, and deploy a Microsoft SQL Server on Amazon EC2 - all with minimal fuss. It is easy to use and supports single-instance SQL Server deployments and high availability (HA) setups on Amazon EC2.

The Launch Wizard provisions and configures the selected resources to create a fully functioning, production-ready SQL Server including CPU cores, network performance, memory, storage, and performance. It also gives an estimated cost for resources. You can modify the resource configurations and instantly view the updated cost assessment. AWS Launch Wizard also creates AWS CloudFormation templates which can be customized for subsequent implementations.

In this article, we will deploy a SQL Server on Linux (standalone) on Ubuntu (Linux) using AWS Launch Wizard.

AWS Launch wizard diagram

Image reference: AWS Launch Wizard

Apart from SQL Server, the Launch Wizard also allows you to deploy:

  • Active Directory
  • Amazon Elastic Kubernetes Service
  • Exchange Server
  • Internet Information Services
  • Remote Desktop Gateway

Deployment Models of AWS Launch Wizard for SQL Server

AWS Launch Wizard supports SQL Server deployment on EC2 for Windows and Ubuntu and Red Hat Linux operating systems. The following list gives supported SQL Server versions, operating systems, license support, and storage requirements on Windows and Linux deployment models.

Windows Deployment Model

Resource Details
SQL Server Version SQL Server 2016, 2017, 2019
Operating System Windows Server 2012 R2, 2016, and 2019
Deployment Models * Single Instance
* High availability
    - Always On Availability Group: Availability groups with primary and secondary replicas for database level protection
    - Always On Failover Cluster Instances: Failover Cluster Instances (FCI) for instance-level protection
Edition Standard, Enterprise
License Support * Amazon Machine Images (AMIs) with license included
* Custom AMI for bringing your own SQL licenses model
Cluster Windows Server Failover Cluster (WSFC)
Storage You can choose the capacity and performance based on your deployment requirements in the Launch Wizard.

These are the default general purpose SSD volume disks:
1. 100 GiB for the operating system (C:)
2. 500 GiB for the SQL Server database files (D:)
3. 500 GiB for the SQL Server log files (E:)
4. 500 GiB for the SQL Server TempDB and backup files (F:)

Performance:
* A baseline of 3 IOPS/GiB
* A total of 1,500 IOPS per volume.

NOTE – The volumes are customizable via BASH code and the CloudFormation code.
VPC and IP Addresses Requires the following IP addresses for Windows failover clusters and always on availability group scenarios.
* IP address as the primary IP address for the instance
* Second IP address as the WSFC IP resource
* IP address to host the Always on Availability Group listener

If you use defaults to deploy a new VPC for SQL Server Always on Availability Groups on the failover cluster, it assigns 10.0.0.0/20, 10.0.16.0/20, and 10.0.32.0/20 as CIDR blocks for the private subnets.

Linux Deployment Model

Resource Details
SQL Server Version SQL Server on Linux 2017, 2019
Operating System Ubuntu 18.04 Red Hat Enterprise Linux 8.4
Deployment Models Ubuntu:
* High availability: SQL Server Always On Availability Groups application across multiple availability zones
* Standalone

RedHat Enterprise Linux:
* High availability: SQL Server Always On Availability Groups application across multiple availability zones
Edition Standard, Enterprise, and Express for the Configuration Only Quorum node
License Support * Amazon Machine Images (AMIs) with license included
* Custom AMI – BYOL (Bring Your Own License) model
Cluster Pacemaker
Storage You can choose capacity and performance based on your deployment requirements in the Launch Wizard.

Default disks:
1. GP2 volume (100 GiB) for Temporary Data
2. GP2 volume (500 GiB) to host the SQL Server database files
3. GP2 volume (500 GiB) for the SQL Server backup

NOTE – The volumes are customizable via BASH code and the CloudFormation code. The Ubuntu solution was first developed by AWS, with a much improved solution with Red Hat afterward.
VPC and IP Addresses A Linux Pacemaker cluster requires at least one public subnet and three private subnets associated with the private route table. The Private subnet should have outbound connectivity to services such as S3 bucket, CFN, SSM, and logs. It requires one floating IP address in the VPC for the availability group listener. This IP address should not be from the subnet CIDR range. The Launch wizard also configures a route to the primary node network interface. The interface is for the internal VPC network traffic on the primary node.

Deploy a Standalone SQL Server on Linux EC2 Instance Using AWS Launch Wizard

Using the AWS Launch Wizard console, let's deploy a standalone SQL Server instance on a Linux EC2 instance. Log in to the AWS portal and search for Launch Wizard.

Launch wizard search results

Clicking on the Launch Wizard link takes you to the landing page of the AWS Launch Wizard. You can deploy various popular enterprise applications such as SQL Server and Active Directory.

AWS Launch Wizard landing page

Click on Choose application and select the application from the drop-down list. Choose SQL Server and click on Create deployment.

AWS Launch Wizard-Choose application prompt

The configuration page opens to create SQL Server deployments.

Step 1: Review Permissions

The AWS Launch Wizard requires the Identity and Access Management (IAM) role for deploying resources on your behalf. It automatically creates a role name, AmazonEC2RoleForLaunchWizard, as shown below. To learn more about the IAM role, click on the documentation link from the Role policy section.

AWS Launch Wizard permissions

Step 2: Configure Application Settings

The Configure application settings interface requires the following input:

Operating system:

Choose the operating system – Windows, Ubuntu, or RedHat Enterprise Linux.

AWS Launch Wiard-application settings, operating system

Deployment model:

As stated earlier, you can deploy a Standalone or HA SQL Server with multiple nodes. You can choose a Single instance or HA model based on the operating system type. For example, select a Single instance for this demonstration.

AWS Launch Wiard-application settings, deployment model

General configuration:

  • Deployment name: You can specify a unique name for the deployment.
  • Simple Notification Service (SNS): AWS uses the SNS topic for notifications and alerts. It is an optional field.
  • Enable rollback on failed deployment: AWS Launch Wizard does not delete the resources in case of failure so that you can investigate the errors. Use the option, Enable rollback on failed deployment, to instruct AWS to delete resources immediately in case of deployment failure.
AWS Launch Wiard-application settings, general settings

Connectivity:

The Connectivity field defines key-pair and the Virtual Private Cloud (VPC) network configuration.

  • EC2 key pair: An EC2 key pair is required to connect with the EC2 instance. If you have an existing key pair, select it from the drop-down or create a new key pair. Click Create Key Pair to configure a new key pair for EC2 instance connectivity.
AWS Launch Wizard key pairs
AWS Launch Wizard create key pair
  • Download and save the private key file in a safe place.
AWS Launch Wizard key pairs
save private key file
  • Virtual Private Cloud (VPC): AWS Launch Wizard allows you to create a new VPC or choose an existing VPC. Let's create a new VPC, as shown below.
AWS Launch Wizard private cloud

SQL Server configuration:

This section is specific to a SQL Server configuration.

  • Username: Launch Wizard uses the default username SA as the system administrator account.
  • Password: Enter the SA login credential.
  • Amazon Machine Image (AMI): Choose the SQL Server version and edition from the available license including AMI. You can use custom AMI for your SQL Server license (BYOL model).
AWS Launch Wizard SQL Server configuration

Step 3: Define Infrastructure Requirements

The AWS Launch Wizard recommends the infrastructure resources as per defined requirements. By default, it uses the following default values:

  • Number of instance cores: 4
  • Network performance: Up to 10 Gibs
  • Memory (GB): 4
  • Type of Storage Drive: SSD
  • SQL Server throughput: Up to 250 MiB/s

Based on the requirements, it shows the recommended resources. For example, the default requirements suggest the following:

  • Instance type: c6a.xlarge
  • Volume type: gp2
  • Volume size:
  • Temporary database: 100 GB
  • Data: 500 GB
  • Backup: 500GB

You can define the infrastructure requirement based on Infrastructure suggestion or Instance type.

AWS Launch Wizard Infrastructure Requirements

AWS Launch Wizard Infrastructure Requirements
volume sizes

Estimated on-demand cost to deploy additional resources

You can review the estimated resource deployment cost using the AWS Launch Wizard. Note: The AWS Launch Wizard is free, and the price is associated with the deployed resources.

AWS Launch Wizard Infrastructure Requirements
Estimated on-demand cost

Step 4: Review and Deploy

On the Review and Deploy page, you can assess the SQL Server deployment configurations. Click on Deploy at the bottom of the page if you agree with the configuration and terms specified.

AWS Launch Wizard Review and Deploy1
AWS Launch Wizard Review and Deploy2

This will start the deployment, and you can access the SQL Server in the AWS EC2 instance post-deployment.

AWS Deployment wizard

Click on the application name – mssqldemo – to get details of individual components.

AWS deployment details

Refresh the deployment to see status changes. As a deployment phase finishes, it will be marked as Completed.

AWS deployment details refresh

Cost Comparison on Windows and Linux SQL Server Deployments

As mentioned earlier, deploying on Linux can be much cheaper than on Windows. The following table shows the cost comparison between SQL Server on Windows vs. Linux for both standalone and cluster configurations. SQL Server on Linux is cheaper than Windows due to licensing costs. However, prices may vary depending on region and resources.

OS Edition Standalone Cluster
Ubuntu 18.04 SQL Server 2019 Standard $598.28 $1424.12
Windows Server 2019 SQL Server 2019 Standard $1053.32 $1598.72

You can view the deployment cost in the AWS Launch Wizard or use the AWS pricing calculator to estimate the cost of SQL Server deployments on Windows and Linux.

View AWS CloudFormation Templates

The AWS Launch Wizard creates CloudFormation templates you can reuse for future deployments. In the AWS services, navigate to CloudFormation and view the stacks. To get details of a deployed SQL Server instance, click on Resources in the stack. You get a list of deployed resources such as EC2 instance, security group, and network interfaces.

AWS CloudFormation Templates Stacks

Click on the EC2 instance and check the instance state, platform details, AMI name, Instance ID, Hostname, IP addresses, and VPC ID.

AWS CloudFormation Templates Instance details

Clean Up Resources Deployed Using AWS Launch Wizard

Let's assume you deployed SQL Server resources using the AWS Launch Wizard for learning purposes and would like to remove what you created. Since the Launch Wizard creates multiple resources such as VPC, Storage, EC2 instance, and security group, it isn't easy to look for each resource and delete them. You want to delete them once you explore the deployment using the Launch Wizard.

You can remove all resources deployed using the AWS Launch Wizard with a single click. Go to the AWS CloudFormation stack and click on delete. As we saw earlier, Launch Wizard created the nested stacks. It is always advisable to delete resources through the root stack so that it can delete all resources with their dependency order.

Choose the option – Go to the root stack (recommended) and enter the phrase 'delete' in the confirm deletion box.

Clean up launch wizard
delete nested stack

Click Delete stack. This deletes the resources deployed using the AWS Launch Wizard.

Clean up launch wizard

Benefits of Using the Launch Wizard

The AWS Launch Wizard has the following benefits for deploying SQL Server resources:

  1. Simplified resource deployment: The AWS Launch Wizard guides you in deploying resources for standalone and HA configurations. It saves time in provisioning individual resources for the key applications.
  2. Automated resource recommendations: The Launch Wizard recommends AWS resources such as instance type, memory, and disks based on the defined requirements. You can review the resources and their cost from the Launch Wizard and make configuration changes if required.
  3. Reusable CloudFormation templates: AWS Launch Wizard generates AWS CloudFormation templates for future deployments. You can reuse the templates, customize them, and deploy the services without hassle.
  4. Adhere to AWS best practices: The AWS Launch Wizard adheres to AWS security, application best practices, performance efficiency, and cost savings.

Windows to Linux Replatforming Assistant for Microsoft SQL Server Databases

AWS incorporates a Replatforming Assistant for migrating SQL Server workloads from Windows to the Linux operating system. It is a scripting tool that works with any Windows Server virtual machine hosted in the cloud or on-premises. You can use the replatforming tool to check incompatibilities, export databases from Windows, and import them into Linux EC2. If you deploy a Windows-based SQL Server using the AWS Launch Wizard, you can use the Replatforming Assistant to convert it to Linux.

Refer to AWS documentation for more details on the replatforming tool.

Summary

The AWS Launch Wizard is a straightforward way to create a new AWS SQL Server instance on EC2. The Launch Wizard guides you step-by-step by selecting the database engine, edition, size, and infrastructure requirements options that meet your business needs. Using this wizard, you can launch a new instance in standalone or high availability mode with minimal configurations for either Windows or Linux.

Next Steps
MSSQLTips.com Product Spotlight sponsored by AWS makers of the AWS Launch Wizard.

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips


Article Last Updated: 2022-10-27

Comments For This Article