Simplify SQL Server Always On deployments with AWS Launch Wizard and Amazon FSx

Configuring and deploying your high availability Microsoft SQL Server workloads in the cloud often requires identifying and provisioning individual resources to get started, which can bring added complexity and cost.

AWS Launch Wizard for SQL Server enables quick and easy deployment of SQL Server solutions on AWS, supporting Always On Availability Groups and single node deployments.

To help you more easily leverage shared storage for your high availability SQL Server deployments, AWS recently announced AWS Launch Wizard support for Always On Failover Cluster Instances (FCI), using Amazon FSx for Windows File Server as the shared storage solution.

In this blog, we cover how SQL Server FCI can help you save costs, why Amazon FSx is a preferred shared storage solution for deploying FCI, and how AWS Launch Wizard can help you provision and configure your FCI solution within hours through a guided, console-based wizard.

Why use Launch Wizard for Always On FCI deployments?

AWS Launch Wizard is a simple, intuitive, and free to use wizard-based experience that enables quick and easy deployment of high availability SQL solutions on AWS. The wizard walks you through an end-to-end deployment experience of Always On Failover Cluster Instances using prescriptive guidance. By answering a few high-level questions about the application, such as required performance characteristics, the wizard will then take care of identifying, provisioning, and configuring matching AWS resources such as Amazon Elastic Compute Cloud (Amazon EC2) instances, Amazon FSx file systems, and an Amazon Virtual Private Cloud (Amazon VPC). Based on your selections, the wizard presents you with a dynamically generated estimated cost of deployment – as you modify your resource selections, you can see an updated cost assessment to help you match your budget. After your inputs are complete, Launch Wizard provisions these resources and configures them to create a fully functioning production-ready SQL Server Always On deployment in just a few hours. The created resources are tagged, making it easy to identity and work with them, and the wizard also creates AWS CloudFormation templates, providing you with a baseline for repeatable and consistent application deployments.

Why use Always On FCI?

Microsoft SQL Server provides two deployment options of its Always On solution for business continuity use cases like high availability and disaster recovery (DR): Always On Failover Cluster Instances (FCI) and Always On Availability Groups (AG). FCI is generally preferable over AG for SQL Server high availability deployments when the following are priority concerns for your use case:

Performance: FCI provides more efficient use of SQL Server resources as it involves replication at the block-level (storage layer) instead of database-level replication. FCI also reduces the CPU/memory needed to handle replication, freeing up resources for primary compute jobs.

License cost efficiency: You need the Enterprise Edition license of SQL Server to run AGs, whereas you only need the Standard Edition license to run FCIs. This is typically in the range of 50–60% less expensive than the Enterprise Edition. Although you can run a basic version of AGs on Standard Edition starting from SQL Server 2016, it limits support to only one database per AG. This can become a challenge when dealing with applications such as SharePoint that require multiple databases.

Simplified administration: With FCI, the entire instance is protected – if the primary node becomes unavailable, the entire instance is moved to the standby node. This takes care of the SQL Server logins, SQL Server Agent jobs, certificates, etc. that are stored in the system databases, which are physically stored in shared storage. With AG, on the other hand, only the databases in the group are protected, and system databases cannot be added to an AG – only user databases are allowed. It is the database administrator’s responsibility to replicate changes to system objects on all AG replicas. This leaves the possibility of human error causing the database to become inaccessible to the application.

In contrast, there are scenarios where AG is preferable over FCI, such as if you must configure the additional nodes as read replicas to enable higher read performance scalability.

Why use Amazon FSx for Windows File Server as the shared storage solution?

With Amazon FSx for Windows File Server (Amazon FSx), you get fully managed shared file storage that automatically replicates the storage synchronously across two AWS Availability Zones. Moreover, Amazon FSx provides high availability with automatic failure detection, failover, and failback. The service also fully supports the SMB Continuous Availability (CA) feature required for SQL Server Always On FCI deployments.

If you use SQL Server FCI with shared storage using a third-party storage replication software solution, you must purchase a license for the storage replication solution, and then deploy, administer, and maintain the shared storage solution yourself. With Amazon FSx, you can use a fully managed shared storage solution to simplify your SQL Server FCI deployment.

If you are running your SQL Server Always On deployment on-premises, you are likely using a combination of FCI and AG. FCI to provide high availability within your primary data center site (because shared storage cannot typically span multiple data centers), and AG to provide a DR solution across sites. AWS’s Availability Zone architecture and Amazon FSx’s support for highly available shared storage deployed across multiple Availability Zones now makes it possible for you to eliminate the need for separate high availability and DR solutions. This reduces costs as well and simplifies deployment complexities.

Finally, another way you can use Amazon FSx to simplify your SQL Server FCI deployment is for the Windows file share witness for your SQL cluster. A Windows file share witness is a file share that is available to all nodes in a high availability cluster. The job of the witness is to provide an additional quorum vote when necessary to ensure that a cluster continues to run if there is a site outage. With Amazon FSx, you can easily and cost-effectively use a Single-AZ file system that is fully managed to host the file share witness.

Getting started with AWS Launch Wizard

First, you go to Launch Wizard console and select Choose Application.

Next, choose SQL Server and Create Deployment to start the wizard.

In the SQL Server Launch Wizard, Step 1 displays information regarding the AWS Identity and Access Management (IAM) role used for the deployment. Launch Wizard creates this role on your behalf in your account so that Launch Wizard can provision the resources across AWS services for my SQL Server deployment. If part of an organization, your account must have permissions to create or access AWS resources such as instances, an Amazon VPC, etc.

Next, configure the application settings. Windows and High Availability deployment are selected by default. You can select Always On Failover Cluster Instances as the high availability deployment option. After this, you will see four short sections in this step: General, Connectivity, Active Directory, and SQL Server.

In the General section, enter a deployment name and specify a Simple Notification Service (SNS) topic ARN (Amazon Resource Name) to receive notifications and alerts about your application deployment status. You also have the option to select enable rollback on failed deployment, which would immediately delete the resources created by the Launch Wizard deployment. This is useful if the deployment fails due to issues such as lack of permissions or incorrect Active Directory credentials. This option is disabled by default so that you have an opportunity to debug resources if a deployment fails. After diagnosing the issue, when you delete the failed Launch Wizard deployment, then the related resources will be deleted.

In the Connectivity section, you provide inputs for the networking infrastructure of your application resources. You can choose an existing Key pair name or create a new one. If you create a new key pair, hit the refresh button on the right to display the newly create key pair in the drop-down menu. The key pair will be used if you want to connect to the instances using RDP, or obtain the administrator password. Tenancy model gives you the option to use a shared hardware instance or a dedicated host (additional charges apply for using a dedicated host as the physical server is reserved for you and can help address corporate compliance requirements). You have the option to select an existing Virtual Private Cloud (VPC) or create a new VPC.

After selecting the VPC, you need to select the Availability Zones (AZ) and private subnets in each Availability Zone, and select the Outbound Connectivity check box to confirm that public connectivity is established for the private subnets. If creating a new VPC, Launch Wizard will create and configure the subnets for you.

Next, fill out the Active Directory information. If you have instructed Launch Wizard to create a new VPC for this deployment, you will have only the option to create and connect to a new AWS Managed Microsoft Active Directory (AD). If you have used an existing VPC, you can connect to existing AWS Managed AD or on-premises AD by providing the relevant credentials.

Next, provide your SQL Server account details, and the SQL Server Install type. For install type, you can choose a License-included AMI (Amazon Machine Image) or bring your own license (BYOL) through a custom AMI.

In Additional SQL Server settings – optional you can provide SQL node names, cluster name, and SQL server instance name. If you leave these fields blank, then Launch Wizard will assign unique names for you.

With this, you have configured the application settings. Selecting Next takes you to Step 3: Define infrastructure requirements, where you can select your Storage and compute infrastructure requirements. You have two options. By default, you will see Based on infrastructure in which Launch Wizard recommends AWS infrastructure based on your needs. Here, you can input your network, memory, and processor requirements for your instance, and Launch Wizard will recommend an instance type that can be seen under Recommended resources. Similarly, you can input your storage performance and capacity requirements, and Launch Wizard will recommend the Throughput and Amazon FSx Storage for your needs. Note that your Amazon FSx storage capacity is a function of your peak desired IOPS and your desired storage capacity, and therefore it may be higher than the Allocated storage space selected. As you modify your inputs, the recommended resources will get updated.

Alternately, you can select your instance type and Amazon FSx throughput and storage capacity manually by selecting Based on instance type.

You can also see the estimated monthly costs to deploy the resources, which also get updated as you modify your inputs, and can help you select what’s best for your needs.

The final step is Step 4: Review and Deploy, which takes you to the Summary page shown in the following screenshot. Once you hit Deploy, the Launch Wizard begins to spin up the resources and configure your application.

Typically, your deployment will be completed within two hours, and you can go into the Amazon EC2 console to access your EC2 instances, AWS Systems Manager Application Manager to manage your application, and Amazon FSx to view your file system. In AWS CloudFormation, you can view the stack and template generated by Launch Wizard based on your selections, and this code can be re-used for future deployments or for integration with your CloudFormation-supported CI/CD tools.

For verification, you can find the Launch Wizard-created cluster in Failover Cluster Manager on the EC2 instance.

You can create SQL Server databases and check if the data files are placed in the Amazon FSx file share by using the DNS name of the file share and navigation using the path: \\<fsxdnsname>\<sqlshare>.

In case of an instance (SQL node) failure, you will find that the automatic failover to the secondary node begins. The SQL Server goes temporarily offline, and soon comes back online with the secondary node up and running.

The entire instance is moved to the standby node, and this takes care of the SQL Server logins, SQL Server Agent jobs, certificates, etc. that are stored in the system databases, which are physically stored in shared storage provided by Amazon FSx. This simplifies the number of steps the database administrator may otherwise have to take to get the database back up and running.

Cleaning up

While there is no cost to use Launch Wizard, you will be charged for resources created as part of the application deployment. If you no longer need the application running, you can delete the deployment in Launch Wizard, which will delete all the AWS resources created as part of the deployment.

Conclusion

In this blog post, we demonstrated how you can utilize the AWS Launch Wizard for SQL Server to easily create production-ready SQL Server FCI deployments using Amazon FSx. We walked through why FCI using Amazon FSx might be the right solution for your needs, and the step-by-step process of deploying SQL Server FCI using Launch Wizard.

AWS Launch Wizard and Amazon FSx can help you reduce complexity and cost of deploying and running a SQL Server high availability solution. AWS Launch Wizard for SQL Server FCI using Amazon FSx simplifies provisioning and configuration, presents you with a dynamically generated estimated cost of deployment, creates resources for you within hours, and generates AWS CloudFormation templates for repeatable deployments.

Additionally, if you run both FCI and AG on-premises to achieve high availability and disaster recovery, you can move your deployment to a SQL FCI deployment on AWS. This eliminates separate HA and DR solutions, further reducing costs as well as simplifying deployment complexities. The fully managed shared storage solution, provided by Amazon FSx, simplifies administration and eliminates the need for third-party storage replication. Learn more about simplifying SQL Server FCI with Amazon FSx for Windows File Server in this blog post.

Thank you for reading, and please do not hesitate to leave a comment with any questions or feedback.