Lab 6 – Automate Resources

Estimated Time: 90 minutes

This lab can be performed from a web browser with access to the Azure portal.

Note: the Microsoft.Insights module needs to be added to your subscription in order to complete this lab. You can register by completing the following steps.

From the Azure portal, click on the cloud shell icon on the top right of the portal.

A picture containing racket, drawing, ball, woman Description automatically generated

A shell will open at the bottom of the screen. Click on PowerShell as shown below.

A screenshot of a cell phone Description automatically generated

You may be prompted to create a storage account. Click Create Storage.

A screenshot of a cell phone Description automatically generated

After the PowerShell window opens, paste in the following command and press Enter.

register-AzResourceProvider -ProviderNamespace Microsoft.Insights

A screenshot of a cell phone Description automatically generated

Lab files: The files for this lab are in the D:\LabFiles\Automate Resources folder.

Lab overview

The students will take the information gained in the lessons to configure and subsequently implement automate processes within AdventureWorks.

Lab objectives

After completing this lab, you will be able to:

  • Deploy an Azure resource from a GitHub Quickstart template

  • Configure performance metric related notifications

  • Deploy an Azure Automation Runbook to perform index maintenance in an Azure SQL Database

Scenario

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. AdventureWorks utilizes SQL Server in both Infrastructure as a Service and Platform as a Service offerings.

Exercise 1: Deploy an Azure Quickstart Template

Estimated Time: 30 minutes

Individual Exercise

The main task for this exercise are as follows:

  • Deploy an Azure resource from a GitHub Quickstart template

Task: Deploy an Azure SQL Database from a template

  1. Navigate to the following GitHub using a web browser.

    https://github.com/Azure/azure-quickstart-templates/tree/master/quickstarts/microsoft.sql/sql-database-transparent-encryption-create

    A screenshot of a cell phone Description automatically generated

    Click on azuredeploy.json, and review the file.

  2. Navigate back to the above link (click the back arrow in the top left of the browser), and click on the Deploy to Azure button. You may be prompted to login to the Azure portal. Login with your supplied credentials.

  3. You will see a screen like the one below. In order to deploy this template, you need to complete the blank fields.

    In the Resource Group field, click “Create New” and type DP300-Lab06, and then click OK. Change the region to a region near you. For the remaining fields, use the following values:

    • SQL Administrator Login: labadmin
    • SQL Administrator Login Password: Azur3Pa$$
    • Transparent Data Encryption: Enabled
    • Location: [resourceGroup().location]

    A screenshot of a cell phone Description automatically generated

  4. Click Review + create, then click Create. Your deployment will begin. You can track the status of your deployment by clicking the bell (highlighted in the screenshot below) and then clicking on the Deployment in progress link in the Notifications pane.

    A screenshot of a cell phone Description automatically generated

    Your deployment will take approximately 5-10 minutes to deploy. If you have clicked on the link above, you will be able to track your deployment.

    A screenshot of a cell phone Description automatically generated

  5. Upon completion, the screen will update with a link to your newly created resource.

    A screenshot of a cell phone Description automatically generated

    Click on the Go to resource link. You will be taken to the Azure Resource Group your deployment just created. You should see both a SQL server and a SQL database in the Resource group. Make note of the name of your SQL server, as you will it in the next exercise.

    A screenshot of a social media post Description automatically generated

Exercise 2: Configure Performance Metrics Based Alerts

Estimated Time: 30 minutes

Individual Exercise

The main task for this exercise is as follows:

  • Configure performance metric related notifications

Task: Creating an alert when CPU exceeds an average of 80 percent.

  1. Navigate to portal.azure.com if you are not already there. You may need to login again. In the search bar at the top of the Azure Portal, type SQL, and click on SQL databases. Click on the database name: sample-db-with-tde.

    A screenshot of a cell phone Description automatically generated

  2. On the main blade for the sample-db-with-tde database, navigate down to the monitoring section.

  3. Click on Alerts as shown below. You’ll see the screen below. Click on + Create, and then on Alert rule.

    A screenshot of a cell phone Description automatically generated

  4. You will notice that the resource is already populated for you with the SQL Server you created. If the Select a signal window is already open then proceed to the next step. Under the Condition section, click Add condition.

image

  1. In the Configure signal logic fly out menu, select CPU percentage.

    Picture 14

  2. Supply a threshold value of 80. Click Done.

    Picture 15

  3. On the Actions tab, under the Action group section, select Create action group.

  4. On the Create an action group screen, type:
    • Action group name: emailgroup
    • Display name: emailgroup

    A screenshot of a social media post Description automatically generated

    Select Next: Notifications >.

  5. Then click on the drop down Notification type and select Email/SMS message/Push/Voice and enter the name DemoLab.

    Picture 2059024190

  6. In the flyout screen on the right (shown in the image above), click the check box next to email, enter an email address and click OK. Then click on the Review + create button, and then click Create on the Create action group screen.

    From the Create an alert rule screen, on the Details menu, add an Alert rule with the name DemoAlert, and then select Review + create and Create.

Exercise 3: Deploy an Automation Runbook

Estimated Time: 30 minutes

Individual Exercise

The main tasks for this exercise are as follows:

  • Deploy a maintenance task script.

  • Deploy an Azure Automation Runbook to perform index maintenance in an Azure SQL Database

Task: Deploy an Automation Runbook to rebuild indexes in an Azure SQL Database.

  1. From the lab virtual machine, if you are not in the Azure portal, start a browser session and navigate to https://portal.azure.com. Provide appropriate credentials.

    Picture 2

  2. In the Azure Portal in the search bar type Automation and then click on Automation Accounts

    Picture 2075709555

    Click on the + Create button in the portal.

    Picture 1590905270

  3. Create your automation account

    Resource Group: Use the resource group you created earlier in this lab. DP300-Lab06

    Name: DP300Lab06

    Location: Use the region in which you created your Azure SQL server in Lab 2

    Select Review + create and Create. Your automation account should deploy in 1-3 minutes.

  4. Navigate to the github page for AdaptativeIndexDefragmentation. https://github.com/microsoft/tigertoolbox/blob/master/AdaptiveIndexDefrag/usp_AdaptiveIndexDefrag.sql

    A screenshot of a cell phone Description automatically generated

    Click on Raw. This will provide the code in a format where you can copy it. Select all of the text (Ctrl-A) and copy it to your clipboard (Crtl-C).

  5. In the Azure Portal, navigate back to your database and click on the Query editor as shown below.

    A screenshot of a cell phone Description automatically generated

  6. You will be prompted for credentials to login to your database. Use the credentials you created in Exercise 1. Login: labadmin Password: Azur3Pa$$

    When you try to log in, you may receive an error about a Whitelist IP. Click on the blue Whitelist IP link at the end of the error message. Doing so will update server firewall rules to allow access.

    A screenshot of a cell phone Description automatically generated

  7. After you see a success message, click OK on the log in screen again. This time you should be granted access to SQL Database Query Editor.

  8. In the query editor, paste the text you copied earlier in this task from GitHub into the Query 1 pane. Delete the USE msdb and GO statements on lines 5 and 6 of the script.

    A screenshot of a social media post Description automatically generated

  9. Click Run. The query should result in the following message:

    A screenshot of a social media post Description automatically generated

  10. In the Azure portal, type automation within the search field. Click on Automation Accounts.

    Picture 21

    Identity the automation account that has been created in your subscription and click on it.

    Picture 1165377632

  11. Select Modules from the Shared Resources section of the Automation blade.

    Picture 1267754120

  12. Click on Browse Gallery

    Picture 974424449

  13. Search for sqlserver within the Gallery

    Picture 1043266059

  14. Click on the SqlServer text which will direct to the next screen. Click on Select. Picture 31

  15. On the Add a module page, select the latest Runtime version available, and then click Import. This will import the PowerShell module into your Automation account.

    Picture 32

  16. You will need to create a credential to securely login to your database. From the Automation Account blade navigate to the Shared Resources Section and click Credentials.

    Picture 1940038127

  17. Click + Add a credential, and then create a credential as shown below:
    • Name: SQLUser
    • Username: labadmin
    • Password: Azur3Pa$$

    Click Create.

    A screenshot of a cell phone Description automatically generated

  18. Scroll to the Process Automation section of the Automation account blade and click on Runbooks and then + Create a runbook.

    Picture 568038330

  19. In the Create a a runbook panel, enter:
    • Name: IndexMaintenance
    • Runbook Type: Powershell
    • Runtime version: Select the latest runtime version available
    • Description: You can supply a short description of your choosing. Click Create.

    Picture 1283377310

    Once the runbook has been created, the process should drop you directly into the runbook.

  20. Use Windows Explorer to navigate to the folder D:\LabFIles\Automate Resources. Right click on the DP300-Lab-Module-6.ps1 file, and click Edit. The PowerShell IDE will open.

    A screenshot of a cell phone Description automatically generated

  21. Edit the $AzureSQLServerName variable to reflect the name of your Azure SQL server. You can get this value from the overview screen of your Azure SQL Database as shown below.

    A screenshot of a cell phone Description automatically generated

  22. Copy the name of the server including the .database.windows.net domain suffix.

    A screenshot of a cell phone Description automatically generated

  23. On Line 1 of the file paste in your server name. Select all of the text and copy it to your clipboard.

  24. Navigate back to your automation runbook, and paste in the PowerShell code you copied from the PowerShell IDE. Then click Save and then click Publish.

    Picture 679031903

  25. Click Yes if prompted to over-write any previously published versions.

    Picture 36

  26. Next you will schedule the runbook to execute on a regular basis. Click on Schedules in the left hand navigation menu. Then click on + Add a schedule at the top.

    Picture 1181869969

  27. Click on Link a schedule to your runbook.

    Picture 1181869969

  28. Select + Add a schedule.

    Picture 2105012659

  29. Supply a descriptive schedule name and a description if desired.

  30. Specify the start time of 4:00AM of the following day and in the Eastern Time zone. Configure the reoccurrence for every 1 Day. Do not set an expiration.

    Picture 39

  31. Click Create. Click OK.

  32. The schedule is now linked to the runbook.

    Picture 218516936