Diagnostic logs provide rich, frequent data about the operation of an Azure resource. Azure Monitor makes the following two types of diagnostic logs available.
– **Tenant logs:** These logs come from tenant-level services that exist outside of an Azure subscription, such as Azure Active Directory logs.
– **Resource logs**: These logs come from Azure services that deploy resources within an Azure subscription, such as network security groups or Storage accounts.
In this hands-on lab, we discuss how to make resource logs available to other Azure services such as Azure Storage and Azure Monitor.Today, we assume the role of an Azure administrator for Coder Analytics, Inc. We are currently utilizing an Azure SQL database for our production data mining software. We’ve been asked by several other groups to maintain logging data for this database.
– IT management would like to see an overview of SQL logging and metrics.
– IT security would like to maintain a copy of the logging data for archival purposes.
In this lab, we take a look at the various services where we can route this monitoring data. We then generate sample data and view it in each of these services.
Successfully complete this lab by achieving the following learning objectives:
- Log On to the Azure Portal and Prepare Azure Resources
Log into the Azure Portal with the credentials provided. Click on All Resources in the navigation hub menu and become familiar with the Azure resources that have been provisioned. These include:
- One Azure SQL database.
- One Azure SQL database server.
- One Log Analytics workspace.
- One Azure Storage account.
Note the naming convention of the SQL resources, particularly the five-character lab code suffix. This is unique for all labs.
Enable SQL Firewall Access to the Virtual Network
Click on the SQL server named sqls-XXXXX, where XXXXX is a five-character unique lab ID for this lab. Inside the SQL server blade, click on Firewalls and virtual networks. Click the + Add client IP button to add the IP of the local workstation to the SQL server firewall. Click Save to save the changes.
Deploy Azure SQL Analytics (Preview)
In the Azure Portal, search for "Azure SQL Analytics" in the search bar at the top of the page. Deploy the solution in the log analytics workspace named laws-XXXXX, where XXXXX is a five-character unique lab ID for this lab.
- Configure Diagnostics Settings for the Azure SQL Database
In the Azure Portal, under All resources, click the entry for the SQL server named sqldb-XXXXX, where XXXXX is a five-character unique lab ID for this lab.
Configure Diagnostic Settings
Click Diagnostic settings in the database blade menu. Click + Add Diagnostic setting and configure with the following settings:
- Name: SQLDiag1
- Archive to a storage account: Checked
- Subscription: Leave as default.
- Storage Account: saXXXXX, where XXXXX is a five-character unique lab ID for this lab.
- Send to Log Analytics: Checked
- Subscription: Leave as default.
- Log analytics workspace: laws-XXXXX, where XXXXX is a five-character unique lab ID for this lab.
Check all logs and metrics and set the retention to 7 days. Click Save to update the diagnostic settings.
- Query the SQL Database to Generate Metrics Data
Back in the Azure Portal, click on All Resources, then on the SQL database named sqldb-XXXXX, where XXXXX is a five-character unique lab ID for this lab. Click on Query editor (preview) in the blade navigation menu. Sign in to the Azure database using
azureadminas the login and
LA!2019!Lab1as the password.
Enter the following T-SQL query into the window and click Run to execute the query.
SELECT TOP (1000) * FROM SalesLT.SalesOrderHeader AS Header JOIN SalesLT.SalesOrderDetail AS Detail ON Detail.SalesOrderID = Header.SalesOrderID JOIN SalesLT.Product AS Product ON Detail.ProductID=Product.ProductID
Execute the query several times to generate database performance load.
NOTE: Wait about 5-10 minutes before proceeding to the next objective.
- View Logging Data in the Various Resources
In the Azure Portal, click All Resources, then on the storage account named saXXXXX, where XXXXX is a five-character unique lab ID for this lab. In the blade menu, click Containers. Notice the containers that now exist in the storage account. Explore the database logs in the containers.
Azure Log Analytics
In the Azure Portal, click All Resources, then on the log analytics workspace named laws-XXXXX, where XXXXX is a five-character unique lab ID for this lab. In the blade menu, click Solutions, then click on AzureSQLAnalytics(laws-XXXXX). In the Overview pane, click View Summary under the Azure SQL Analytics (Preview) box.
Click on the Azure SQL Database selection in the Azure SQL Analytics (Preview) box.
Review the metrics data in the log analytics solution.