Select the right EC2 instance for SQL Server workloads

Overview

Microsoft SQL Server has been running on Amazon Elastic Compute Cloud (Amazon EC2) instances for over 15 years. AWS has taken that experience and used it to help develop Amazon EC2 instances to fit SQL Server workloads running from minimal specifications all the way to high performance, multi-Region clusters.

Choosing the correct EC2 instance for SQL Server is largely dependent on your workload. Understanding how SQL Server is licensed, how it uses memory, and how SQL Server features align with Amazon EC2 offerings can help guide you to the best EC2 instance for your application.

This section addresses a variety of SQL Server workloads and how they can be paired with certain EC2 instances to keep your licensing and compute costs to a minimum.

Cost comparison

Amazon EC2 enables you to Bring Your Own License (BYOL) or pay as you go with Windows Server and SQL Server licensing. For pay-as-you-go licensing, the licensing costs for the Windows Server and SQL Server licenses are baked into the hourly cost of the EC2 instance. For example, you can have different AMIs with different prices. The price of the AMI is contingent on the SQL Server edition that the AMI runs on.

Windows Server and SQL Server pricing isn't itemized. You won't find itemized pricing on tools like the AWS Pricing Calculator. If you select different combinations of license-included offerings, the licensing costs can be deduced, as the following table shows.

The most cost-effective method for running SQL Server is to stay at a lower-level edition until you need a feature from a higher-level edition. For more information, see the Compare SQL Server editions section of this guide. Upgrading from SQL Server Web edition to SQL Server Standard edition is over seven times the SQL Server licensing cost and over three times the cost of moving from Standard edition to Enterprise edition. The disparity in licensing costs is a major factor to consider and is explored in the rest of this section.

Cost optimization scenario

Consider an example scenario where an analytics company tracking delivery vehicles is seeking to improve its SQL Server performance. After a MACO expert reviews the company's performance bottlenecks, the company transitions from x1e.2xlarge instances to x2iedn.xlarge instances. Although the instance size is smaller, the enhancements to the x2 instances improve SQL Server performance and optimization by using buffer pool extensions. This allowed the company to downgrade from SQL Server Enterprise edition to SQL Server Standard edition and reduce its SQL Server licensing from 8 vCPUs to 4 vCPUs.

Before optimization:

After optimization:

The combined changes from x1e.2xlarge instances to x2iedn.xlarge instances enabled the example customer to save $5,407 per month on their production database servers. This reduced the total cost of the workload by 69 percent.

Cost optimization recommendations

Memory optimized instances

One of the most important aspects of SQL Server is understanding its reliance on memory. SQL Server attempts to use all available RAM not being used by the operating system (up to 2 TB for a default installation). It does this for performance reasons. Working with data in memory is much more performant than having to constantly pull data from disk, make changes, and then write it back to the disk. Instead, SQL Server attempts to load as much data from the attached databases as possible and keeps that data in RAM. Changes made to the data happen in memory and are hardened to disk at a later time.

Since SQL Server performs better with larger amounts of RAM, we typically recommend starting with Amazon EC2 memory optimized instance types. Memory optimized instances are versatile and offer a variety of different options. The R family has a 1-to-8 vCPU-to-RAM ratio and has options for Intel processors, AMD processors, enhanced networking, enhanced EBS performance, instance storage, and enhanced processor speed. For memory-heavy workloads, there's also an X family that combines many of the same options and extends the vCPU-to-RAM ratio to 1-to-32. Due to the versatility of memory optimized instances, you can apply them to SQL Server workloads of all shapes and sizes.

Workloads below minimal resources (less than 4 vCPUs)

Although some use cases work well with burstable (T3) instances, we recommend that you generally avoid using burstable instances for SQL Server workloads. The licensing for SQL Server is based on the number of vCPUs assigned to an instance. If SQL Server is idle the majority of the day and is acquiring burst credits, you pay for SQL licenses which you aren't fully utilizing. In addition, SQL Server has a minimum license requirement of 4 cores per server. This means if you have a SQL Server workload that doesn't require 4 vCPUs worth of compute power, you're paying SQL Server licensing which you aren't using. In these scenarios, it would be best to consolidate multiple SQL Server instances onto a larger server.

Workloads using minimal resources (less than 64 GB RAM)

Many SQL Server workloads under 64 GB RAM don't prioritize high performance or high availability. For these types of workloads, SQL Server Web edition might be a good fit if the application is covered under Microsoft's licensing restrictions.

SQL Server Web edition scales up to 32 vCPUs and 64 GB RAM and is 86 percent less expensive than SQL Server Standard edition. For low resource workloads, using an AMD memory optimized instance like the r6a, which has a 10 percent less expensive compute price than its Intel counterpart, is also a good way to keep compute and SQL licensing costs to a minimum.

Workloads with average resources (less than 128 GB RAM)

SQL Server Standard edition is used on the majority of SQL Server workloads up to 128 GB RAM. SQL Server Standard edition is 65–75 percent less expensive than SQL Server Enterprise edition and can scale up to 48 vCPUs and 128 GB RAM. Since the 128 GB RAM limitation is typically hit before the 48 vCPU limitation, it's the focus of most customers who want to avoid upgrading to SQL Server Enterprise edition.

SQL Server has a feature called the buffer pool extension. This feature enables SQL Server to use a portion of a disk to act as an extension of RAM. The buffer pool extension works well when combined with ultra-fast storage, like the NVMe SSDs used in Amazon EC2 instance storage. Amazon EC2 instances containing instance storage are denoted with a "d" in the instance name (for example, r5d, r6id, and x2iedn).

Buffer pool extensions are not a replacement for normal RAM. However, if you require more than 128 GB of RAM, you can use buffer pool extensions with EC2 instances like the r6id.4xlarge and x2iedn.xlarge to delay an upgrade to Enterprise edition licensing.

High performance workloads (more than 128 GB RAM)

SQL Server workloads requiring high performance are challenging for cost optimization because of their reliance on a lot of resources. However, understanding the differences in EC2 instances can prevent you from making the wrong choice.

The following table shows a variety of memory optimized EC2 instances and their performance limits.

Each instance is used for a different purpose. Understanding your SQL Server workload can help you choose the instance type that's best for you.

Details on attributes:

Additional resources