Easily Deploy SQL Server Failover Cluster Instance on AWS

By:   |   Updated: 2022-05-09   |   Comments (1)   |   Related: > Amazon AWS


Problem

For this article, I will share with you how you can launch your SQL Server Failover Cluster instance complete with shared storage on Amazon FSx via the AWS Launch Wizard.

Solution

SQL Server failover cluster instance configuration offers a highly available database engine platform for your relational database needs. This architecture is also cost effective as it can be done using SQL Server Standard Edition in an active-passive setting.

In a SQL Server Failover Cluster Instance configuration, shared storage is needed that can be mounted on each of the nodes participating in the cluster. For this purpose, an Amazon FSx will be deployed to store the mdf and ldf files for the system and user databases.

In my previous article I shared how you can easily launch and build a SQL Server Always On configuration on AWS. The configuration for Always On involves the use of SQL Server Enterprise Edition in an active-active setup.

Launch Wizard for SQL Server

AWS offers an easy way of deploying, configuring and setting up SQL Servers Always On either for Availability Groups or a Failover Cluster. This can all be done via the AWS Launch Wizard which we will cover in this article to setup SQL Servers running on Failover Cluster Instances.

If you need to use your own SQL Server license (BYOL), this is covered in the AWS Launch Wizard where you can select a prepared AMI of EC2 instance running OS and your licensed SQL Server Standard Edition. Make sure to prepare this AMI before going to the console for the AWS Launch Wizard.

AWS Launch Wizard is free and has no additional cost, but you will have to pay for the provisioned resources. To check availability in your region you can visit the AWS Launch Wizard page here.

To get started with AWS Launch Wizard to deploy SQL Servers Failover Cluster Instance, sign in to the AWS Console. If you do not have an AWS account yet, you create and activate an account here.

Prerequisites

  1. Coordinate with your Active Directory administrator to get the information for your organization's AD domain settings. You will need the DNS IP address, a domain user and password that has permission to create local objects in active directory. This is for the failover cluster configuration that will be configured.
  2. Select a SQL computer name that is compliant with your organization's naming convention for host names and service accounts. You will need this information in the AWS Launch Wizard.
  3. Create an SNS topic for notifications regarding the deployment.
  4. Create a AWS key pair under the AWS EC2 dashboard. You will need this to login to the SQL Server nodes via RDP.
  5. If you plan to use your own SQL Server Standard license, prepare an AMI (image) of an existing EC2 where you have pre-installed your licensed SQL Server.

It is also recommended that if you already have an existing AWS VPC setup to follow the tips in this article. However, creating a new AWS VPC is also included in the launch wizard.

So let's get started!

Create SQL Server FCI via AWS Launch Wizard

Sign in to your AWS console. From the AWS Management Console, navigate to AWS Launch Wizard by searching for it on the top search bar.

aws launch wizard

From the applications, choose the SQL Server and click 'Create Deployment'.

aws launch wizard choose application 

The AWS console will now navigate to the 'Create Deployment' wizard. On the left side menu, the 4 steps are enumerated.

Step 1 is to review the permission that AWS will create for its use in launching the AWS resources. Click 'Next' to go to Step 2.

aws launch wizard review permissions

Step 2 is to configure application settings. Here you will define the operating system, deployment model. Select the option for 'High Availability Deployment' and 'Always On Failover Cluster Instances'.

aws launch wizard configure application settings

In the 'General' section, you will need to key in the deployment name, select SNS to use for the email notification, check the box for 'Enable rollback on failed deployment' to make sure that clean-up of AWS resources is executed in case this deployment fails.

aws launch wizard configure application settings

In the 'Connectivity' section, you will need to select the AWS key pair to use for RDP connectivity, select the VPC and subnets where the EC2 instance will be deployed. You have to make sure that in your selected VPC, internet connectivity is allowed from the private subnets as the Microsoft packages for configuration are downloaded from the internet.

aws launch wizard configure application settings connectivity

For more information on VPC and connectivity to the internet from the private subnets, click here.

In the 'Active Directory' section, select the option to 'Create and connect to new AWS Managed Microsoft AD', enter your preferred password and domain name. If you are planning to deploy and connect to your organization Active Directory, you can select the 'Connect to existing Active Directory'.

aws launch wizard configure application settings active directory

In the 'SQL Server' section, select the option to 'Create new SQL Server service account', key in your preferred username and password. Note that this account will also be added to the active directory users you have selected in the 'Active Directory' section.

aws launch wizard configure application settings sql server

For the AMI to use, you can select from the 'License-included AMI' a SQL Server standard edition of your choice.

Step 3,, you will need to define the storage and compute for the EC2 instances that will be deployed. Here you also specify the FSx shared storage capacity. For the purpose of testing you can select the minimum size of 32GB.

aws launch wizard define infrastructure requirements

Amazon FSx will be used as the shared storage for the SQL Server system and user databases. For this deployment architecture, the shared storage will not need to swing from one node to another node during failover as this shared storage can be mounted or mapped to each of the participating nodes in the cluster at the same time.

Make sure to apply proper tags to this deployment so you can easily identify the resources for this deployment.

Step 4,, make sure to review the settings and the cost for this deployment. If you find all settings are correct, click 'Deploy' to start the deployment.

aws launch wizard review and deploy

Monitor the Deployment Process

The entire deployment of all the many AWS resources that need to be setup may take a couple of hours. This is all done for you, so you can take a break and get a cup coffee or check emails.

On the AWS console, you can navigate to the CloudFormation dashboard to monitor the deployment progress.

aws cloudformation monitor deployment process

You can verify that the new directory service is also created by navigating to the AWS Directory Service dashboard.

aws directory service dashboard

To check the deployment of Amazon FSx, navigate to the dashboard by searching for 'FSx' in the search bar.

aws fsx storage dashboard

Test and Verify the Failover Cluster Instance

When the deployment successfully completes, it's time to test and verify the configuration. Log in to the 2 servers via RDP with the key pair that you prepared in the prerequisite section.

For my setup, I verified the cluster setup in the Failover Cluster Manager console. I wanted to see that the SQL Server role is present and that the service is online for the active server and offline for the passive server, just how the failover cluster instances are expected to work.

failover cluster manager

I also verified by logging in to the passive server to verify that SQL Server is not running. Please see my second EC2 instance below.

From the second server, I can also connect to the SQL Server FCI.

sql server managemenet studio and configuration manager

The exciting thing about running SQL Server FCI on AWS, is that the failover event is much faster and smoother as there is no storage to shift and bring online from the current active server to the passive server. The data and log files are located in the Amazon FSx file share for Windows Servers.

Let's see how we should map the Amazon FSx shared storage on both nodes. In the AWS Console, navigate to the Amazon FSx dashboard by searching for Amazon FSx from the services in the menu.

amazon fsx shared storage

From the Amazon FSx dashboard, select the File Systems from the left pane menu and select the name of the file system that was created during the launch wizard. Then on 'Network & Security' tab, copy the DNS name to your clipboard for later reference. See my screenshot above.

Log in to both of the SQL Server nodes and open up Windows file explorer. Right click 'This PC' and select 'Map Network Drive'. This will open a new popup window similar to the screenshot below. Type in the 'Folder' name in the similar format as the example:

map network drive

Once that is completed, you will be able to locate where the system data and log files are and also where the user data and log files will be. I created a test database and have verified the location of these data and log files. Refer to the below 2 screenshots.

create new sql server database
sql server database files

When you have completed validating the deployed SQL Server FCI settings, you can delete your deployments in the AWS Launch Wizard dashboard to make sure you do not incur costs for this test environment we setup.

Conclusion

In this article, I guided you on how you can easily deploy SQL Server Failover Cluster Instances on AWS. Using AWS Launch Wizard offers a guided and well documented way of configuring highly available SQL Servers on AWS.

Deploying SQL Server FCI with an Amazon FSx file share for Windows lowers the cost of running your SQL Servers as you can opt to use SQL Server Standard Edition.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Carla Abanes Carla Abanes is a certified Microsoft SQL Server Database Administrator with extensive experience in data architecture and supporting mission critical databases in production environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-05-09

Comments For This Article




Wednesday, June 1, 2022 - 11:59:33 AM - Quang Phan Back To Top (90132)
Thank you for a very detail blog post. I wonder if there is any resource out there that shows how to migrate from on-prem SQL FCI into AWS ? The challenge is the on-prem database has the path to the SAN drive whereas in AWS you have to use the FSX DNS name :(














get free sql tips
agree to terms