| title | description | services | ms.service | ms.subservice | ms.custom | ms.devlang | ms.topic | author | ms.author | ms.reviewer | manager | ms.date |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
Use Log Analytics with a SQL Database multitenant app | Microsoft Docs |
Set up and use Log Analytics with a multitenant Azure SQL Database SaaS app |
sql-database |
sql-database |
scenario |
conceptual |
stevestein |
sstein |
billgib |
craigg |
04/01/2018 |
In this tutorial, you set up and use Azure Log Analytics to monitor 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 supports monitoring thousands of elastic pools and hundreds of thousands of databases. Log Analytics 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:
[!div class="checklist"]
- Install and configure Log Analytics.
- Use Log Analytics 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.
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 Log Analytics for monitoring and alerting. Log Analytics is a separate Azure service that enables analytics over diagnostic logs and telemetry that's gathered in a workspace from potentially many services. Log Analytics 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. Log Analytics 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.
-
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.
-
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.
-
Now start the load generator to run a simulated load on all the tenants.
a. Set $DemoScenario = 2, Generate normal intensity load (approx. 30 DTU).
b. To run the script, press F5.
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.
Log Analytics is a separate service that must be configured. Log Analytics 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.
- In the PowerShell ISE, open ..\WingtipTicketsSaaS-MultiTenantDb-master\Learning Modules\Performance Monitoring and Management\Log Analytics\Demo-LogAnalytics.ps1.
- To run the script, press F5.
Now you can open Log Analytics 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.
In this exercise, open Log Analytics in the Azure portal to look at the telemetry gathered for the databases and pools.
-
Browse to the Azure portal. Select All services to open Log Analytics. Then, search for Log Analytics.
-
Select the workspace named wtploganalytics-<user>.
-
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.
-
Select the Azure SQL Analytics tile to open it.
-
The views in the solution scroll sideways, with their own inner scroll bar at the bottom. Refresh the page if necessary.
-
To explore the summary page, select the tiles or individual databases to open a drill-down explorer.
-
Change the filter setting to modify the time range. For this tutorial, select Last 1 hour.
-
Select a single database to explore the query usage and metrics for that database.
-
To see usage metrics, scroll the analytics page to the right.
-
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.
-
Select a pool. On the pool page that opens, scroll to the right to see the pool metrics.
-
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 Log Analytics 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 Log Analytics to query and set alerts, see Work with alert rules in Log Analytics.
Log Analytics 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.
In this tutorial you learned how to:
[!div class="checklist"]
- Install and configure Log Analytics.
- Use Log Analytics to monitor pools and databases.
Try the Tenant analytics tutorial.