This hands-on lab is designed to teach you the basics of Azure Monitor. You will explore how Azure Monitor works in a SQL database through the implementation of Azure Monitor and the configuration of alerts, security logs, log analytics queries, and basic metric charts. This is designed as a beginner-friendly lab with little to no prior experience required. We will not be diving deep into Log Analytics or query language but will instead focus on the implementation aspects of Azure Monitor.
Learning Objectives
Successfully complete this lab by achieving the following learning objectives:
- Prepare the Environment
Provision a SQL database.
- Create a new server in the US West region:
- Basic (5 DTU), 2 GB data size
- Public endpoint with Azure services and client IP address added
- Sample existing data
- No trial
- Create a new server in the US West region:
Provision a Log Analytics instance in the West US region.
- Implement Monitoring in SQL Database
Now that we have our environment provisioned, let’s go into SQL Database and implement monitoring. In order to do that, we will implement security audit logs, create an alert rule, implement diagnostic settings, and create charts for Avg DTU Limit and Avg Data Space.
- Implement security auditing and send logs to a newly created storage account.
- Create an alert rule.
- When DTU used goes above 70% (Static, with a 5-minute aggregation and 1-minute frequency).
- Create an action group with an email notification.
- Create a chart for Avg DTU Limit and Avg Data space used.
- Create a diagnostic setting to send logs for errors, timeouts, and basic metrics to Log Analytics.
- Review and Create a Log Query
Create some activity within SQL Database and then learn how to look at logs and utilize the query functionality.
Note: For the Azure Log Analytics page to work properly, you must enable cookies for the site.
Run the following query in logs:
// Avg CPU usage // Avg CPU usage in the last hour by resource name. //consistently high averages could indicate a customer needs to move to a larger SKU AzureMetrics | where ResourceProvider == "MICROSOFT.SQL" // /DATABASES | where TimeGenerated >= ago(60min) | where MetricName in ('cpu_percent') | parse _ResourceId with * "/microsoft.sql/servers/" Resource // subtract Resource name for _ResourceId | summarize CPU_Maximum_last15mins = max(Maximum), CPU_Minimum_last15mins = min(Minimum), CPU_Average_last15mins = avg(Average) by Resource , MetricNameNote: This query may not show immediately in your lab. If you run the query and get no results, you likely need to let more time elapse in the environment for Azure to pick up on the activity.
- Review Audit Logs and Find Storage Logs
Run sample queries in the query editor, and then look for them in the security audit logs and in the Azure storage.
Run 2 sample queries using the query editor:
SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM SalesLT.ProductCategory pc JOIN SalesLT.Product p ON pc.productcategoryid = p.productcategoryid;INSERT INTO [SalesLT].[Product] ( [Name] , [ProductNumber] , [Color] , [ProductCategoryID] , [StandardCost] , [ListPrice] , [SellStartDate] ) VALUES ('myNewProduct' ,123456789 ,'NewColor' ,1 ,100 ,100 ,GETDATE() );Find your queries in the audit logs.
Find your logs in your newly created storage account.
