Deploying highly-available SQL Server on Amazon EC2 Dedicated Hosts

Want to bring your eligible SQL Server licenses to use on AWS? If your organization is planning data center evacuation, and looking to extend the life of existing investments in Microsoft SQL Server and Windows Server licenses, Amazon Elastic Compute Cloud (Amazon EC2) and AWS License Manager can help.

Do you also want to setup high availability for your SQL Server on Amazon EC2 Dedicated Hosts?

In this post, we’ll show you how Amazon EC2 Dedicated Hosts and AWS License Manager can be used to design a highly-available Microsoft SQL Server architecture while saving costs by bringing your own licenses. You will understand how License Manager integrates with Dedicated Hosts to reduce management overhead. Furthermore, you’ll understand how host resource groups simplify the management of Dedicated Hosts while allowing you to meet the high availability requirements for the SQL Server.

Solution overview

When designing high availability for Dedicated Hosts within a single AWS Region, make sure that you’ve allocated Dedicated Hosts in a minimum of two Availability Zones as shown in the following diagram.

AWS License Manager is used for creating a license configuration that will track Windows and SQL Server licensing usage. EC2 Image Builder is used to build the AMI, and as part of the deployment, it associates the Amazon Machine Image (AMI) to the license configuration. Then, within License Manager, a host resource group is configured and associated with the license configuration. This will automatically handle the provisioning, releasing, and recovering of the underlying Dedicated Hosts.  You will need to bring your Windows Server license in Bring your own licensing Model or use Windows License included instance on Dedicated Hosts.

Figure 1 High-level architecture showing SQL Server cluster nodes replicating synchronously between Availability Zones.

Solution workflow

This solution workflow consists of the following steps:

1. Create customer managed license configuration
2. Associate a license configuration with an AMI through EC2 image builder
3. Create a host resource group and associate the license configuration
4. Launch your SQL Server using Host resources groups
5. Setup SQL Server high availability using Always On availability group or SQL Server FCI with Amazon FSx.

Prerequisites

To deploy this solution, the following is required:

• SQL Server Enterprise Edition/Standard Edition core-based or processor-based licenses
• Access to AWS Management Console
• ·License Manager
• Amazon Dedicated Hosts
• Amazon FSx for Windows

1.Create License Manager configuration

License Manager emulates licensing terms as rules. With License configuration, you’ll create licensing rules based on the licensing terms in your enterprise agreements. The rules shown in the following diagram are an example scenario of license configuration to bring four Dedicated Hosts of r5 Instance family that you can license at the core level or vCPU level with the license type. While creating license configurations, work closely with your organization’s compliance team to review your enterprise agreements.

Figure 2 Creating AWS License Manager configuration rules with parameter and rules to track license usage.

License Configuration settings

• License Type : Core
• License count : 192
• License count hard limit : True

Rule type:

• License affinity to host (in days) : 90 days
• Tenancy : Dedicated Host

To learn more about parameters and rules, see this document.

2. Associate AMI to license configurations

Now that the license configurations are set up, you must associate the AMI. This can either be done directly through the license configuration, or as part of EC2 Image Builder, which simplifies the process for building and maintaining secure images.

Prepare AMI for the BYOL use case

For BYOL, you must import and license your own media. You can use EC2 Image Builder or EC2 VM Import/Export to import your VM images.

Figure 3 Creating pipeline to build Windows AMI’s for running SQL Server Cluster nodes.

If you already build your AMIs with EC2 Image Builder, and have an existing pipeline, then follow these steps.

Associate the AMI using EC2 Image Builder

1. Open the EC2 Image Builder console.
2. Under Distribution Settings, open the settings of the target AMI pipeline (the one that you want to associate with EC2 Image builder), and select edit.
3. Under “Associate License Configurations,” add the two license configurations that you created earlier.
4. OR

Associate the AMI directly using License Manager

1. In the License Manager console, open your license configuration for Windows Server.
2. Select “Associate AMI” under the “Associated AMI” tab.

Figure 4 Associating license configuration rules with Windows AMI to enforce licensing terms and usage.

3. Create Dedicated Host resource group

With the host resource group, host management tasks such as allocating, releasing, and recovery is taken care of for you by the service.

Dedicated Host resource group is a logical collection of Dedicated Hosts that you manage as a single entity. Host resource group can be created with specific preferences, such as instance family and allowed license configuration.

In a practical sense, this could represent a logical group of Dedicated Hosts of a similar instance family, i.e., a Dedicated Hosts group to run SQL Workload on r5 instances only. You could also create a host resource group with more relaxed preferences, such as including multiple instance family, and associate more than one license configuration. This simplifies instance placement on the Dedicated Hosts by automatically allocating the right type of Dedicated Hosts for your workload.

For running the SQL cluster, you should configure the host resource group with precise settings that allow only the SQL Server license configuration rule to stay compliant, and pick r5 as your preferred Instance family.

Figure 5 Selecting Host management preferences within the Host resource group to manage Dedicated Hosts.

Dedicated Host Management settings

Auto-allocate : True

Auto-release : True

Auto-recover : True

Allowed Instance families : r5

To learn more about host resource group, see this document.

Implement high availability for Dedicated Hosts environment

Apart from leveraging Multi-AZ architecture, Amazon EC2 Dedicated Hosts offer a host recovery feature for supported instance families. Host recovery uses host-level health checks to assess Dedicated Host availability and detect underlying system failures. When a system failure is detected on your Dedicated Host, host recovery is initiated, and Amazon EC2 automatically moves the instances to the new host.

Note that host recovery is not supported on instances with instance storage, such as r5d and z1d, where manual recovery is needed for replacement of the Dedicated Host.

Figure 6 High-level architecture showing Dedicated Hosts using two availability zones.

Implement high availability for Microsoft SQL Server environment

Microsoft SQL Server provides two deployment options of its Always On solution for business continuity use cases, such as high availability and disaster recovery: Always On Failover Cluster Instances (FCI) and Always On Availability Groups (AG). Both of these deployment options use Windows Server Failover Cluster (WSFC) technology to provide a cluster of independent nodes that work together to deliver high availability, but there are important differences:

• AGs represent one or more user databases that fail over together across multiple replicas hosted in the cluster to provide database-level high availability. An AG consists of a primary replica and one or more secondary replicas that are maintained through SQL Server log-based data movement for data protection without the need for shared storage. Each replica is hosted by an instance of SQL Server on a different node of the WSFC, and each replica has its own local storage independent of the AG.

Figure 7 High-level architecture showing SQL Server Always on cluster nodes replicating synchronously between Availability Zones.

• You can create a Primary SQL Server Amazon EC2 instance on Dedicated Hosts in availability zone 1 on r5 Dedicated hosts, and a secondary SQL Server Amazon EC2 instance can be deployed on Dedicated Hosts in availability zone 2. You can setup an Always On availability group between two instances of SQL Server.
• Check here for how to setup SQL Server Always On availability group.
• And the differences for the Always On FCIs with FSx are as follows:
• FCIs present a single SQL Server instance that’s installed across nodes in a WSFC to provide high availability for the entire installation of SQL Server. This means that everything inside of the SQL Server instance moves to another node in the cluster should the underlying node encounter a problem. Among other things, system databases, SQL Server logins, SQL Server Agent jobs, and certificates get moved to another node. FCIs require some form of shared storage – either shared block storage (SAN) or shared file storage (accessed via SMB).
• Amazon FSx provides you with fully managed shared file storage that automatically replicates the storage synchronously across two 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 to support SQL Server Always On FCI deployments.

Figure 8 High-level architecture showing SQL Server Failover Cluster Instance nodes between Availability Zones.

• You can create a Primary SQL Server Amazon EC2 instance on Dedicated Hosts in Availability Zone 1 on r5 Dedicated hosts, and secondary SQL Server Amazon EC2 instance can be deployed on Dedicated Hosts in Availability Zone 2. You can setup Windows failover cluster and SQL Server Failover cluster using FSx between two instances of SQL Server.
• Check how to setup SQL Server FCI with FSx implementation.

Clean up

When you’re finished, you can terminate the Amazon EC2 instances that you launched earlier. This will automatically release the Dedicated Hosts. There is no additional charge for using License Manager. You pay only for the resources that you create to run your applications, such as Amazon EC2 instances.

Conclusion

In this post, you have learned how Dedicated Hosts can be used with License Manager to save costs by utilizing your existing licensing and deploying a highly-available SQL Server environment. We showed how to setup SQL Server high availability using Always On availability group and SQL Server FCI deployments using Amazon FSx. We also showed how you could use license configurations, host resource groups, and EC2 image builder to reduce management overhead and easily track software license usage. This significantly improves your overall experience of BYOL Windows, and SQL Server licenses to AWS cloud that stay compliant.

Check the best practices document to learn more about the most effective ways for working with SQL Server on Amazon EC2.

Authors: