AWS Database Blog
Monitor Amazon RDS Custom for SQL Server using Amazon CloudWatch
Amazon RDS Custom for SQL Server is a managed database service for applications that require customization of the underlying operating system and database environment. Amazon RDS Custom allows you to access and customize your database server host and operating system, for example, by applying special patches and changing the database software settings to support third-party applications that require privileged access. It supports applications that have dependencies on specific configurations and third-party applications that require customizations in corporate, e-commerce, and content management systems, such as Microsoft SharePoint.
With Amazon RDS Custom for SQL Server, you can enable features that require elevated privileges like SQL Common Language Runtime (CLR), install specific drivers to enable heterogenous linked servers, or have more than 100 databases per instance.
Monitoring is an important part of maintaining the reliability, availability, and performance of Amazon RDS Custom for SQL Server. However, at the time of writing, Amazon RDS Custom for SQL Server doesn’t have managed Amazon CloudWatch monitoring capabilities. In this post, we discuss how to use a CloudWatch dashboard to monitor Amazon RDS Custom for SQL Server, and how to use CloudWatch alarms for proactive monitoring. Additionally, we give a few examples of metrics-related alarms and demonstrate the setup process for improved monitoring capabilities.
Solution overview
The following diagram illustrates the solution architecture.
This solution enables you to monitor the following metrics from the RDS Custom environment:
- Host metrics – CPUUtilization, FreeableMemory, and FreeStorageSpace for the data and binary volume
| Infrastructure / Host | ||
| Console Name | Description | Units |
| Free Space [D] Drive | The amount of available storage space in the data volume | MB |
| Free Space [C] Drive | The amount of available storage space in the binary volume | MB |
| CPU Utilization | The percentage of CPU Utilization | Percent |
| Memory Available Mbytes | The amount of available random-access memory | Mbytes |
- Workload metrics – ReadIOPS, WriteIOPS, ReadThroughput, WriteThroughput, and database connections
| Workload Metrics | ||
| Console Name | Description | Units |
| Read IOPS | The average number of disk read I/O operations per second | Count/Second |
| Write IOPS | The average number of disk write I/O operations per second | Count/Second |
| Write Throughput | The average number of bytes written to disk per second | Kib/s |
| Read Throughput | The average number of bytes read from disk per second | Kib/s |
| User Connections | The number of client network connections to the database instance via SQL Server Listener 1433 port | Count |
| Logical Connections | The number of logical connections to the system | Count |
| Connection Reset/sec | Total number of logins started from the connection pool | Count |
| Buffer Cache Hit Ratio | Indicates the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand-page accesses | Percent |
- Performance metrics – Average read latency, average write latency, and average queue length
| Performance Metrics | ||
| Console Name | Description | Units |
| Avg Read Latency | The average amount of time taken per disk Read I/O operation | Milliseconds |
| Avg Write Latency | The average amount of time taken per disk Write I/O operation | Milliseconds |
| Avg Queue Length | The number of outstanding I/O (read/write requests) waiting to access the disk | Count |
The high-level implementation steps are as follows:
- Set up an RDS Custom instance.
- Assign AWS Identity and Access Management (IAM) permissions to the RDS instance role.
- Connect to the RDS instance.
- Configure CloudWatch resources with an automated script.
- Run a load test to populate the metrics.
- Create a CloudWatch alarm for monitoring.
Prerequisites
To follow along with this post, you should have familiarity with the following AWS services:
- Amazon CloudWatch
- AWS Command Line Interface (AWS CLI)
- Amazon Elastic Compute Cloud (Amazon EC2)
- Amazon RDS Custom for SQL Server
Set up an RDS Custom instance
For instructions on creating an RDS Custom for SQL Server instance, refer to Creating and connecting to a DB instance for Amazon RDS Custom for SQL Server.
Assign IAM permissions to the RDS instance role
When you create an RDS instance, you attach an IAM role that dictates the services the instance can connect to. In order to have the agent publish metrics successfully, we need to provide the appropriate permissions to the IAM role attached to the RDS instance.
To do this, navigate to the IAM role attached to the RDS instance and add the CloudWatchAgentServerPolicy policy. For instructions, refer to Adding and removing IAM identity permissions
For more information about the IAM permissions required for the CloudWatch agent, refer to Create IAM roles and users for use with the CloudWatch agent.
Connect to the RDS instance
To RDP into your RDS Custom for SQL Server instance, complete the following steps:
- On the Amazon EC2 console, choose Instances in the navigation pane.
- Select the EC2 instance that was created as part of Amazon RDS Custom for SQL Server and choose Connect.
- On the Session Manager tab, choose Connect.
An AWS Systems Manager Session Manager window opens.
- Enter the following command to configure your firewall to allow RDP connection to your RDS Custom host machine:
- Return to the Amazon EC2 console and choose the RDP client tab to enter the RDP connection details.
- For Connection type, select Connect using RDP client.
- You can either download the remote desktop file or manually create an RDP connection using the public or private DNS name of your EC2 instance, user name, and password.
For the password, fetch the key pair and use it to decrypt the password.
Also make sure the security group associated with the RDS Custom instance has inbound rules to allow your IP address to RDP port 3389.
- Use Microsoft Remote Desktop to RDP to the RDS Custom host machine.
Configure CloudWatch resources
You can use the following automation script to configure and start the CloudWatch agent and also create the CloudWatch dashboard. But before you run the script, you need to complete a few configuration prerequisites.
Install the AWS CLI, verify IAM permissions, and load data to the EC2 instance
Complete the following prerequisite steps:
- Install the AWS CLI on the underlying host (EC2 instance) of Amazon RDS Custom for SQL Server by running the following this command on PowerShell:
- Ensure the IAM role attached to the underlying host (EC2 instance) of Amazon RDS Custom for SQL Server has the following permissions:
- CloudWatch put-dashboard – This permission is required for the automation script to create the dashboard
- Amazon EC2 describe-volumes – This permission is required to get volume information for the automation script to create monitoring for the volumes.
- Verify if the IAM role has the required permissions for the automation script to run successfully by running the following command on PowerShell:
If both commands run successfully, we can confirm all permissions are set up correctly.
- Download and copy the cw-agent.json file from AWS GitHub repository to the C:\ drive on the RDS Custom EC2 instance.
- Download and copy the dashboardconfig.json file from AWS GitHub repository to the C:\ drive on the RDS Custom EC2 instance.
- Copy the CW_agent_config_automation.ps1 file from AWS GitHub repository to the C:\ drive on the RDS Custom EC2 instance.
Run the script
To run the .ps1 script, open a PowerShell terminal as administrator and run the following command:
The script will prompt for the CloudWatch dashboard name; provide a name and press Enter.
The automation configures the CloudWatch agent, starts the CloudWatch agent, and creates the RDS Custom dashboard. You can confirm the creation of the dashboard by using the CloudWatch console.
Note that in the case of an EC2 instance class modification or detection of an unhealthy EC2 host, Amazon RDS replaces the underlying EC2 instance. This action removes the CloudWatch configuration files (cw-agent.json, dashboardconfig.json, CW_agent_config_automation.ps1) and the CloudWatch configuration that was performed.
To persist the configuration files within Amazon RDS Custom, refer to Persist your OS-level customization within Amazon RDS Custom for SQL Server using Custom Engine Version (CEV). After you set up Amazon RDS Custom to use CEV with the CloudWatch configuration files persisted, in the event of host replacement, the user can reinitiate the custom dashboard by running the PowerShell script (CW_agent_config_automation.ps1).
The CloudWatch agent for the post has been configured to send five metrics from the Windows Performance counters. You can customize the configuration file to add more metrics. If you do modify the configuration file, you also need to modify the dashboard to display the new metrics.
Run a load test to populate the metrics
If your RDS Custom for SQL Server database has any workloads running, you should see the CloudWatch metrics being populated on the CloudWatch dashboard that you created.
For a new RDS Custom for SQL Server instance that doesn’t have any workloads running, you can use the Microsoft OStress utility to run queries in multiple threads and iterations to generate a workload. The OStress utility is part of the Microsoft RML Utilities for SQL Server and can be downloaded from RML Utilities for SQL Server.
To perform a load test using the OStress utility, run the following command on the command prompt:
Use the following parameters:
- -U – The primary user of your SQL Server database; the default is admin
- -P – The password of your primary user
- -n – The number of connections processing each input query in stress mode
- -r – The number of iterations for each connection to run its input file or query
- -q – Quiet mode; suppresses all query output
- -d – The database name
- -Q – A single batch query to process
You can increase the number of connections and iterations for each connection by increasing the values for the OStress parameters -n and -r to generate a high workload.
To make it easier to aggregate all the instance levels, volume levels, and SQL metrics in one place, we created a CloudWatch dashboard that enables you to monitor your RDS Custom database from a single pane of glass. The following screenshots show examples of our dashboard.
Create a CloudWatch alarm
You can create a CloudWatch alarm that watches a single CloudWatch metric. The alarm performs one or more actions based on the value of the metric or expressions relative to a threshold over a number of time periods. In this section, we create an alarm that sends a notification to an Amazon Simple Notification Service (Amazon SNS) topic, which lets you perform corrective actions.
- Create an SNS topic:
- Subscribe to the topic and specify the email protocol and an email address for the notification endpoint:
The metrics are being sent to three namespaces: AWS/EC2, AWS/EBS, and RDSCustom.
- You can use the following query to identify the available metrics in the individual namespaces:
- Choose any metric and use the following code to create a CloudWatch alarm. Be mindful of the namespace, dimensions, and metric name.
The following is an example of a CloudWatch alarm created on the CPUUtilization metric:
Note that the EC2 instance ID is used to create the preceding alarm. In the event that the EC2 instance gets replaced (for example, when scaling up of instance class), it will change the EC2 instance ID. You need to recreate the alarm with the new EC2 instance ID.
The following screenshot shows an example email sent via Amazon SNS.
Cleanup
To avoid incurring unexpected charges, delete the AWS resources that are no longer required.
Conclusion
In this post, we discussed how to integrate CloudWatch with an RDS Custom for SQL Server environment. This allows you to monitor the health of your RDS Custom DB instances and observe changes to the infrastructure and database workloads. You can monitor metrics over a specific time period and set CloudWatch alarms to receive notifications. You can perform one or more actions based on the value of the metric relative to a threshold that you set.
We welcome your feedback. If you have questions or suggestions, leave them in the comments section.
About the authors
Arnab Saha is a Senior Database Specialist Solutions Architect at Amazon Web Services. Arnab specializes in Amazon RDS, Amazon Aurora , AWS DMS and Amazon Elastic Block Store. He provides guidance and technical assistance to customers thus enabling them to build scalable, highly available and secure solutions in AWS Cloud.
Nanda Chinnappa is a Cloud Infrastructure Architect with AWS Professional Services at Amazon Web Services. Nanda specializes in Infrastructure Automation, Cloud Migration, Disaster Recovery and Databases which includes Amazon RDS and Amazon Aurora. He helps AWS Customer’s adopt AWS Cloud and realize their business outcome by executing cloud computing initiatives.
Sid Joshi is a Solutions Architect with Amazon Web Services. He works with AWS customers to provide guidance on cloud adoption, migration and strategy. He is passionate about technology and enjoys building and experimenting in the Networking and Observability space.