Alert on SQL db replication using Application Insights

Question

How can I leverage Application Insights to monitor a database replication to SQL managed instance and alert on triggers when the database gets replicated or the last time it was replicated. Basically alert me when the database hasn't replicate for more than 12 hours.. How can I accomplish this using Azure Monitor (application insights)? The replication is coming from a third party provider, for example Netsuite replicates to us onto our SQL managed instance database; we have no control on the replication, we get it and is read only on our end.

Answer 1

HI @Razzi29,

To monitor database replication to a SQL Managed Instance using Application Insights and set up alerts for when the database hasn't replicated for more than 12 hours, you can follow these steps:

1. Set Up Application Insights: Ensure that Application Insights is properly configured for your SQL Managed Instance. This will allow you to collect telemetry data.
2. Custom Telemetry: Since the replication is managed by a third-party provider and you have no control over it, you may need to implement custom telemetry to log the last replication time. This can be done by creating a scheduled job or function that checks the last replication timestamp and sends this data to Application Insights.
3. Create an Alert Rule:
   • In Azure Monitor, navigate to Alerts.
   • Select Create and then choose Alert rule.
   • Set the scope to your Application Insights resource.
   • For the condition, you can create a log query that checks if the last replication time is older than 12 hours. You might use a query similar to:

               customEvents
          | where name == "ReplicationEvent"
          | summarize LastReplicationTime = max(timestamp)
          | where LastReplicationTime < ago(12h)
     

   • Set the action group to notify you via email or another method when the alert is triggered.

Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/monitoring-sql-managed-instance-azure-monitor?view=azuresql#alerts

Please feel free to let us know, as we are always here to help whenever you need us.

Please do not forget to "Accept the answer” and “upvote it” wherever the information provided helps you, this can be beneficial to other community members.it would be greatly appreciated and helpful to others.