By Sudarshan Roy and Rajib Sadhu, Sr. Database Architects at AWS
Many customers choose to run their Microsoft SQL Server workloads on Amazon RDS for SQL Server to take advantage of its fully-managed services, high performance, and straight forward homogeneous migration to the cloud. Speed and performance are often prioritized over cost when customers first move to the cloud, which results in oversized instances and underutilized spend on unused resources. However, rightsizing your databases in the cloud is needed to achieve long-term cost efficiency. In this article, we will provide some best practice guidelines on how to optimize cost for your workloads on Amazon RDS for SQL Server.
Consolidation, in general terms, is the combining of various units into more efficient and stable larger units. When applied to an IT department, consolidation specifically translates into improved cost efficiency from higher utilization of resources, standardization and improved manageability of the IT environment, and (more recently) a focus on a “green” IT environment through reduced energy consumption. One of the important components in the IT environment is the database. The high cost of Microsoft licenses for SQL Server, especially SQL Server Enterprise Edition, is a point of concern for many customers planning their migration of SQL Server workloads to Amazon RDS for SQL Server. You have many options to consider when migrating your SQL Server workloads to Amazon RDS, each resulting in optimized price/performance and lower total cost of ownership (TCO). In addition, where customers have over-provisioned resources, like CPU and memory, there is opportunity to move existing workloads to more optimized RDS for SQL Server instances without compromising performance.
The benefits of efficient consolidation and rightsizing include:
- Reduce cost: The need to reduce licensing, hardware, and maintenance costs. The approaches that you have are: i) instance consolidation and ii) database consolidation. We will discuss in further details under the consolidation strategies section.
- Lower management overhead: Database servers require countless man hours to manage. Reducing instance footprint promotes efficiency, such as helping DBAs have better control over standard configurations, processes, and auditing. One best practice is to start by consolidating the non-production environments first.
- Efficient provisioning: Customers often deploy a dedicated database instance for a project which may lead to under-utilization. Database consolidation using a larger instance type may be more cost effective.
- Merge database servers with identical schema to a single RDS for SQL Server instance. The advantages of using a multi-tenant model are simplicity, cost, and operational efficiency.
- Group databases with similar characteristics into a single RDS for SQL Server instance. A couple of examples include 1) grouping production versus non-production workloads and 2) databases that are supposed to serve the same application.
- Select instance types optimized to meet your use cases. Amazon RDS provides a range of instance types which comprise varying combinations of CPU, memory, storage, and networking capacity to give you the flexibility to choose the appropriate mix of resources for your database. For example, many SQL Server databases are memory-bound rather than CPU-bound. So, try to fit databases into memory optimized instances such as the r5, r5b, r5d, x1e, and z1d instances where applicable.
Consolidation Considerations and Limitations
While many databases can benefit from consolidation and rightsizing, there are limitations and exceptions to take into account during your planning process. Key considerations include:
- RDS for SQL Server instances have a maximum number of databases they can host, which can be found in our documentation. Depending on your instance type, you can host up to 100 databases on an RDS for SQL Server instance.
- Depending on your licensing edition, instances have capacity (CPU & Memory) limits. For example, SQL Server Standard Edition has a maximum capacity of 24 cores and 128 GB memory. You should align that with the instance class chosen and factor in future growth.
- Databases should not be grouped without analyzing the usage trends with available tools, such as Extended Events, Query Store, Database Activity Monitor, Amazon CloudWatch, or certain 3rd Party Performance Monitoring Tools.
- Databases that have specialized requirements (e.g., sysadmin access, running SQLCLR) should not be consolidated on RDS for SQL Server. Examples of such databases include SharePoint, Microsoft CRM databases, or any other application which need vendor approvals. In these cases, Amazon RDS Custom for SQL Server is recommended as the managed database service as it provides privileged access to the underlying operating system and database environments for customization of applications.