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

2

3

4

5

6

7

8

9

10

11

12

13

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

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

1

• Click on database-1

2

• Click on Modify

3

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

4

5

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

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

• For Filter pattern, type OFFLINE.

• 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
  

• Next

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

• For Metric value, enter 1.

• Choose Next.
• Choose Create metric filter.

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

• Click Next
• Choose Create metric filter.

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

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

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

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

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

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

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

5. Test the solution

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.