AWS Database Blog

Improve observability by using Amazon RDS Custom for SQL Server with Telegraf and Amazon Grafana

You can use open source monitoring solutions like Telegraf, InfluxDB, and Grafana to monitor your applications and databases running on-premises or on Amazon Elastic Compute Cloud (Amazon EC2). In this post, we explain how you can leverage these tools on Amazon Relational Database Service (Amazon RDS) Custom for SQL Server.

Use of open source software in application monitoring gives you the flexibility to further customize these tools for your specific monitoring needs. By utilizing an extensive range of supported Grafana data sources and Telegraf plugins, your monitoring solution can integrate metrics from various application components, such as CDNs, APIs, databases, networks, and firewalls, along with business key performance indicators (KPIs) into a single dashboard.

When moving from a self-managed SQL Server environment to a managed database solution like Amazon Relational Database Service (Amazon RDS) for SQL Server, you can monitor your database using integrated solutions like Amazon CloudWatch and Amazon RDS Performance Insights. These services provide many advantages over self-managed solutions. However, if you’re heavily invested in an agent-based monitoring solution, you may feel restricted because you can’t extend the same solution to Amazon RDS for SQL Server due to the lack of access to the underlying operating system.

Amazon RDS Custom is a managed database service for applications that require customization of the underlying operating system and database environment. You can now monitor Amazon RDS custom metrics in your existing monitoring solution. Because RDS Custom provides complete access to the underlying OS, you can install a variety of monitoring agents, such as Telegraf. This also enables you to have granular control over various metrics and customize your monitoring solution even further.

In this post, we explain how to implement this solution and improve observability for Amazon RDS Custom for SQL Server. Remember, to persist the changes made on your RDS Custom instance, you should create your own Custom Engine Version (CEV). The CEV feature allows you to use your own AMI as a basis for RDS Custom for SQL Server, and ensures your OS changes will persist in certain situations, such as a disk corruption.

Monitoring tools used in this post

To build a monitoring solution, Amazon RDS Custom allows you the flexibility to use a variety of open source or proprietary software’s which requires access to the underling operating system. We use the following open source software’s in this post:

  • InfluxDB – InfluxDB is an open source timeseries database. We use InfluxDB for storing metrics.
  • Telegraf – Telegraf is a server-based agent for collecting and sending metrics and events from databases, systems, and IoT sensors.
  • Amazon Managed Grafana – Amazon Managed Grafana is a fully-managed service for Grafana, a popular open-source analytics service. We are using Amazon Managed Grafana for the purpose of visualizing SQL Server metrics.

Solution overview

To monitor our RDS Custom for SQL Server instance, we install a Telegraf agent to collect and push SQL Server metrics into a time series database. For this post, we use Amazon EC2 and create an Ubuntu instance to run open-source InfluxDB as our time series database to store the metrics. To make monitoring and visualization simpler, we use Amazon Managed Grafana.

With this solution, you can achieve the following:

  • Gain access to the OS of your RDS Custom instance and deploy a monitoring agent (for this post, we demonstrate with Telegraf)
  • Configure the Telegraf agent and enable the SQL Server input plugin to collect database performance metrics from your RDS Custom instance and ingest the data into an InfluxDB database
  • Create a monitoring dashboard for SQL Server metrics in Amazon Managed Grafana.

The following diagram illustrates our solution architecture.

Prerequisites

Deploy the required resources using AWS CloudFormation.

Launch Stack

The template creates an IAM instance profile, AWS Key Management Service (AWS KMS) encryption key, and more resources. For more information, refer to Configure networking, instance profile, and encryption.

You can verify the resources on the Outputs tab on the AWS CloudFormation console, as shown in the following screenshot. Note the values for the KMS key, security group, and VPC, to use in later steps.

Create an RDS Custom instance

To create your RDS Custom instance, complete the following steps:

  1. On the Amazon RDS console, choose Create database.
  2. Select Standard create.
  3. Select Microsoft SQL Server as the engine type.
  4. For Database management type, select Amazon RDS Custom.
  5. Select any SQL Server Edition

  1. Under Settings¸ enter a name for the database and a primary user name and password.
  2. For Instance configuration and storage, keep the default parameters.
  3. Under Connectivity, choose the VPC and subnet you created with the CloudFormation template.

  1. For VPC security group, select Choose existing and choose the security group created as part of the CloudFormation template.

  1. Under RDS Custom security, choose the instance profile and the AWS KMS key created through the CloudFormation template.

  1. Choose Create database and wait for Amazon RDS to provision the RDS Custom instance.

Deploy InfluxDB on an Ubuntu instance

Since InfluxDB is supported on multiple operating systems, we are using Ubuntu for this post. To deploy InfluxDB on an Ubuntu instance, complete the following steps:

  1. Launch an EC2 instance with Ubuntu version 22 in the same VPC as the RDS Custom instance.
  2. Log in to the EC2 instance and run the following commands to download and install InfluxDB 1.8.0:
sudo su  
apt-get -y 
update wget https://dl.influxdata.com/influxdb/releases/influxdb_1.8.10_amd64.deb
sudo dpkg -i influxdb_1.8.10_amd64.deb
systemctl start influxdb
systemctl enable influxdb
  1. Optionally, you can run the following command to make sure that InfluxDB is running:
systemctl status influxdb
  1. Because the InfluxDB instance is configured on port 8086, create an inbound rule in the security group of the InfluxDB EC2 instance to allow the RDS Custom instance on port 8086.

Create a database in InfluxDB

To create your database in InfluxDB, complete the following steps:

  1. Connect to the terminal on Amazon EC2 and open the InfluxDB CLI with the following command:
influx

This command brings up the InfluxDB CLI, as shown in the following screenshot.

  1. Run the following command to create a database called sqlmonitoring:
create database sqlmonitoring
  1. Optionally, you can verify that the database is created successfully by running the following command:
show databases

Create login credentials for InfluxDB and the Telegraf agent

By default, InfluxDB 1.8 has no authentication enabled. We enable authentication by creating an admin user. We also create another user named ‘telegraf‘. The telegraf agent running on RDS Custom server will use the ‘telegraf’ user account to send metrics data to the InfluxDB database. To create your users, complete the following steps:

  1. Run the following command in the InfluxDB CLI to provision the users admin and telegraf:
CREATE USER admin WITH PASSWORD 'password' WITH ALL PRIVILEGES
CREATE USER "telegraf" WITH PASSWORD 'Telegraf@123'
GRANT READ,WRITE ON sqlmonitoring TO telegraf
  1. Verify if users are created successfully:
show users

Set up your RDS Custom instance to enable RDP access

To allow remote desktop (RDP) access on your RDS Custom instance, complete the following steps:

  1. Create an inbound rule in the security group of the RDS Custom instance to allow connections on the RDP port.

  1. Connect to the RDS instance using Session Manager and run the following command to create a firewall rule to allow RDP traffic to the instance:
Set-NetFirewallRule -DisplayName "Remote Desktop - User Mode (TCP-In)" -Direction Inbound -LocalAddress Any -Profile Any

We use AWS Secrets Manager to store our credentials.

  1. On the Secrets Manager console, locate the secret rds-custom.

  1. Choose Retrieve secret value

  1. Save the secret value as a .pem file.
  2. Use this .pem file to get the password of the RDS custom instance.

Create a SQL server login for Telegraf

Next, we need to provision a login account in the SQL Server instance and provide sufficient privileges to access various system catalogs and dynamic management views (DMVs) to collect various SQL metrics.

  1. RDP to the RDS Custom instance and open SQL Server Management Studio (SSMS).
  2. Connect using the credentials provided during the RDS Custom instance creation.

  1. Open a new query window and run the following query to create a SQL login with the necessary privileges to collect metrics:
USE master;
GO
CREATE LOGIN telegraf WITH PASSWORD = N'Telegraf@123';
GO
GRANT VIEW SERVER STATE TO telegraf;
GO
GRANT VIEW ANY DEFINITION to telegraf;
GO

Set up a Telegraf 1.23.2 agent on the RDS Custom instance

For the purpose of this post, we are using Telegraf version 1.23.2, but you should use the latest available version.

To set up Telegraf, complete the following steps:

  1. RDP to the RDS instance and run the following script in Windows PowerShell:
## Download telegraf
Invoke-WebRequest https://dl.influxdata.com/telegraf/releases/telegraf-1.23.2_windows_amd64.zip -UseBasicParsing -OutFile telegraf.zip
## Expand the archive
Expand-Archive .\telegraf.zip
## Create Directory structure for telegraf agent installation
mkdir 'C:\Program Files\InfluxData\telegraf'
## Copy telegraf exe to 'C:\Program Files\InfluxData\telegraf'
copy .\telegraf\telegraf-1.23.2\telegraf.exe 'C:\Program Files\InfluxData\telegraf\'
  1. Create a file called telegraf.conf in C:\Program Files\InfluxData\telegraf\ with the following configuration (provide the IP address of the InfluxDB EC2 instance):
# Telegraf Configuration
# Configuration for telegraf agent
[agent]
interval = "10s"
round_interval = true
metric_batch_size = 1000
metric_buffer_limit = 10000
collection_jitter = "0s"
flush_jitter = "0s"
precision = "0s"
## Override default hostname, if empty use os.Hostname()
hostname = ""
omit_hostname = false
###############################################################################
# OUTPUT PLUGINS #
###############################################################################
[[outputs.influxdb]]
urls = ["http://<INFLUXDB_IP>:8086"]
database = "sqlmonitoring"
###############################################################################
# INPUT PLUGINS #
###############################################################################
[[inputs.cpu]]
percpu = true
totalcpu = true
collect_cpu_time = false
report_active = false
core_tags = false
[[inputs.mem]]
[[inputs.sqlserver]]
servers = [
"Server=localhost;Port=1433;User Id=telegraf;Password=telegraf@123;app name=telegraf;log=1;",
]
auth_method = "connection_string"
database_type = "SQLServer"
query_version = 2
  1. Run the following command to install Telegraf as a Windows service:
“C:\Program Files\InfluxData\telegraf\telegraf.exe” --service install --config "C:\Program Files\InfluxData\telegraf\telegraf.conf"
  1. On the Start menu, choose Run and enter services.msc.
  2. Start the Telegraf Data Collector Service.

Verify data ingestion in InfluxDB

Access the Influx CLI and run the following commands to verify that the data pushed by the Telegraf agent is visible in the sqlmonitoring database:

Use sqlmonitoring
Show measurements

You should see the following output when the Telegraf agent starts to ingest metrics into InfluxDB.

Set up a Grafana workspace

On the Amazon Managed Grafana console, make sure you’re in the same Region as the InfluxDB EC2 machine. You can follow the steps below to setup an Amazon Managed Grafana workspace.

  1. Create a workspace
  2. Setup VPC connectivity to allow connectivity with InfluxDB on EC2
  3. Configure the workspace

Set up an InfluxDB data source in Grafana

To set up your data source in Grafana, complete the following steps:

  1. Log in to your Grafana workspace.
  2. Under Configuration in the navigation pane, choose Data sources.
  3. Choose Add data source.
  4. Select InfluxDB.
  5. For Name, enter a name for your data source.
  6. For Query Language, choose InfluxQL.
  7. For URL, enter your InfluxDB instance URL.

  1. In the InfluxDB Details section, enter the database name sqlmonitoring and enter the credentials of the telegraf user.
  2. Choose Save & test.

Create a dashboard in Grafana

We now import the JSON to create a dashboard in Grafana for monitoring SQL Server metrics. You can further customize the dashboard panels and queries for your specific monitoring requirements.

  1. Log in to your Grafana workspace.
  2. In the left pane, hover the mouse on the + sign and choose Import.
  3. There are multiple open source dashboards available on the Grafana Dashboard site. We recommend Dashboard ID: 9386
  4. Enter dashboard ID 9386 into the ‘Import from Grafana.com’ text box

  1. Choose ‘Load’
  2. Choose the InfluxDB data source in the next window and choose ‘Import’

  1. Choose Import
  2. Once you finish importing the dashboard you will start seeing the SQL server metrics like the following screenshots.
    • Infra details:
    • Performance Counters:

Clean up

To avoid incurring unwanted charges, delete the resources you created as part of this post. Refer to the following instructions:

Summary

In this post, we demonstrated a solution to set up SQL Server monitoring on an RDS Custom instance using Telegraf’s input plugin for SQL Server. We also demonstrated how you can configure InfluxDB and Amazon Managed Grafana for storing and visualizing metrics. Remember, in order to persist your changes on RDS Custom for SQL Server, you need to create a Custom Engine Version which allows you to use your own AMI as a basis for RDS Custom.

Try out the solution and if you have any comments or questions, leave them in the comments section.

About the Authors

Arun Pandey is a Senior Database Specialist Solutions Architect at AWS. With over 17 years of experience in application engineering and infra-architecture, Arun helps digitally-native companies in India build resilient and scalable database platforms, which aids in solving complex business problems and innovating faster on AWS.

Siddharth Joshi is a Technical Account Manager at AWS with 15 years of experience. At AWS, he works with ISV customers to help them achieve operational excellence, among other technical guidance. He is passionate about observability and automation.