Microsoft Workloads on AWS

Reduce Microsoft SQL Server High Availability costs running on Amazon EC2

AWS has helped hundreds of thousands of customers transition their SQL Server workloads to the cloud since 2008, longer than any other cloud provider. Running business-critical applications often requires Microsoft SQL Server databases to be highly available. Customers choosing to use SQL Server for their mission-critical workloads can achieve high availability by deploying SQL Server using Always On technology on EC2. SQL Server Always On can be deployed in two main configurations: Always On Failover Cluster Instances (FCI) and Always On Availability Groups (AGs).

In our previous post we showed how to use Optimize CPU for License Included feature for core licensing optimization. In this post, we show you how to reduce SQL Server high availability costs on Amazon Elastic Compute Cloud (Amazon EC2) by up to 40% using the new Amazon EC2 High Availability for SQL Server feature with SQL Server License Included (LI).

Prerequisites

Solution Overview

Solution Overview of SQL High Availability clusterFigure 1: Solution Overview

In a typical SQL Server HA deployment as shown in Figure 1, one EC2 instance acts as the active node (Primary Node) while the other acts as the passive/standby node (Secondary Node). This solution uses AWS Systems Manager (SSM) Run Command document AWSEC2-DetectSqlHaState to automatically detect the passive/active state of your SQL Server instances. The SSM Command document runs under the context of Local System User. This user is automatically mapped to the [NT AUTHORITY\SYSTEM] SQL login that has permissions to retrieve HA metadata.

If your security policies restrict or disable the [NT AUTHORITY\SYSTEM] account, you can alternatively configure AWS Secrets Manager to store SQL Server authentication credentials. Create a secret in AWS Secrets Manager with appropriate SQL Server permissions. The minimum permissions required for the SQL Server login to get the information needed to determine SQL HA metadata are :

      • VIEW DATABASE STATE : Grants the ability to view conditions about the current database via the database-level dynamic management views or functions.
      • VIEW SERVER STATE : Grants the ability to see server-level configuration information.
      • VIEW ANY DEFINITION : Grants the ability to see the T-SQL code and any metadata for any object within the server.
      • VIEW ANY DATABASE : Grants the ability to see metadata on databases.

In either authentication scenario, the solution continuously monitors your HA configuration state and updates billing automatically. You pay full SQL Server licensing costs only for the active node, while the passive node will only be charged for EC2 Compute and Windows Server licensing costs.

Getting Started

Enable Amazon EC2 High Availability for SQL Server LI

To enable the passive node benefit, navigate to the EC2 console and select one or more instances running SQL Server Enterprise or Standard edition LI on EC2. For this blog, we’ll select the primary and secondary EC2 instances as shown in Figure 2. From the EC2 console, select your instances, click Actions, expand Instance settings, and choose Modify SQL High Availability settings to begin the configuration process as shown in Figure 2.

Figure 2: Modify SQL High Availability settings

Note: Make sure to add all EC2 instances that are part of your SQL Server cluster including Primary and Secondaries.

After choosing Modify SQL High Availability settings, you’ll access the Modify SQL license High Availability settings wizard. In the Step 1, “Review prerequisites” as shown in Figure 3, the wizard verifies two key requirements: the SSM agent installation status and IAM permissions configuration.

Your EC2 instances must have the SSM agent installed and either the AWS-managed policy AWSEC2SqlHaInstancePolicy attached to their instance role, or a custom role with equivalent permissions as listed in prerequisites.

For this blog as shown in Figure 3, we’re using the IAM role SSMRoleForInstances with the required permissions, and the SSM agent status shows as “Online.” If the SSM agent status shows as “Offline,” follow the instructions in the SSM Agent installation guide to install and configure the agent. Once you’ve confirmed these prerequisites are met, choose Next to proceed.

Figure 3: Step 1-Review prerequisites

In Step 2, “Manage SQL High Availability license savings” configure your SQL Server license settings. You can choose to Enable for individual instances or choose the slide toggle to Enable all instances. For this blog we selected to enable this on all instances as shown in Figure 4.

Note: In your environment if you have disabled [NT AUTHORITY\SYSTEM] then you’ll need to select the proper AWS Secret that connects to your SQL Server database instance created in prerequisites.

For this blog we select none for “SQL Server credentials – optional” After configuring your preferences, choose Next to continue.

Figure 4: Manage SQL High Availability license savings

In Step 3 “Review and apply changes“, verify your configuration settings before applying changes as shown in Figure 5. Ensure all status indicators appear green, confirming that prerequisites are met for the selected instances. After validating all settings, choose Apply changes to enable the SQL Server High Availability feature.

Figure 5: Review prerequisites

After choosing Apply changes, you’ll return to the Amazon EC2 console automatically. The instances may take a few minutes to show the updated status. Select your opted-in instance and navigate the new SQL High Availability tab to verify your configuration. This tab displays the SQL High availability status and SQL license usage details for your SQL Server instance as shown in Figure 6.

Figure 6: SQL High Availability status.

The SQL High Availability tab displays key metrics for your SQL Server configuration. For the Primary EC2 instance, you’ll observe the SQL High availability status showing as “Active” and the SQL license usage indicating “Full license included” as shown in Figure 6, which is expected since this is the active node in your HA configuration.

When viewing the SQL High Availability status tab for the Secondary EC2 instance, you’ll see the High availability status showing as “Standby” and the SQL license usage displaying “Waived,” confirming that the passive node is successfully applied as shown in Figure 7.

Figure 7: SQL High Availability Status for Standby Node

Important considerations

  1. A passive SQL Server node must meet Microsoft’s requirements (page 26) to qualify for the benefit, including:
    a. Does not serve incoming traffic
    b. Does not run active SQL Server workloads
    c. Is not a readable secondary (except master, msdb, tempdb, or model databases)
    d. There is not a standalone database running outside of the Availability Group (this is specific for AGs only)
  2. The option is available with these configurations:
                Supported HA deployments: Always On Availability Groups and Failover Cluster Instances
                Supported versions:
        • Microsoft Windows Server 2019 or newer versions
        • Microsoft SQL Server 2017 or newer versions
        • Microsoft SQL Server Enterprise and Standard editions only
        • Windows PowerShell 5.1 or later
        • SQL Server LI on Amazon EC2 running on Windows Server only

3. The passive instance must be the same size or smaller than the active instance in terms of vCPUs
4. Supports Multi-AZ deployments within the same region (cross-region not supported)
5. Compatible with the Optimize CPU for LI feature for core licensing optimization
6. Only two-node clusters are supported

Licensing information

SQL Server LI is billed per vCPU per second for on-demand instances on EC2, subject to a four-core licensing minimum for SQL Server. SQL Server LI EC2 instances smaller than four-vCPUs in size will still be billed for four cores of SQL Server. SQL Server EC2 instances using the Passive Node High Availability feature will not be billed for SQL Server LI on the passive secondary SQL Server node, but Windows Server LI costs will still be billed, based on the number of vCPUs of the EC2 instance, and compute costs will still be incurred for the passive secondary node. Windows Server LI is billed per vCPU per second for on-demand instances on EC2, with no minimum vCPU license requirement.

While this new feature is specific for SQL Server LI on EC2, customers can also BYOL SQL Server on EC2 with License Mobility through Software Assurance, as AWS is an Authorized Mobility Partner. When bringing SQL Server Core licenses with active Software Assurance or eligible subscription, all the vCPUs of the EC2 instance must be licensed, with one core license covering one vCPU. A minimum of four cores of SQL Server licensing must be assigned to each EC2 instance per Microsoft’s Product Terms. SQL Server/CAL licenses are also eligible for License Mobility with Software Assurance or eligible subscriptions, where one Server license is assigned to each EC2 instance, and all users/devices with direct or indirect access have CALs assigned to them. SQL Server CALs must also have active Software Assurance or subscription to be eligible. If deploying SQL Server on Windows Server on shared EC2, Windows Server LI is required per Microsoft’s license terms.

Clean Up

It is a best practice to delete resources that you are no longer using so that you do not incur unintended charges. You can clean up the resources you created using this tutorial:

Conclusion

In this post, we showed you how to enable SQL Server High Availability feature to save costs on Standby License Included Amazon EC2 SQL Server instances. This feature helps you to reduce your SQL HA costs by up to 40%.

For customers looking to templatize their deployments and take advantage of this optimization, AWS CloudFormation support will come soon. The new feature can also be used through the AWS API. This feature may be terminated, in which case AWS will provide you with as much prior notice as is reasonably practicable under the circumstances.

Vikas Babu Gali

Vikas Babu Gali

Vikas Babu Gali is a Senior Specialist Solutions Architect, focusing on Microsoft Workloads at Amazon Web Services. Vikas provides architectural guidance and technical assistance to customers across different industry verticals accelerating their cloud adoption.

Christine Megit

Christine Megit

Christine Megit is a Sr. Worldwide Microsoft Licensing Specialist at AWS. Christine joined AWS in 2020 after spending almost 10 years at a Microsoft reseller where she worked in sales administration and licensing assessments. Today, Christine supports customers, partners, and internal AWS service teams by providing guidance on effective Microsoft licensing strategies and best practices for optimizing Microsoft workloads on AWS.

Blake Lyles

Blake Lyles

Blake Lyles is a Microsoft Workloads Specialist Solutions Architect with a special focus on SQL Server. Blake has been at Amazon for over 6 years, spending most of that time working with database workloads, including, SQL Server on EC2, supporting RDS, Database Migration Service, and Amazon DocumentDB. Blake has helped customers migrate and modernize their database workloads on AWS.