Set up and use Azure Monitor logs with a multitenant Azure SQL Database SaaS app

Applies to: Azure SQL Database

In this tutorial, you set up and use Azure Monitor logs to monitor elastic pools and databases. This tutorial builds on the Performance monitoring and management tutorial. It shows how to use Azure Monitor logs to augment the monitoring and alerting provided in the Azure portal. Azure Monitor logs supports monitoring thousands of elastic pools and hundreds of thousands of databases. Azure Monitor logs provides a single monitoring solution, which can integrate monitoring of different applications and Azure services across multiple Azure subscriptions.

Note

This article was recently updated to use the term Azure Monitor logs instead of Log Analytics. Log data is still stored in a Log Analytics workspace and is still collected and analyzed by the same Log Analytics service. We are updating the terminology to better reflect the role of logs in Azure Monitor. See Azure Monitor terminology changes for details.

In this tutorial you learn how to:

• Install and configure Azure Monitor logs.
• Use Azure Monitor logs to monitor pools and databases.

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

• The Wingtip Tickets SaaS database-per-tenant app is deployed. To deploy in less than five minutes, see Deploy and explore the Wingtip Tickets SaaS database-per-tenant application.
• Azure PowerShell is installed. For more information, see Get started with Azure PowerShell.

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

Monitor and manage database and elastic pool performance with Azure Monitor logs

For Azure SQL Database, monitoring and alerting is available on databases and pools in the Azure portal. This built-in monitoring and alerting is convenient, but it's also resource-specific. That means it's less well suited to monitor large installations or provide a unified view across resources and subscriptions.

For high-volume scenarios, you can use Azure Monitor logs for monitoring and alerting. Azure Monitor is a separate Azure service that enables analytics over logs gathered in a workspace from potentially many services. Azure Monitor logs provides a built-in query language and data visualization tools that allow operational data analytics. The SQL Analytics solution provides several predefined elastic pool and database monitoring and alerting views and queries. Azure Monitor logs also provides a custom view designer.

OMS workspaces are now referred to as Log Analytics workspaces. Log Analytics workspaces and analytics solutions open in the Azure portal. The Azure portal is the newer access point, but it might be what's behind the Operations Management Suite portal in some areas.

Create performance diagnostic data by simulating a workload on your tenants

1. In the PowerShell ISE, open ..\WingtipTicketsSaaS-MultiTenantDb-master\Learning Modules\Performance Monitoring and Management\Demo-PerformanceMonitoringAndManagement.ps1. Keep this script open because you might want to run several of the load generation scenarios during this tutorial.

2. If you haven't done so already, provision a batch of tenants to make the monitoring context more interesting. This process takes a few minutes.

   a. Set $DemoScenario = 1, Provision a batch of tenants.

   b. To run the script and deploy an additional 17 tenants, press F5.

3. Now start the load generator to run a simulated load on all the tenants.

   a. Set $DemoScenario = 2, Generate normal intensity load (approximately 30 DTU).

   b. To run the script, press F5.

Get the Wingtip Tickets SaaS database-per-tenant application scripts

The Wingtip Tickets SaaS multitenant database scripts and application source code are available in the WingtipTicketsSaaS-DbPerTenant GitHub repo. For steps to download and unblock the Wingtip Tickets PowerShell scripts, see the general guidance.

Install and configure Log Analytics workspace and the Azure SQL Analytics solution

Azure Monitor is a separate service that must be configured. Azure Monitor logs collects log data, telemetry, and metrics in a Log Analytics workspace. Just like other resources in Azure, a Log Analytics workspace must be created. The workspace doesn't need to be created in the same resource group as the applications it monitors. Doing so often makes the most sense though. For the Wingtip Tickets app, use a single resource group to make sure the workspace is deleted with the application.

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

Now you can open Azure Monitor logs in the Azure portal. It takes a few minutes to collect telemetry in the Log Analytics workspace and to make it visible. The longer you leave the system gathering diagnostic data, the more interesting the experience is.

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

In this exercise, open Log Analytics workspace in the Azure portal to look at the telemetry gathered for the databases and pools.

1. Browse to the Azure portal. Select All services to open Log Analytics workspace. Then, search for Log Analytics.

   

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

3. Select Overview to open the log analytics solution in the Azure portal.

   

   Important

   It might take a couple of minutes before the solution is active.

4. Select the Azure SQL Analytics tile to open it.

   

5. The views in the solution scroll sideways, with their own inner scroll bar at the bottom. Refresh the page if necessary.

6. To explore the summary page, select the tiles or individual databases to open a drill-down explorer.

   

7. Change the filter setting to modify the time range. For this tutorial, select Last 1 hour.

   

8. Select an individual database to explore the query usage and metrics for that database.

   

9. To see usage metrics, scroll the analytics page to the right.

   

10. Scroll the analytics page to the left, and select the server tile in the Resource Info list.

    

    A page opens that shows the pools and databases on the server.

    

11. Select a pool. On the pool page that opens, scroll to the right to see the pool metrics.

    

12. Back in the Log Analytics workspace, select OMS Portal to open the workspace there.

    

In the Log Analytics workspace, you can explore the log and metric data further.

Monitoring and alerting in Azure Monitor logs are based on queries over the data in the workspace, unlike the alerting defined on each resource in the Azure portal. By basing alerts on queries, you can define a single alert that looks over all databases, rather than defining one per database. Queries are limited only by the data available in the workspace.

For more information on how to use Azure Monitor logs to query and set alerts, see Work with alert rules in Azure Monitor logs.

Azure Monitor logs for SQL Database charges based on the data volume in the workspace. In this tutorial, you created a free workspace, which is limited to 500 MB per day. After that limit is reached, data is no longer added to the workspace.

Next steps

In this tutorial you learned how to:

• Install and configure Azure Monitor logs.
• Use Azure Monitor logs to monitor pools and databases.

Try the Tenant analytics tutorial.

Additional resources

• Additional tutorials that build on the initial Wingtip Tickets SaaS database-per-tenant application deployment
• Azure Monitor logs