How to scan thru Amazon RDS for SQLServer error logs using metric filters
Tags: ec2, rds, vpc, cloudwatch
Published: 2024-03-07 01:48:22

**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](https://github.com/awsmine/RDS_projects)** 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html)**
**[CloudWatch](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/WhatIsCloudWatch.html)**
**[Amazon Simple Notification Service](https://docs.aws.amazon.com/sns/latest/dg/welcome.html)**

# Steps for implementation to this project:

## 1. Create RDS for SQL Server

**1**
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8ju8smz7m7km5jcfi39p.png)
**2**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n8k79f098nccsqm5gm7s.png)
**3**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2m4s8zhlqeqxlp4wobak.png)
**4**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hhpdbcah7y0j9lte3u11.png)

**5**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yjd6q6phv315x5902pl1.png)
**6**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cxu5j2b4tgt1thf32mu0.png)
**7**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z1g42tdr436z046vvjr1.png)
**8**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zpdm3bxcjqu9yzna7pcf.png)
**9**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lzdzb6dmudd3crd9j7ac.png)
**10**


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wiajku0sgna7thue048s.png)
**11**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vaf7kbftt8qlv5ef7hea.png)
**12**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9260t17np867lmtdk41j.png)
**13**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5jd19qe0agpjek0glsbv.png)

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


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jagrg1w0jmgqhmnisspv.png)


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

**1**
- Click on database-1

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nmzpqagzj0yukw9uco33.png)
**2**
- Click on Modify

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xxcdb9ic4728vfcgvtza.png)
**3**
- In the Log exports section, choose the logs that you want to start publishing to CloudWatch.
- Select Error log


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/si9x44qbtm8rpkuw3piq.png)
**4**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8xwi8xzkopewga0xmzjm.png)

**5**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3jilw5t4a4mevsq1h1jp.png)

## 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v7rch3sxoolb7m2b1zkf.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ibxexqhg83ohclb93z0t.png)
- For Filter pattern, type OFFLINE.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/05it8jf7sert8ck06eum.png)
- 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lli0cd4eym6cs1hd4t8t.png)

- Next
- For Filter name, Metric namespace, and Metric name, enter OFFLINE Database(s).
- For Metric value, enter 1.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qqkoahnqajv6tvgla6cd.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/quc4gpnnk8qse16yhbun.png)


- Choose Next.
- Choose Create metric filter.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4v3z4a0la49le314wk19.png)

- 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 ONLINE`accordingly.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q1jxgsmz82iazwr81c4a.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g0ckacyknv52dcrsoeb8.png)
- Click Next
- Choose Create metric filter.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/97l1hj63jffqrrtsglsp.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0u4rzhripf9sulrfdoou.png)

## 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dkz9y7zdgy69a4f0gji2.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uaqbi4g8i185gpg94upo.png)
- For Threshold type, select Static.
- Select Greater and enter 0.
- Next

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z1qhxj16iavai7rqoi89.png)

- 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p8io5vx768tv92ycpulh.png)


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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lelio25j0zy0u7j87bw1.png)



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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ccefa91swdcb7tagymfm.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u1hz6aqhiqabhps6e5h4.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d8uf9clqg6omtscqw6l4.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/if8oj4dehqnwsmn6fq96.png)


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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fq6i6oxpn5p07dy81nyo.png)

## 5. Test the solution


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m0z6a4r7c7q455106tpw.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0ucac9rz9zk7sbriryye.png)


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