Skip to main content

Configuring Alerts for Azure SQL

Hands-On Lab

 

Photo of James Lee

James Lee

Training Architect

Length

01:00:00

Difficulty

Intermediate

Azure gives us the ability to alert on several metrics available in our Azure SQL environment. With a few clicks, it can notify you about critical changes or performance issues with your SQL databases, server, data warehouses and more. In this hands-on lab, we will create an alert for our SQL database alerting us via email of an export of our pre-deployed database.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Configuring Alerts for Azure SQL

Introduction

Azure gives us the ability to alert on several metrics available in our Azure SQL environment. With a few clicks, it can notify you about critical changes or performance issues with your SQL databases, server, data warehouses and more. In this hands-on lab, we will create an alert for our SQL database alerting us via email of an export of our pre-deployed database.

Solution

Log in to the Azure Portal using the credentials provided on the lab instructions page.

Log In to the Azure Portal and Create a Storage Account

  1. Click on All resources in the navigation hub menu and familiarize yourself with the Azure resources that have been provisioned, including:
    • One Azure SQL database
    • One Azure SQL server
  2. Copy the five-character lab code suffix (the five characters after the hyphen in the database and database server names) into a text file. We'll need the suffix for the storage account creation.
  3. Click Storage accounts in the left-hand menu.
  4. Click + Add, and create the storage account with the following parameters:
    • Subscription: Leave as default
    • Resource group: The listed resource group
    • Storage account name: saXXXXX, where XXXXX is the five-character lab suffix you previously copied
    • Location: (US) West US
    • Performance: Standard
    • Account kind: StorageV2 (general purpose v2)
    • Replication: Locally-redundant storage (LRS)
    • Access tier: Leave as default
  5. Click Review + Create.
  6. Click Create.

Configure the Azure SQL Database for Alerting

  1. Click All resources in the left-hand menu.
  2. Click the listed SQL server.
  3. In the sqls-XXXXX blade, click Firewalls and virtual networks.
  4. Set Allow access to Azure services to On.
  5. Click Save.

Create an Alert Rule and Action Group for the SQL Database

  1. Click All resources in the left-hand menu.
  2. Click the listed SQL database.

Configure Alert Rule

  1. Click Alerts in the database blade menu.
  2. Click + New alert rule at the top of the pane.
  3. In the Create rule dialog, leave the Resource as sqls-XXXXX/sqldb-XXXXX.
  4. Under Condition, click Add.
  5. In the Monitor service dropdown box, select Activity Log - Administrative, then click Export an existing database. (Microsoft.Sql/servers/databases).
  6. Click Done.

Configure Action Group

  1. Under Actions, click Create action group.
  2. In the Add action group dialog, create an action group with the following parameters:
    • Action group name: AGroup1
    • Short name: AGroup1
    • Subscription: Leave as default.
    • Resource group: Change to the resource group for the lab.
    • Actions:
      • Action name: Emailme
      • Action type: Email/SMS/Push/Voice
  3. In the Email/SMS/Push/Voice dialog, check the box next to Email and enter your email address into the Email text box.
  4. Click OK to exit the dialog.
  5. Click OK to create the action group. You will be returned to the Create rule dialog.

> NOTE: Once the action group has been created, you will receive an email stating you have been added as a member of the action group.

Finalize Alert Rule

  1. Back in the Create rule pane, enter an Alert rule name (e.g., "SQL Database Exported"), and then press Tab.
  2. A green checkbox will appear next to the alert rule name, and a Create alert rule button will appear at the bottom of the page. Click this button to create the alert rule.
  3. Check your email to make sure you received the message confirming you are now part of the action group.

> NOTE: Wait approximately five minutes for the alert rule to become enabled before proceeding to the next objective.

Export the SQL Database to Trigger the Alert

  1. Click Overview at the top of the blade menu.
  2. Click Export to trigger an export of the SQL database.
  3. In the Export database dialog, configure the export with the following parameters:
    • File name: Leave as default.
    • Subscription: Leave as default.
    • Storage: Click and select the following options:
      • Storage account: Click the storage account we created in the first objective
      • Container: Click + Container and create a new blob:
        • Name: sqlexport
        • Public access level: Private (no anonymous access).
        • Click OK.
        • Back in the Containers dialog, click the container and click Select.
    • Authentication type: Cannot be changed
    • Server admin login: azureadmin
    • Password: LA!2019!Lab1
  4. Click OK.
  5. Click Activity log in the left-hand menu to see what's happening.

Monitor Email for the Incoming Alert

Open your email client and wait for an email message from Microsoft Azure with the alert details. You may receive more than one email message, as multiple entries with Export an existing database (Microsoft.Sql/servers/databases) will be written to the activity log.

Conclusion

Congratulations on successfully completing this hands-on lab!