DEV Community

How to scan thru Amazon RDS for SQLServer error logs using metric filters

SQL Server is a relational database management system developed by Microsoft. Amazon RDS for SQL Server makes it easy to set up, operate, and scale SQL Server deployments in the cloud. With Amazon RDS, you can deploy multiple editions of SQL Server including Express, Web, Standard and Enterprise, in minutes with cost-efficient and re-sizable compute capacity.

We can use Amazon CloudWatch metrics and Amazon Relational Database Service (Amazon RDS) event notifications to monitor different metrics and events.

In this post, I have created to receive Amazon Simple Notification Service (Amazon SNS) notifications for your RDS SQL Server instances - online or offline.

Please visit my GitHub Repository for RDS articles on various topics being updated on constant basis.

Let’s get started!

Objectives:

1. Create RDS for SQL Server

2. Publish Amazon RDS for SQL Server error logs to CloudWatch.

3. Create filter patterns for offline or online databases

4. Create alarms for the filtered metrics

5. Test the solution

Pre-requisites:

  • AWS user account with admin access, not a root account.
  • IAM Role

Resources Used:

Amazon RDS for Microsoft SQL Server
CloudWatch
Amazon Simple Notification Service

Steps for implementation to this project:

1. Create RDS for SQL Server

1
Image description
2

Image description
3

Image description
4

Image description

5

Image description
6

Image description
7

Image description
8

Image description
9

Image description
10

Image description
11

Image description
12

Image description
13

Image description

  • Wait for 4-5 min to finish creating the database.

Image description

2. Publish Amazon RDS for SQL Server error logs to CloudWatch.

1

  • Click on database-1

Image description
2

  • Click on Modify

Image description
3

  • In the Log exports section, choose the logs that you want to start publishing to CloudWatch.
  • Select Error log

Image description
4

Image description

5

Image description

3. Create filter patterns for offline or online databases

1

  • Open the CloudWatch console and from the Logs section, choose Log groups.
  • Select the Amazon RDS for SQL Server error log of your DB instance (/aws/rds/instance/database-1/error).

Image description

  • Under Actions, Choose Metric filters and choose Create metric filter.

Image description

  • For Filter pattern, type OFFLINE.

Image description

  • You can Test pattern using following steps:
  • Select log data to test from the dropdown in Test pattern section (in our case it is database-1.node1)
  • Click Test pattern
  • Test pattern will only work if one of the databases is in OFFLINE state and corresponding entry is available in the error log.
  • In our case, the database-1 is online, so nothing in Results section
    Image description

  • Next

  • For Filter name, Metric namespace, and Metric name, enter OFFLINE Database(s).

  • For Metric value, enter 1.

Image description

Image description

  • Choose Next.
  • Choose Create metric filter.

Image description

  • Follow the same steps to create a metric filter for ONLINE databases. Adjust the filter pattern ONLINE, filter name Database(s) are ONLINE, metric namespace Database(s) are ONLINE, and metric name Database(s) are ONLINEaccordingly.

Image description

Image description

  • Click Next
  • Choose Create metric filter.

Image description

Image description

4. Create alarms for the filtered metrics

  • To create alarms for our filtered metrics, complete the following steps:

  • Select the filter OFFLINE Database(s) and choose Create alarm

Image description

  • Enter a metric name OFFLINE Database(s)
  • Choose Minimum for Statistic and set Period to 30 Seconds

Image description

  • For Threshold type, select Static.
  • Select Greater and enter 0.
  • Next

Image description

  • Select In alarm for Alarm state trigger.
  • For Send a notification to the following SNS topic, select Create new topic and enter a topic name OFFLINE-SQLDB-SNS-TOPIC.
  • Enter a valid email for receiving the notifications.
  • Choose Create topic, then
  • choose Next.

Image description

  • For Alarm name enter a name OFFLINE-ALARM.
  • Review the configuration, then choose Create alarm.

Image description

  • Now that the SNS topic is created, make sure you confirm the subscription by choosing the Confirm subscription link in the email.

Image description

Image description

Image description

Image description

  • Repeat the same steps to create alarms for ONLINE databases and adjust the conditions accordingly.
  • ONLINE-ALARM created

Image description

5. Test the solution

Image description

Image description

Cleanup

  • delete CloudWatch log group
  • delete RDS database instance
  • delete SNC topic and subscription

What we have done so far

I have demonstrated how to use metric filters to scan through Amazon RDS for SQL Server error logs and set up alerts as per your requirements.

Top comments (0)