Set up and use Log Analytics (OMS) with a multi-tenant Azure SQL Database SaaS app

In this tutorial, you set up and use Log Analytics (OMS) for monitoring elastic pools and databases. This tutorial builds on the Performance Monitoring and Management tutorial. It shows how to use Log Analytics to augment the monitoring and alerting provided in the Azure portal. Log Analytics is suitable for monitoring and alerting at scale because it supports hundreds of pools and hundreds of thousands of databases. It also provides a single monitoring solution, which can integrate monitoring of different applications and Azure services, across multiple Azure subscriptions.

In this tutorial you learn how to:

  • Install and configure Log Analytics (OMS)
  • Use Log Analytics to monitor pools and databases

To complete this tutorial, make sure the following prerequisites are completed:

See the Performance Monitoring and Management tutorial for a discussion of the SaaS scenarios and patterns, and how they affect the requirements on a monitoring solution.

Monitoring and managing performance with Log Analytics (OMS)

For SQL Database, monitoring and alerting is available on databases and pools. This built-in monitoring and alerting is resource-specific and convenient for small numbers of resources, but is less well suited to monitoring large installations or for providing a unified view across different resources and subscriptions.

For high-volume scenarios Log Analytics can be used. This is a separate Azure service that provides analytics over emitted diagnostic logs and telemetry gathered in a log analytics workspace, which can collect telemetry from many services and be used to query and set alerts. Log Analytics provides a built-in query language and data visualization tools allowing operational data analytics and visualization. The SQL Analytics solution provides several pre-defined elastic pool and database monitoring and alerting views and queries, and lets you add your own ad-hoc queries and save them as needed. OMS also provides a custom view designer.

Log Analytics workspaces and analytics solutions can be opened both in the Azure portal and in OMS. The Azure portal is the newer access point but may be behind the OMS portal in some areas.

Create data by starting the load generator

  1. In the PowerShell ISE, open Demo-PerformanceMonitoringAndManagement.ps1. Keep this script open as you may want to run several of the load generation scenarios during this tutorial.
  2. If you have fewer than five tenants, provision a batch of tenants to provide a more interesting monitoring context:

    1. Set $DemoScenario = 1, Provision a batch of tenants
    2. To run the script, press F5.
  3. Set $DemoScenario = 2, Generate normal intensity load (approx. 40 DTU).

  4. To run the script, press F5.

Get the Wingtip Tickets SaaS Database Per Tenant application scripts

The Wingtip Tickets SaaS Multi-tenant Database scripts and application source code are available in the WingtipTicketsSaaS-DbPerTenant GitHub repo. Check out the general guidance for steps to download and unblock the Wingtip Tickets SaaS scripts.

Installing and configuring Log Analytics and the Azure SQL Analytics solution

Log Analytics is a separate service that needs to be configured. Log Analytics collects log data and telemetry and metrics in a log analytics workspace. A workspace is a resource, just like other resources in Azure, and must be created. While the workspace doesn’t need to be created in the same resource group as the application(s) it is monitoring, this often makes the most sense. For the Wingtip Tickets SaaS Database Per Tenant SaaS app, this enables the workspace to be easily deleted with the application by deleting the resource group.

  1. In the PowerShell ISE, open ...\Learning Modules\Performance Monitoring and Management\Log Analytics\Demo-LogAnalytics.ps1.
  2. To run the script, press F5.

At this point, you should be able open Log Analytics in the Azure portal (or the OMS portal). It takes a few minutes for telemetry to be collected in the Log Analytics workspace and to become visible. The longer you leave the system gathering data the more interesting the experience is. Now's a good time to grab a beverage - just make sure the load generator is still running!

Use Log Analytics and the SQL Analytics solution to monitor pools and databases

In this exercise, open Log Analytics and the OMS portal to look at the telemetry being gathered for the databases and pools.

  1. Browse to the Azure portal and open Log Analytics by clicking More services, then search for Log Analytics:

    open log analytics

  2. Select the workspace named wtploganalytics-<USER>.

  3. Select Overview to open the Log Analytics solution in the Azure portal. overview-link

    Important

    It may take a couple of minutes before the solution is active. Be patient!

  4. Click on the Azure SQL Analytics tile to open it.

    overview

    analytics

  5. The view in the solution blade scrolls sideways, with its own scroll bar at the bottom (refresh the blade if needed).

  6. Explore the various views by clicking on them or on individual resources to open a drill-down explorer, where you can use the time-slider in the top left or click on a vertical bar to focus in on a narrower time slice. With this view, you can select individual databases or pools to focus on specific resources:

    chart

  7. Back on the solution blade, if you scroll to the far right you will see some saved queries that you can click on to open and explore. You can experiment with modifying these, and save any interesting queries you produce, which you can then reopen and use with other resources.

  8. Back on the Log Analytics workspace blade, select OMS Portal to open the solution there.

    oms

  9. In the OMS portal, you can configure alerts. Click on the alert portion of the database DTU view.

  10. In the Log Search view that appears you will see a bar graph of the metrics being represented.

    log search

  11. If you click on Alert in the toolbar, you will be able to see the alert configuration and can change it.

    add alert rule

The monitoring and alerting in Log Analytics and OMS is based on queries over the data in the workspace, unlike the alerting on each resource blade, which is resource-specific. Thus, you can define an alert that looks over all databases, say, rather than defining one per database. Or write an alert that uses a composite query over multiple resource types. Queries are only limited by the data available in the workspace.

Log Analytics for SQL Database is charged for based on the data volume in the workspace. In this tutorial, you created a Free workspace, which is limited to 500MB per day. Once that limit is reached data is no longer added to the workspace.

Next steps

In this tutorial you learned how to:

  • Install and configure Log Analytics (OMS)
  • Use Log Analytics to monitor pools and databases

Tenant analytics tutorial

Additional resources