Microsoft Workloads on AWS

Reduce Microsoft SQL Server licensing costs with AWS Compute Optimizer

In this blog post, we explore a new capability of AWS Compute Optimizer that generates Microsoft SQL Server licensing recommendations. AWS Compute Optimizer has the capability to provide customers running Microsoft SQL Server on Amazon Elastic Compute Cloud (Amazon EC2) with licensing cost optimization recommendations, which can, result in significant SQL Server licensing cost savings.

AWS Compute Optimizer leverages inferred workload types to detect if SQL Server is running on an Amazon EC2 instance. It can then detect if SQL Server Enterprise edition features are being used and recommend to customers if downgrading to SQL Server Standard edition is an option to save on licensing costs.

Addressing challenges in optimizing performance and SQL Server licensing

Database administrators (DBA) are looking for easier ways to optimize their SQL Server on Amazon EC2 instances. Database performance needs are changing frequently, resulting in different hardware requirements, features, and configurations. This leads customers to require dedicated DBAs to manually analyze and assess the database performance.

AWS Compute Optimizer provides customers with right sizing recommendations for their Amazon EC2 instances and Amazon Elastic Block Store (Amazon EBS) volumes, which can provide infrastructure cost savings. Using AWS Compute Optimizer and inferred workload types, DBAs can quickly and easily identify an Amazon EC2 instance running SQL Server workloads and review the suggested recommendations after large application and database releases. Right sizing analysis and recommendations focus mainly on optimizing cost and performance of the Amazon EC2 instance and Amazon EBS volumes, but lacks insights into required database features, thus limiting edition recommendations.

One commonly overlooked cost optimization for SQL Server workloads is licensing. For example, a SQL Server Enterprise edition feature might have been required when the application was originally launched, but in subsequent releases is no longer used. Without deep knowledge of individual release changes per application/database, customers could miss a feature requirement change and may not be able to identify the SQL Server edition downgrade opportunity.

An additional use case for identifying edition downgrading opportunities are SQL Server version upgrades. SQL Server Enterprise and Standard edition features can change between different versions. For example, Transparent Data Encryption (TDE) is a popular feature in SQL Server Enterprise edition. With the release of SQL Server 2019, TDE is now available with Standard edition, thus removing the Enterprise edition requirement. AWS Compute Optimizer provides SQL Server licensing edition downgrade recommendations for customers running SQL Server workloads on Amazon EC2.

AWS Compute Optimizer checks SQL Server for a variety of Enterprise edition features, including:

  • Instances requiring more than, 128 GB of memory for buffer pool or 48 vCPU
  • Commonly used features, such as Availability Groups, resource governor, and asynchronous read replicas
  • Less frequently used features, such as NUMA aware and memory-optimized tempdb metadata

If none of these features or limitations are present, AWS Compute Optimizer recommends downgrading SQL Server editions and shows the on-demand price cost savings potential (see Figure 1). If you want to know more about SQL Server edition feature comparisons, please refer to “Editions and supported features of SQL Server 2022”.

Figure 1: AWS Compute Optimizer provides recommendations on Amazon EC2 and Amazon EBS to help quickly identify resources which are under-provisioned, optimized, or over-provisioned.

Figure 1: AWS Compute Optimizer provides recommendations on Amazon EC2 and Amazon EBS to help quickly identify resources which are under-provisioned, optimized, or over-provisioned.

These automated insights and recommendations can help DBAs and customers easily identify and validate when there is an opportunity to downgrade SQL Server editions and cost optimize their workloads. With AWS Compute Optimizer evaluating SQL Server edition features, DBAs and customers can be rest assured they are using the most cost-optimized edition of SQL Server at all times.

SQL Server Enterprise edition is 73% more expensive than Standard edition, so downgrading from Enterprise to Standard edition can yield significant cost savings, as shown in Table 1. The prices shown are based on Microsoft’s public pricing, as of this blog post’s publication date, for SQL Server 2022 and SQL Server 2019.


Table 1. Pricing comparison for SQL Server Enterprise and Standard editions

In addition to reducing SQL Server licensing costs, downgrading SQL Server from Enterprise to Standard edition can help BYOL customers reduce Software Assurance cost. BYOL customers can optimize their licensing investment by repurposing or shelving unused Enterprise edition licenses, avoiding additional licensing costs if repurposed and/or reducing true-up costs.

AWS Compute Optimizer’s right sizing recommendations are available at no additional cost. The edition downgrading recommendation requires customers to enable Amazon CloudWatch Application Insights, which uses a paid custom metric. To learn more, please read What is Amazon CloudWatch Application Insights?.

Getting started with AWS Compute Optimizer

1. To start receiving AWS Compute Optimizer recommendations, you will need to opt-in for AWS Compute Optimizer. Once you have opted-in, you will begin to receive resource right sizing recommendations such as Amazon EC2 instance type selections and Amazon EBS volumes IOPS and throughput recommendations. The inferred workload type feature is enabled by default, so no additional settings are required to detect if SQL Server is running on an Amazon EC2 instance. We recommend enabling memory utilization with the Amazon CloudWatch agent for your SQL Server workloads on Amazon EC2 instances for deeper memory utilization insights.

2. For the commercial software license recommendations feature in AWS Compute Optimizer to provide recommendations, you will need to enable Amazon CloudWatch Application Insights for individual Amazon EC2 instances. To view which Amazon EC2 instances have Amazon CloudWatch Application Insights enabled or require enabling, click on “Licenses” in the navigation pane within the AWS Compute Optimizer console, as shown in Figure 3.

Figure 3: Within the Licenses section in AWS Compute Optimizer, you can view the details of the Amazon EC2 instances which have detected SQL Server workloads running.

Figure 3: Within the Licenses section in AWS Compute Optimizer, you can view the details of the Amazon EC2 instances which have detected SQL Server workloads running.

3. In the recommendations for licenses dashboard, your Amazon EC2 instances running SQL Server workloads will be listed and can be sorted by findings. There are three potential findings:

a. Optimized – For these Amazon EC2 instances, Amazon CloudWatch Application Insights is enabled, and AWS Compute Optimizer has determined that Enterprise features are being used, and is therefore already optimized.

b. Not optimized – For these Amazon EC2 instances, Amazon CloudWatch Application Insights is enabled, and AWS Compute Optimizer has identified that you are not using SQL Server Enterprise edition features and should consider downgrading to Standard edition.

c. Insufficient metrics – For these instances, Amazon CloudWatch Application Insights is not enabled or do not have appropriate permissions, and therefore, a recommendation cannot be provided.

4. For Amazon EC2 instances showing Insufficient metrics, click on the Instance ID to start the Application Insights enabling process.

5. To allow AWS Compute Optimizer and Amazon CloudWatch Application Insights access to check SQL Server Enterprise edition feature usage, a secret should be selected or created (see Figure 4). The secret will be a SQL Server authenticated instance login, username, and password, which needs to be set up on the target SQL Server instance. You will need to ensure that the SQL Server login has the following permissions:

GRANT VIEW SERVER STATE TO [LOGIN];

GRANT VIEW ANY DEFINITION TO [LOGIN];

Once you have created the login and granted the permissions to the target SQL Server instance, you can use AWS Secrets Manager to store the login credentials for Amazon CloudWatch Application Insights to use. Select the secret from the drop-down menu.

Figure 4: You can select the secret you create to allow Amazon Cloudwatch Application Insights access to your database in the dropdown

Figure 4: You can select the secret you create to allow Amazon Cloudwatch Application Insights access to your database in the dropdown

6. You will also need to set up an IAM policy and instance role to allow the Amazon EC2 instance to access the selected secret above. This instance role needs to be attached to the target Amazon EC2 instance running SQL Server to enable licensing recommendations. Click on the checkbox “Confirm Instance Role and Policy is attached.”

 

7. You can now click on Enable license recommendations (see Figure 5). Once you’ve clicked on this, you should get a green checkmark at the top stating “CloudWatch Application Insights is successfully enabled.”

The options have been provided to enable license recommendations for an instance and the Enable license recommendations button has been selected. At the top there is a green bar which states CloudWatch Application Insights is successfully enabled.

Figure 5: Enable License Recommendations is clickable after providing the secret and confirming the instance role and policy are attached.

After enabling Amazon CloudWatch Application Insights, it will typically take 24 hours for the recommendations to populate within the AWS Compute Optimizer dashboard. The enabling process will deploy a Windows Service named PrometheusSqlExporterSQL (see Figure 6), on the target Amazon EC2 instance. This service is required to determine if any SQL Server Enterprise edition features are being used.

A Windows service named PrometheusSqlExporterSQL must be running on the instance in order to provide licensing recommendations. This image shows the service running from Windows Services.

Figure 6: Windows Services showing installed PrometheusSglExporterSQL service running.

To view the details of the recommendation, you can click on an instance ID which is identified as over-provisioned in the Findings column on the recommendations for licensing dashboard (see Figure 7). This will bring you to the instance details page, where you will see a new tab at the top labeled “License Recommendations.” Under this tab, you can view the findings, such as “not optimized” and finding reasons such as “license over-provisioned”.

In order to view detailed recommendations, you must go to the instance details. From the Licenses screen, you can click on the Instance ID to view detailed license specifics.

Figure 7: A view of the Licenses section within AWS Compute Optimizer. The example here shows a SQL Server instance with a recommendation to downgrade from Enterprise to Standard edition.

By clicking on the Instance ID, you can see more details on the licensing recommendations. On the instance specific page, you will see details about the costs of the current license versus the recommendation. In Figure 8, Enterprise is the current edition with Standard being the recommendation.

A view of the detailed license specifics showing the recommendation to move from Enterprise to Standard edition for SQL Server. This also shows pricing estimates if you were to downgrade.

Figure 8: Clicking on an Instance ID in the Licenses section of AWS Compute Optimizer will take you to a more detailed view of the specific recommendations for that instance.

You will also be able to view details such as the estimated monthly savings and savings opportunity percentage if you were to downgrade the SQL Server edition. In Figure 9, the Amazon EC2 savings and SQL Server recommendation savings are split out in a pie chart, showing estimated savings for both. The recommendation also includes the on-demand price of downgrading or staying at the current edition, allowing customers to have total cost savings transparency.

There is also a donut chart shown on the AWS Compute Optimizer homepage which shows a monthly savings estimate for all recommendations, including Licenses.

Figure 9: From the AWS Compute Optimizer dashboard, you can get a quick view of potential savings from the Licenses recommendations.

Downgrading from SQL Server Enterprise edition to SQL Server Standard edition

Customers running SQL Server on Amazon EC2 with SQL Server license-included AMIs should launch a new SQL Server Standard edition AMI and complete a database migration instead of attempting an in-place downgrade. Please review our SQL Server database migration methods documentation for different SQL Server native migration methods.

An AWS Systems Manager automation document can also help customers who are running SQL Server on Amazon EC2 with the SQL Server BYOL model downgrade from SQL Server Enterprise edition to Standard or Developer edition. Please review this detailed blog post about how to use the automation to downgrade editions: Downgrade SQL Server Enterprise edition using AWS Systems Manager Document to reduce cost.

Conclusion

By selecting the right SQL Server edition, customers can achieve significant cost savings while ensuring the SQL Server features needed are being used. The addition of the SQL Server Enterprise edition feature evaluation to AWS Compute Optimizer can help organizations reduce SQL Server licensing and infrastructure costs. Using this feature, customers can ensure that their SQL Server on Amazon EC2 instances are running in a performance, license, and cost optimized state.


AWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.

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.

Reghardt van Rooyen

Reghardt van Rooyen

Reghardt van Rooyen is a Senior Specialist Solutions Architect, focusing on Microsoft Workloads, at Amazon Web Services. Utilizing his 14 years of SQL Server database administration and leadership experience, Reghardt specializes in architecting high throughput SQL Server HADR solutions for enterprise customers. Always inquisitive, he explores AWS infrastructure and SQL Server database performance limits to ensure customers’ implementations are performant and cost optimized. As a native of South Africa, Reghardt enjoys rugby, BBQ, and spending time with his family and friends outdoors.

Yogi Barot

Yogi Barot

Yogi is Principal Solutions Architect who has 22 years of experience working with different Microsoft technologies, her specialty is in SQL Server and different database technologies. Yogi has in depth AWS knowledge and expertise in running Microsoft workload on AWS.