Monitoring Azure SQL Data Sync using OMS Log Analytics

Azure SQL Data Sync is a solution which enables customers to easily synchronize data either bidirectionally or unidirectionally between multiple Azure SQL databases and/or on-premises SQL Databases.

Previously, users had to manually look at SQL Azure Data Sync in the Azure portal or use PowerShell/RestAPI’s to pull the log and detect errors and warnings. By following the steps in this blog post, Data Sync users can configure a custom solution which will greatly improve the Data Sync monitoring experience. Remember, this solution can and should be customized to fit your scenario.

Monitoring dashboard for all your Sync Groups

Users will no longer need to manually look through the logs of each Sync Group individually to look for issues. Users will be able to monitor all their Sync Groups from any of their subscriptions in one place using a custom OMS view. With this view users will be able to surface the information that matters to them.

Automated email notifications

Users will no longer need to manually look at the log in the Azure Portal or through PowerShell/RestAPI’s. By leveraging OMS Log Analytics we can create alerts which will go directly to the emails of those that need to see them in the event of an error.

How do you set this up?

You can implement your custom Data Sync OMS monitoring solution in less than an hour by following the steps below and making minimal changes to the given samples.

You’ll need to configure 3 components:

• PowerShell Runbook to feed Data Sync Log Data to OMS
• OMS Log Analytics alert for email notifications
• OMS view for monitoring

Download the 2 samples:

• Data Sync Log PowerShell Runbook
• Data Sync Log OMS View

Prerequisites:

• Azure Automation Account
• Log Analytics linked with OMS Workspace

PowerShell Runbook to get Data Sync Log

We will use a PowerShell runbook hosted in Azure Automation to pull the Data Sync log data and send it to OMS. A sample script is included. As a prerequisite you need to have an Azure Automation Account. You will need to create a runbook and the schedule for running it.

To create the runbook:

1. Under your Azure Automation Account, click the Runbooks tab under Process Automation.
2. Click Add a Runbooks at the top left corner of the Runbooks blade.
3. Click Import an existing Runbook.
4. Under Runbook file use the given “DataSyncLogPowerShellRunbook” file. Set the Runbook type as “PowerShell”. You can use any name you want.
5. Click Create. You now have your runbook.
6. Under your Azure Automation Account, click the Variables tab under Shared Resources.
7. Click Add a variable at the top left side of the variables blade. We need to create a variable to store the last execution time for the runbook. If you have multiple runbooks you'll need one variable for each.
8. Set the name as “DataSyncLogLastUpdatedTime” and Type as DateTime.
9. Select the Runbook and click the edit button at the top of the blade.
10. Make the required changes (details in the script)
    • Azure information
    • Sync Group information
    • OMS information (find this information at OMS Portal -> Settings -> Connected Sources)
11. Run the runbook in the test pane and check to make sure it’s successful.
    • Note: If you have errors make sure you have the newest PowerShell Module installed. You can do this in the Modules Gallery in your Automation Account.
12. Click Publish

To schedule the runbook:

1. Under your runbook, click the Schedules tab under Resources.
2. Click Add a Schedule in the Schedules blade.
3. Click Link a Schedule to your runbook.
4. Click Create a new schedule.
5. Set Recurrence to Recurring and set the interval you’d like. You should use the same interval here, in the script, and in OMS.
6. Click Create

To monitor if your automation is running:

1. Under Overview for your automation account, find the Job Statistics view under Monitoring. Pin this to your dashboard for easy viewing.
2. Successful runs of the runbook will show as “Completed” and failed runs will show up as “Failed”.

OMS log reader alert for email notifications

We will use an OMS Log Analytics to create an alert. As a prerequisite you need to have a Log Analytics linked with an OMS workspace.

1. In the OMS portal click on Log Search towards the top left.
2. Create a query to select the errors and warnings by sync group within the interval you are using.
   • Type=DataSyncLog_CL LogLevel_s!=Success| measure count() by SyncGroupName_s interval 60minute
3. After running the query click the bell that says Alert.
4. Under Generate alert based on select Metric Measurement.
   • Set the Aggregate Value to Greater than.
   • After greater than, use the threshold you’d like to set before you receive notifications.
   • Transient errors are expected in Data Sync. We recommend that you set the threshold to 5 to reduce noise.
5. Under Actions set Email notification to “Yes”. Enter the desired recipients.
6. Click Save. You will now receive email notifications based on errors.

OMS view for monitoring

We will create an OMS view to visually monitor all the sync groups. The view includes a few main components:

• The Overview tile shows how many errors, successes, and warnings all your sync groups have.
• Tile for all sync groups which shows the amount of errors and warnings per sync group that has them. Groups with no issues will not appear.
• Tile for each Sync Group which shows the number errors, successes, and warnings and the recent error messages.

To configure the view:

1. On the OMS home page, click the plus on the left to open the view designer.
2. Click Import on the top bar of the view designer and select the “DataSyncLogOMSView” file.
3. The given view is a sample for managing 2 sync groups. You can edit this to fit your case. Click edit and make the following changes.
   • Create new “Donut & List” objects from the Gallery as needed.
   • In each tile update the queries with your information. 
     • On all tiles, change the TimeStamp_t interval as desired
     • On the Sync Group specific tiles, update the Sync Group names.
   • In each tile update the titles as needed.
4. Click Save and your view is ready.

Cost

In most cases this solution will be free.

Azure Automation: There may be a cost incurred with the Azure Automation Account depending on your usage. The first 500 minutes of job run time per month is free. In most cases you will use less than 500 minutes for this solution. To avoid charges, schedule the runbook to run at an interval of 2 hours or more.

OMS Log Analytics: There may be a cost associated with OMS depending on your usage. The free tier includes 500 MB of ingested data per day. In most cases this will be enough for this solution. To decrease the usage, use the failure only filtering included in the runbook. If you are using more than 500 MB per day, upgrade to the paid tier to avoid stoppage of analytics from hitting the limitation.

Code samples

• Data Sync Log PowerShell Runbook
• Data Sync Log OMS View