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”.
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.
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.
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.”
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.
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”.
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.
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.
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.