In this blog post, you will learn how to improve observability on your
Custom for SQL Server database. You will configure metric exporters and send those metrics to
. By utilizing both Amazon Managed Service for Prometheus, and Amazon Managed
, you can spend time monitoring and improving your application, instead of spending time managing your monitoring infrastructure.
Amazon RDS Custom for SQL Server is a managed database service that allows you to access and customize your database environment and operating system. Monitoring is an important part of maintaining the reliability, availability, and performance of your Amazon RDS Custom instance. Administrators who want to monitor the database environment using open-source tools can install Prometheus exporters on the server. Prometheus exporters help you in exporting metrics directly from the operating system and database server. By collecting these metrics, you get a detailed view into how exactly your database server is performing.
To follow along with this post, you must have familiarity with the following AWS services:
- Basic knowledge of AWS CloudFormation
- Understanding of environment setup for Amazon RDS Custom for SQL Server.
- Understanding of establishing connection to RDS Custom DB through a bastion host.
- Amazon RDS Custom for SQL Server
- Amazon Elastic Compute Cloud (Amazon EC2)
- Amazon Managed Service for Prometheus
- Amazon Managed Grafana
- AWS Distro for OpenTelemetry (ADOT)
As part of this solution, you monitor SQL metrics from Amazon RDS Custom environment. The following diagram illustrates the solution architecture.
Figure 1: Solution of monitoring SQL Server metrics of RDS custom SQL server with Amazon Managed Prometheus and Amazon Managed Grafana
The high-level steps for implementing this solution are:
- Set up an RDS Custom for SQL Server environment.
- Create an Amazon Managed Service for Prometheus workspace.
- Install metric exporters on RDS custom SQL server.
- Install AWS Distro for Open Telemetry to ingest metrics into Amazon Managed Service for Prometheus.
- Configure Amazon Managed Service for Prometheus as data source to Amazon Managed Grafana.
- Create a dashboard on Grafana to monitor RDS custom SQL Server metrics.
1. Set up an RDS Custom for SQL Server environment
To deploy an Amazon RDS Custom for SQL Server instance, you first configure the networking and infrastructure required for the database. The previous link walks you through the following steps:
- Create the required network setup using a CloudFormation template.
- Once the network resources have been configured. Follow steps to create your RDS Custom for SQL Server database instance.
- Create an Amazon Elastic Compute Cloud (Amazon EC2) instance with a Windows operating system (OS) Amazon Machine Image (AMI). This will be a bastion host to connect to RDS Custom for SQL Server instance in a private subnet.
RDS Custom sends communication from your database instance to other AWS services. RDS Custom validates network connectivity to a variety of AWS services during installation, including Amazon CloudWatch and AWS Systems Manager. If the database cannot communicate with these services, the database shows an
incompatible-network state. You can resolve this in one of two ways. First, ensure resources in private subnets can communicate to the internet via an AWS NAT Gateway. Or, you can configure a VPC endpoint for AWS Systems Manager, AWS Secrets Manager, Amazon CloudWatch, Amazon EC2, and Amazon S3.
Figure 2: The RDS Custom for SQL Server database in the RDS console.
Unlike other RDS databases, an RDS Custom database is visible in the Amazon EC2 AWS console. The database is listed as
DBInstanceIdentifier is the identifier of the database.
Remote desktop into the RDS Custom for SQL Server database instance using the Windows bastion host you created. Make sure that you configure the security groups for both instances to communicate with each other over ports 1433 and 3389 (the ports for
, and the Remote Desktop protocol respectively).
Figure 3: The Windows bastion host and the RDS Custom for SQL Server instance visible in the Amazon EC2 console.
Next, modify the IAM Role
RDSCustomIAMInstanceProfile attached to RDS Custom for SQL Server instance to include
AmazonSSMManagedInstanceCore policies. The
AmazonPrometheusRemoteWriteAccess policy gives the RDS Custom instance permission to remote write metrics to Amazon Managed Prometheus. The
AmazonSSMManagedInstanceCore policy is used to enable Systems Manager core service functionality.
2. Create an Amazon Managed Prometheus workspace
If you don’t have an Amazon Managed Service for Prometheus workspace on your account, the next step is to create a workspace. Take note of the remote write URL endpoint. This is used to remote write metrics to the workspace.
Figure 4: The Amazon Managed Service for Prometheus workspace that has been created
3. Install metric exporters on RDS custom SQL server
For the next step, configure the RDS Custom database instance to allow RDP connections. First, connect to your RDS custom SQL server using AWS Systems Manager through console. Then set a firewall rule using the following command on the DB instance.
Set-NetFirewallRule -DisplayName "Remote Desktop - User Mode (TCP-In)" -Direction Inbound -LocalAddress Any -Profile Any
Next, log into your RDS Custom SQL server with RDP from the Windows bastion host you created. The easiest way to collect metrics on the RDS Custom database instance is to use a Prometheus exporter. We recommend the latest release of the windows_exporter to scrape Windows metrics, and we recommend sql_exporter to scrape SQL-specific metrics. Download the latest releases of these exporters from sql_exporter_packages and windows_exporter_packages. There are several other SQL exporters available. We recommend the sql_exporter because it is updated frequently, supports a number of database platforms, and is used by Grafana. For both exporters, download the latest release and follow the installation instructions on each project’s GitHub page.
For the windows_exporter, the default configuration emits metrics at localhost:9182. The sql_exporter exporter comes with a set of pre-defined SQL metrics, or you can create your own via SQL statements. The configuration file
sql_exporter.yml on the downloaded zip bundle, simply requires a database connection string, which can be securely stored in AWS Secrets Manager.
Create a new secret in AWS Secrets Manager. Select Other type of secret. For the Key enter
data_source_name and enter
connectionstring with your actual values. See figure 5.
Figure 5: Configuring AWS Secrets Manager with the database credentials used by sql_exporter.
Make sure the EC2 IAM role attached to the RDS Custom instance has the
Use the following
sql_exporter.yml configuration file. Replace
<AWS_SECRET_NAME> with the name of the secret you created in AWS Secrets Manager.
global: scrape_timeout: 10s max_idle_conections: 3 target: aws_secret_name: '<AWS_SECRET_NAME>' collectors: [msql_standard] collector_files: - "*.collector.yml"
Once the sql_exporter has been configured, it emits metrics at
4. Install AWS Distro for Open Telemetry to ingest metrics into Amazon Managed Prometheus
The next step is to begin scraping the metrics and remote writing it into the Amazon Managed Prometheus workspace. For this step, you install and configure the AWS Distro for OpenTelemetry (ADOT) to remote write the metrics to the workspace. To install and configure ADOT on a Windows host, select one of two options:
- Download and run aws-otel-collector installation file with the following
config.yamlfile. This solution is a good option if you have a small number of instances to manage.
- Install AWS Distro for OpenTelemetry Collector . You can optionally manage the configuration from a centralized location using AWS Systems Manager Distributor. Use the following
config.yamlfile to scrape the windows_exporter and sql_exporter metrics and remote write them into the workspace. The
REGIONfield is the current Region of the workload.
REMOTE_WRITE_ENDPOINTis the remote write URL of the workspace, which is shown in figure 4. This solution is a good option if you have many instances to manage, or want to avoid manual work.
extensions: sigv4auth: service: "aps" region: "REGION" receivers: prometheus: config: global: scrape_interval: 1m scrape_timeout: 10s scrape_configs: - job_name: windows_exporter static_configs: - targets: ['localhost:9182'] - job_name: sql_exporter static_configs: - targets: ['localhost:9399'] processors: batch: send_batch_size: 10000 timeout: 10s exporters: prometheusremotewrite: endpoint: "REMOTE_WRITE_ENDPOINT" auth: authenticator: sigv4auth resource_to_telemetry_conversion: enabled: true service: extensions: [sigv4auth] pipelines: metrics: receivers: [prometheus] processors: [batch] exporters: [prometheusremotewrite]
5. Configure Amazon Managed Service for Prometheus as data source to Amazon Managed Grafana
Set up an Amazon Managed Grafana workspace, a fully managed service to help you analyze, monitor, and alarm on metrics, logs, and traces across multiple data sources.
Next, use AWS data source configuration to add the Amazon Managed Service for Prometheus workspace as a data source to your Amazon Managed Grafana workspace. This creates a dashboard to monitor SQL metrics of your RDS Custom SQL server instance.
6. Create a dashboard on Grafana to monitor RDS custom SQL Server metrics
The final step is to create a dashboard to display sql metrics from RDS Custom that are ingested in Amazon Managed Service for Prometheus.
- Log in to your Amazon Managed Grafana workspace.
- In the left pane, select the plus sign, and choose Import.
- Enter dashboard ID 9336 into the ‘Import from Grafana.com’ text box.
Figure 6: SQL Server Monitoring Dashboard of RDS Custom SQL Server on Amazon Managed Grafana
To clean up this workload:
- Delete the Amazon Managed Grafana workspace
- Delete the Amazon Managed Service for Prometheus workspace,
- Delete the RDS Custom for SQL Server instance.
- Delete the Windows Bastion host.
To delete all other network resources that were launched as part of the CloudFormation stack, go to the AWS CloudFormation console, select the stack, and choose Delete.
In this blog post, we walked through collecting SQL server and Windows metrics using AWS Distro for OpenTelemetry collector. By writing those metrics to an Amazon Managed Service for Prometheus workspace you can monitor health of your RDS Custom DB instances and observe changes to your database workload.
As a next step, learn more about observability with the One Observability Workshop. Then get started by creating an RDS Custom for SQL Server database instance.
About the authors:
Praneeth Reddy Tekula
Praneeth Reddy Tekula is a Senior Solutions Architect focusing on EdTech at AWS. He provides architectural guidance and best practices to customers in building resilient, secure and scalable systems on AWS. He is passionate about observability and has a strong networking background.
Mike George is a Principal Solutions Architect based out of Salt Lake City, Utah. He enjoys helping customers solve their technology problems. His interests include software engineering, security, artificial intelligence (AI), and machine learning (ML).