Create a CPU status alert for a SQL Server on Azure

Estimated Time: 20 minutes

You have been hired as a Senior Data Engineer to help automate day to day operations of database administration. This automation is to help ensure that the databases for AdventureWorks continue to operate at peak performance as well as provide methods for alerting based on certain criteria.

📝 These exercises ask you to copy and paste T-SQL code and makes use of existing SQL resources. Please verify that the code has been copied correctly, before executing the code.

Setup environment

If your lab virtual machine has been provided and pre-configured, you should find the lab files ready in the C:\LabFiles folder. Take a moment to check, if the files are already there, skip this section. However, if you’re using your own machine or the lab files are missing, you’ll need to clone them from GitHub to proceed.

From the lab virtual machine or your local machine if one wasn’t provided, start a Visual Studio Code session.
Open the command palette (Ctrl+Shift+P) and type Git: Clone. Select the Git: Clone option.

Paste the following URL into the Repository URL field and select Enter.

 https://github.com/MicrosoftLearning/dp-300-database-administrator.git

Save the repository to the C:\LabFiles folder on the lab virtual machine or your local machine if one wasn’t provided (create the folder if it does not exist).

Setup your SQL Server in Azure

Log in to Azure and check if you have an existing Azure SQL Server instance running in Azure. Skip this section if you already have a SQL Server instance running in Azure.

From the lab virtual machine or your local machine if one wasn’t provided, start a Visual Studio Code session and navigate to the cloned repository from the previous section.
Right-click on the /Allfiles/Labs folder and select Open in Integrated Terminal.
Let’s connect to Azure using the Azure CLI. Type the following command and select Enter.
```
 az login
```
📝 Note that a browser window will open. Use your Azure credentials to log in.
Once you are logged in to Azure, it’s time to create a resource group if it doesn’t already exist, and create a SQL server and database under that resource group. Type the following command and select Enter. The script will take a few minutes to complete.
```
 cd ./Setup
 ./deploy-sql-database.ps1
```
📝 Note that by default this script will create or a resource group called contoso-rg, or use a resource whose name start with contoso-rg if it exists. By default it will also create all resources on the West US 2 region (westus2). Finally it will generate a random 12 character password for the SQL admin password. You can change these values by using one or more of the parameters -rgName, -location and -sqlAdminPw with your own values. The password will have to meet the Azure SQL password complexity requirements, at least 12 characters long, and contain at least 1 uppercase letter, 1 lowercase letter, 1 number and 1 special character.

📝 Note that the script will add your current Public IP address to the SQL server firewall rules.
Once the script has completed, it will return the resource group name, SQL server name and database name, and admin user name and password. Take note of these values as you will need them later in the lab.

Create an alert when a CPU exceeds an average of 80 percent

From the lab virtual machine or your local machine if one wasn’t provided, start a browser session and navigate to https://portal.azure.com. Connect to the Portal using your Azure credentials.
From the Azure Portal, in the search bar at the top of the Azure portal, type SQL databases, and select SQL databases. Select the AdventureWorksLT database name listed.
On the main blade for the AdventureWorksLT database, navigate down to the monitoring section. Select Alerts.
Select Create alert rule.
In the Create an alert rule page, select CPU percentage.
In the Alert logic section, select Static for the Threshold type. Then check that the Aggregation type is Average and that the Value is property is Greater than. Then in Threshold enter a value of 80. Review the Check every and lookback period values.
Select Next: Actions >.
In the Actions tab, select Create action group.
On the Action Group screen, type emailgroup in the Action group name and Display name fields, and then select Next: Notifications.
On the Notifications tab, enter the following information:
- Notification type: Email/SMS message/Push/Voice
  
  📝 When you select this option, a Email/SMS message/Push/Voice flyout will appear. Check the Email property and type the Azure username you signed in with. Select OK.
- Name: DemoLab
Select Review + create, then select Create.
Back in the Create an alert rule page, select Next: Details and give the alert rule a unique name.
Select Review + create, then select Create.
With the alert in place, if the CPU usage on average exceeds 80%, an email is sent out.

Cleanup Resources

If you are not using the Azure SQL Server for any other purpose, you can clean up the resources you created in this lab.

Delete the Resource Group

If you created a new resource group for this lab, you can delete the resource group to remove all resources created in this lab.

In the Azure portal, select Resource groups from the left navigation pane or search for Resource groups in the search bar and select it from the results.
Go into the resource group that you created for this lab. The resource group will contain the Azure SQL Server and other resources created in this lab.
Select Delete resource group from the top menu.
In the Delete resource group dialog, type the name of the resource group to confirm and select Delete.
Wait for the resource group to be deleted.
Close the Azure portal.

Delete the Lab resources only

If you didn’t create a new resource group for this lab, and want to leave the resource group and its previous resources intact, you can still delete the resources created in this lab.

In the Azure portal, select Resource groups from the left navigation pane or search for Resource groups in the search bar and select it from the results.
Go into the resource group that you created for this lab. The resource group will contain the Azure SQL Server and other resources created in this lab.
Select all the resources prefixed with the SQL Server name you previously specified in the lab.
Select Delete from the top menu.
In the Delete resources dialog, type delete and select Delete.
Select Delete again to confirm the deletion of the resources.
Wait for the resources to be deleted.
Close the Azure portal.

You have successfully completed this lab.

Alerts can send you an email or call a web hook when some metric (for example database size or CPU usage) reaches a threshold you define. You’ve just seen how you can easily configure alerts for Azure SQL Databases.