AWS Cloud Operations & Migrations Blog

Enhance observability for Amazon RDS Custom for SQL Server using Amazon Managed Service for Prometheus and Amazon Managed Grafana

In this blog post, you will learn how to improve observability on your Amazon RDS Custom for SQL Server database. You will configure metric exporters and send those metrics to Amazon Managed Service for Prometheus, to be visualized in Amazon Managed Grafana. By utilizing both Amazon Managed Service for Prometheus, and Amazon Managed Grafana, 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.

Prerequisites

To follow along with this post, you must have familiarity with the following AWS services:

Solution Overview

As part of this solution, you monitor SQL metrics from Amazon RDS Custom environment. The following diagram illustrates the solution architecture.

An Amazon RDS Custom for SQL Server instance uses the AWS Distro for Open Telemetry Collector to send metrics via a VPC Endpoint to Amazon Managed Service for Prometheus and Amazon Managed Grafana
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:

  1. Set up an RDS Custom for SQL Server environment.
  2. Create an Amazon Managed Service for Prometheus workspace.
  3. Install metric exporters on RDS custom SQL server.
  4. Install AWS Distro for Open Telemetry to ingest metrics into Amazon Managed Service for Prometheus.
  5. Configure Amazon Managed Service for Prometheus as data source to Amazon Managed Grafana.
  6. 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:

  1. Create the required network setup using a CloudFormation template.
  2. Once the network resources have been configured. Follow steps to create your RDS Custom for SQL Server database instance.
  3. 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.

The RDS Custom for SQL Server database instance in the RDS console. You can see the role, engine, region, size, and status of the instance

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 do-not-delete-rds-custom-DBInstanceIdentifier, where 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 Microsoft SQL Server, and the Remote Desktop protocol respectively).

The EC2 instance view, which shows the Windows bastion host and the RDS Custom for SQL Server instance. The console shows the instance state, instance type, state checks, alarm status, availability zones, and public IPv4 DNS information for the instances

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 AmazonPromethuesRemoteWriteAccess and 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.

On the screen showing the Amazon Managed Service for Prometheus workspace that was created, you see the workspace's ARN, remote write endpoint, query endpoint and Workspace ID

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 sqlserver://username:password@connectionstring:1433, replacing username, password, and connectionstring with your actual values. See figure 5.

The AWS Secrets Manager screen, where Other type of secret has been selected, the key is data_source_name, and the value is sqlserver://username:password@connection-string

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 secretsmanager:GetSecretValue permission.

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 localhost:9399.

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:

  1. Download and run aws-otel-collector installation file with the following config.yaml file. This solution is a good option if you have a small number of instances to manage.
  2. 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.yaml file to scrape the windows_exporter and sql_exporter metrics and remote write them into the workspace. The REGION field is the current Region of the workload. REMOTE_WRITE_ENDPOINT is 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]

You can optionally set up alerting via the workspace alert manager, to support a variety of alerting use cases.

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.

The SQL Server monitoring dashboard of RDS Custom SQL Server in Amazon Managed Grafana. The dashboard displays that the database health status. It also displays metrics for deadlocks, wait time, page faults, physical memory, available memory, and page file size

Figure 6: SQL Server Monitoring Dashboard of RDS Custom SQL Server on Amazon Managed Grafana

Clean up

To clean up this workload:

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.

Conclusion

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

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).