Table of Contents

1. Challenge

   Prepare the Environment

   1. 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

   2. Provision a Log Analytics instance in the West US region.

2. Challenge

   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. ​

   1. Implement security auditing and send logs to a newly created storage account.
   2. 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.
   3. Create a chart for Avg DTU Limit and Avg Data space used.
   4. Create a diagnostic setting to send logs for errors, timeouts, and basic metrics to Log Analytics.
3. Challenge

   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 , MetricName
   

   Note: 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.

4. Challenge

   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.

   1. 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() );
      

   2. Find your queries in the audit logs.

   3. Find your logs in your newly created storage account.