AWS Database Blog

Receive SNS notifications about Amazon RDS for SQL Server when database state changes to Offline or Online

Monitoring is an important aspect for any relational database management system (RDBMS). A good monitoring setup gives us better visibility and control of our database setups. Many of our customers use Amazon CloudWatch metrics and Amazon Relational Database Service (Amazon RDS) event notifications to monitor different metrics and events.

Amazon RDS event subscription offers instance-level monitoring and alerting for Amazon RDS for SQL Server. However, it doesn’t have a direct mechanism to alert when a database goes offline or comes back online. In this post, we show you how to receive Amazon Simple Notification Service (Amazon SNS) notifications when your RDS for SQL Server instances go online or offline.

Solution overview

In this solution, we configure Amazon RDS for SQL Server to publish error logs to Amazon CloudWatch logs and set a filter to help generate alerts based on Amazon SNS whenever the database state is offline or online.

The following diagram illustrates the architecture of the solution.

The high-level steps for this solution are as follows:

  1. Publish Amazon RDS for SQL Server error logs to CloudWatch.
  2. Create filter patterns for offline or online databases
  3. Create alarms for the filtered metrics.

Prerequisites

As a prerequisite, you should have Amazon RDS for SQL Server set up, and you should be able to access CloudWatch and Amazon SNS.

Publish Amazon RDS for SQL Server error logs to CloudWatch

To publish Amazon RDS for SQL Server error logs to CloudWatch, you need to modify your DB instance. Complete the following steps:

Refer to Amazon RDS for SQL Server, Amazon CloudWatch and Amazon SNS pricing pages to understand about this solution’s costs.

  1. On the Amazon RDS console, in the navigation pane, choose Databases, then choose the DB instance that you must modify.
  2. Choose Modify.
  3. In the Log exports section, choose the logs that you want to start publishing to CloudWatch.
  4. Select Error log.
  5. (Optional) Choose Apply immediately to apply the changes immediately.
  6. On the confirmation page, review your changes. If they are correct, choose Modify DB instance to save your changes. Or choose Back to edit your changes or Cancel to cancel your changes.

Create filter patterns for offline or online databases

Now that we have published Amazon RDS for SQL Server error logs to CloudWatch, the next step is to create filter patterns. Complete the following steps:

  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 (for this post, /aws/rds/instance/sql-database-ee/error).
  2. Under Actions, Choose Metric filters and choose Create metric filter.
  3. For Filter pattern, type OFFLINE.
  4. (Optional) You can Test pattern using following steps;
    • Select log data to test from the dropdown in Test pattern section (in our case it is sql-database-ee.node2)
    • 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.

  5. Choose Next
  6. For Filter name, Metric namespace, and Metric name, enter OFFLINE Database(s).
  7. For Metric value, enter 1.
  8. Choose Next.
  9. Choose Create metric filter.
  10. Follow the same steps to create a metric filter for ONLINE databases. Adjust the filter pattern, filter name, metric namespace, and metric name accordingly.

You can customize the filter name, metric namespace, and metric name as needed for your use case.

Create alarms for the filtered metrics

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

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

  2. Enter a metric name (for this post, OFFLINE Database(s)).
  3. Choose Minimum for Statistic and set Period to 30 Seconds or any suitable value as per your use case.
  4. For Threshold type, select Static.
  5. Select Greater and enter 0.
  6. Choose Next.
  7. Select In alarm for Alarm state trigger.
  8. For Send a notification to the following SNS topic, select Create new topic and enter a topic name. (You can also use an existing topic.)
  9. Enter a valid email for receiving the notifications.
  10. Choose Create topic, then choose Next.
  11. For Alarm name, enter a name.
  12. Review the configuration, then choose Create alarm.
  13. Now that the SNS topic is created, make sure you confirm the subscription by choosing the Confirm subscription link in the email.
  14. Repeat the same steps to create alarms for ONLINE databases and adjust the conditions accordingly.

Test the solution

We now connect to an Amazon RDS for SQL Server instance using SQL Server Management Studio (SSMS).

We use database DemoDB, as shown in the following screenshot.

We take the DemoDB database offline using the Alter Database Set command.

We can verify the state of the database in SSMS, as shown in the following screenshot.

We also get a notification email to the subscribed email ID.

Now we bring DemoDB online by using rdsadmin.dbo.rds_set_database_online. Note that Alter Database db_name Set Online doesn’t work in Amazon RDS for SQL Server, and you’ll receive the following error if you try this command.

However, rdsadmin.dbo.rds_set_database_online works fine. Refer to Transitioning a Microsoft SQL Server database from OFFLINE to ONLINE for more details.

We can verify the database status using SSMS.

We also receive an email alert that the database is online.

Key considerations

We demonstrated this solution using a Single-AZ Amazon RDS for SQL Server setup. It’s important to note that, if the instance is rebooted, you won’t receive database notifications of it being online or offline. This is because SQL Server goes through the database recovery process after restart. Therefore, if you want to be notified, you can create metric filters using the Starting up database or Recovery is complete filter patterns as per your use case.

Following is a snippet from the SQL Server Error log.

2023-04-13 05:17:19.570 spid29s Starting up database 'DemoDB'.
.......
2023-04-13 05:17:21.050 spid9s Recovery is complete. This is an informational message only. No user action is required.

Clean up

To avoid incurring charges, clean up the Amazon RDS for SQL Server instance and CloudWatch logs if you used this solution in your environment for testing purposes.

To delete CloudWatch logs, you can complete the following steps;

  1. Go to the Amazon CloudWatch console
  2. From the navigation pane on the left, select Log Groups from Logs
  3. Type in database name in Log groups, for example sql-database-ee
  4. Choose Log Group(s)
  5. From Actions menu, select Delete log group(s)

You can delete a DB instance using the AWS Management Console or AWS CLI or the Amazon RDS API. Time required to delete your RDS instance depends on amount of data to be deleted, backup retention period i.e., how many backups to be deleted and whether a final snapshot needs to be taken.

To delete the Amazon RDS for SQL Server DB instance, you must follow the steps mentioned below;

  1. Go to the Amazon RDS console. Un-check ‘Enable deletion protection’ by modifying the database instance before proceeding to next step in case you turned it on, if not proceed to next step.

  2. Choose Databases in the navigation pane, and then choose the DB instance that you want to delete.
  3. Choose Delete on the Actions menu,
  4. Choose Create final snapshot to create a final DB snapshot for the DB instance.
  5. If you chose to create a final snapshot, enter a name for Final snapshot name.
  6. Choose Retain automated backups to retain automated backups,
  7. Enter delete me in the text field.
  8. Choose Delete.

Finally, delete your SNS topic and subscription.

Conclusion

In this post, we showed how you can publish Amazon RDS for SQL Server error logs to CloudWatch, create metric filters for when databases are offline or online, and send email alerts using SNS notifications and CloudWatch alarms. This solution is not limited to what we demonstrated in this post; you can use metric filters to scan through Amazon RDS for SQL Server error logs and set up alerts as per your requirements.

To learn more about Amazon RDS for SQL Server, refer to Amazon RDS for Microsoft SQL Server. If you have any comments or questions about this post, submit them in the comments section.

About the Author

Kanchan Bhattacharyya is a Senior Database Specialist Technical Account Manager at Amazon Web Services. He works with enterprise customers providing technical assistance on database operational performance and sharing database best practices. He specializes in Amazon RDS for SQL Server, Amazon RDS for PostgreSQL, Amazon RDS for MySQL and Amazon Aurora.