SQL Server on Amazon RDS: what to monitor?
Monitoring your SQL Server instances, regardless of where they are hosted, requires a similar set of metrics and alerts. You want to know, for example, that all your SQL Servers, and associated services, are available and to be warned immediately of any interruptions. You need to check that all your backups and other important Agent jobs are running smoothly. Also, of course, you must track the performance of your SQL Server instances to ensure that it is always sufficient to provide a consistently good service to your users.
When using Amazon, or any other “platform as a service” provider, to run your SQL Server instances, the service provider looks after the machines. This means that you will not necessarily need, or even be able, to collect machine and OS-level data for metrics like disk capacity, disk transfer rates, machine-level memory use, or processor times and queue lengths. With Amazon RDS, you can get some visibility into machine-level performance, such as disk reads and writes and memory usage, by using the Amazon Web Services (AWS) API, and further metrics become available if you enable enhanced monitoring (whereas with Azure-hosted SQL Servers, it’s not possible to collect these metrics).
Beyond this, your monitoring can focus more on the performance of the application. In this case, that means collecting the SQL Server instance-level metrics that will help you understand the relationships between your database workload and SQL Server’s use of CPU, disk I/O and memory resources, and to determine when and under what conditions blocking, and other causes of resource waits, lead to performance degradation.
As with Azure SQL Managed Instances or other cloud-hosted platforms, some metrics require particular attention when monitoring SQL Servers running on Amazon RDS. For example, the amount of CPU and memory available to your Amazon-hosted SQL Server instances is determined by the database instance class. Therefore, you will want to monitor SQL Server’s CPU and memory usage patterns closely, to ensure you made the appropriate choice, and to understand when you may need to change instance class, or to tune queries to optimize resource use within your current class.
You will also want to track Disk IO metrics, especially IOPS (input/output operations per second). To maximize SQL Server performance on Amazon RDS, it’s very important to understand your IOPS baselines, and to know what parts of your workload drive substantial or sustained deviations from these baselines. SQL Monitor can capture IO throughput (read bytes per second and write bytes per second) and IOPS (reads per second and writes per second) using the AWS API, although the latter requires enhanced monitoring. You’ll also want to track the IOPS metric for read operations to determine whether you have allocated enough RAM to the instance so that “your working set resides almost completely in memory“, which is a best practice for optimizing the IOPS performance of Amazon RDS SQL Server instances.
Adding Amazon RDS SQL Servers to SQL Monitor
You can add Amazon RDS SQL Servers to SQL Monitor right now, either through SQL Monitor’s GUI, or automatically through its PowerShell API.
You simply enter the endpoint for the RDS instance, as provided by Amazon AWS, and the credentials for accessing the Amazon RDS SQL Server instance. By providing SQL Monitor with credential for the AWS API credentials, it can collect some additional data. Ideally, you will also have enhanced monitoring enabled, in which case AWS collects metrics from an agent on the instance (rather than just for the hypervisor), giving SQL Monitor access to more extensive machine-level metrics. Learn more in our documentation.
Once you’ve added a SQL Server instance to Amazon RDS, SQL Monitor will automatically collect and store all the ‘base’ metrics you require to monitor SQL Server’s consumption of CPU, memory, and disk I/O resources, including those critical metrics identified above, relating to IOPS performance.
You can analyze historical data for each of these metrics and get a baseline to identify unusual patterns of activity. You will receive alerts for any problems, SQL Server errors or abnormal performance conditions, and these alerts will appear on the activity graph timeline (seen at the top of the previous figure), so you can review them directly within the context of SQL Server activity around the time the problem occurred. You can also annotate this activity timeline with events, such as deployments, to assess their impact.
From here, you can drill down to see the top 10 queries and waits, with historical data, detailed analysis of the problem, and query plans to help you diagnose and solve issues quickly.
We also provide information on blocking processes, SQL user processes, latches and locks, and disk usage in short, everything you need to successfully maintain a healthy Amazon RDS SQL Server instance. You can also easily add custom metrics tailored to your specific requirements for each application and business process.
Finally, you’ll also be able to use SQL Monitor’s advanced tempdb metrics (also new in SQL Monitor 11) to diagnose the sessions, databases and applications that are the cause of contention issues, or of rapid tempdb growth, for example.
Managing migrations to Amazon RDS with SQL Monitor
If you are moving your on-premises servers onto a cloud hosting platform, as many organizations are doing this year, monitoring needs to be integral to the project.
First, you will want to benchmark the performance, availability, and other relevant metrics of your on-prem servers. These are your business-as-usual metrics, including the key performance metrics such as disk transfers per second (IOPS) that are likely to change in the virtualized environment in AWS, which will determine the success of a migration. It’s recommended you collect at least 30 days of data, but the longer you are monitoring prior to migration the better.
Next, you should monitor the performance of your databases immediately post-migration to ensure they are available and running as expected.
Once you are satisfied that your migrated databases are behaving as expected, you can begin to monitor your new Amazon RDS-hosted instances to see how performance has changed. Hopefully, it will have improved and give you the data you need to make a business case for future migrations. You may even be able to identify cost-saving opportunities, such as under-utilized instances or those with declining capacity requirements.
Once you’ve completed your migrations you will have the benefit of monitoring your Amazon RDS hosted instances in exactly the same way as you have monitored your on-premises instances, bringing consistency and familiarity to you and your team’s work.
Monitor your SQL Server estate through a single pane of glass
With addition Amazon RDS monitoring in version 11, SQL Monitor now enables you to monitor your entire SQL Server estate regardless of how it is hosted. This means that servers hosted in different data centres or different cloud platforms for business, security or organizational reasons can be seen in one central dashboard, for optimal responsiveness.
You can be sure availability and performance are prioritized for business-critical instances, and you can also be confident that any compliance requirements can be easily maintained through simple categorization and alert management.
You can get started with SQL Monitor by downloading a free trial.
You can learn more about monitoring Amazon RDS with SQL Monitor in this webinar, where AWS experts at Redgate will take you through the benefits of monitoring your entire estate from a single pane of glass.
Was this article helpful?