Deploy an automation runbook to automatically rebuild indexes

Estimated Time: 30 minutes

You have been hired as a Senior Database Administrator 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 (IaaS) and Platform as a Service (PaaS) offerings.

Note: These exercises may 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.

Create an Automation Account

1. From the lab virtual machine, start a browser session and navigate to https://portal.azure.com. Connect to the Portal using the Azure Username and Password provided on the Resources tab for this lab virtual machine.

   

2. In the Azure portal in the search bar type automation and then select Automation Accounts from the search results, and then select + Create.

   

3. On the Create an Automation Account page, enter the information below, and then select Review + Create.

   • Resource Group: <Your resource group>
   • Name: autoAccount
   • Location: Use the default.

   

4. On the review page, select Create.

   

   [!NOTE] Your automation account should be created in around three minutes.

Connect to an existing Azure SQL Database

1. In the Azure portal, navigate to your database by searching for sql databases.

   

2. Select the SQL database AdventureWorksLT.

   

3. On the main section for your SQL Database page, select Query editor (preview).

   

4. You will be prompted for credentials to sign in to your database. Use this credential:

   • Login: sqladmin
   • Password: P@ssw0rd01

5. You should receive the following error message:

   

6. Select the Allowlist IP … link provided at the end of the error message shown above. This will automatically add your client IP as a firewall rule entry for your SQL Database.

   

7. Return to the Query editor, and select OK to sign in to your database.

8. Open a new tab in your browser and navigate to the GitHub page to access the AdaptativeIndexDefragmentation script. Then, select 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 ( CTRL + C ).

   [!NOTE] The purpose of this script is to perform an intelligent defragmentation on one or more indexes, as well as required statistics update, for one or more databases.

9. Close the GitHub browser tab and return to the Azure portal.

10. Paste the text you copied into the Query 1 pane.

    

11. Delete USE msdb and GO on lines 5 and 6 of the query (that are highlighted in the screenshot) , and then select Run.

12. Expand the Stored Procedures folder to see what was created.

    

Configure Automation Account assets

The next steps consist of configuring the assets required in preparation for the runbook creation. Then select Automation Accounts.

1. On the Azure portal, in the top search box, type automation.

   

2. Select the automation account that you created.

   

3. Select Modules from the Shared Resources section of the Automation blade. Then select Browse gallery.

   

4. Search for sqlserver within the Gallery.

   

5. Select SqlServer which will direct to the next screen, and then select Select.

   

6. On the Add a module page, select the latest runtime version available, then select Import. This will import the PowerShell module into your Automation account.

   

7. You’ll need to create a credential to securely sign in to your database. From the blade for the Automation Account navigate to the Shared Resources section and select Credentials.

   

8. Select + Add a Credential, enter the information below, and then select Create.

   • Name: SQLUser
   • User name: sqladmin
   • Password: P@ssw0rd01
   • Confirm password: P@ssw0rd01

   

Create a PowerShell runbook

1. In the Azure portal, navigate to your database by searching for sql databases.

   

2. Select the SQL database AdventureWorksLT.

   

3. On the Overview page copy the Server name of your Azure SQL Database as shown below (Your server name should start with dp300-lab). You’ll paste this in later steps.

   

4. On the Azure portal, in the top search box, type automation.

   

5. Select the automation account that you created.

   

6. Scroll to the Process Automation section of the Automation account blade, select Runbooks, and then + Create a runbook.

   

   [!NOTE] As we’ve learned, note that there are two existing runbooks created. These were automatically created during the automation account deployment.

7. Enter the runbook name as IndexMaintenance and a runbook type of PowerShell. Select the latest runtime version available, then select Create.

   

8. Once the runbook has been created, copy and paste the Powershell code snippet below into your runbook editor. On the first line of the script paste in the server name you copied in the steps above. Select Save, and then select Publish.

   Note: Please verify that the code has been copied correctly, before saving the runbook.

    $AzureSQLServerName = ''
    $DatabaseName = 'AdventureWorksLT'
       
    $Cred = Get-AutomationPSCredential -Name "SQLUser"
    $SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -UserName $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $DatabaseName -Query "EXEC dbo.usp_AdaptiveIndexDefrag" -Verbose) 4>&1
   
    Write-Output $SQLOutput
   

   

9. If everything goes well, you should receive a successful message.

   

Create a schedule for a runbook

Next you will schedule the runbook to execute on a regular basis.

1. Under Resources in the left hand navigation of your IndexMaintenance runbook, select Schedules. Then select + Add a schedule.

   

2. Select Link a schedule to your runbook.

   

3. Select + Add a schedule.

   

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

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

   

6. Select Create, and then select OK.

7. The schedule is now created and linked to the runbook. Select OK.

   

Azure Automation delivers a cloud-based automation, and configuration service that supports consistent management across your Azure and non-Azure environments.

By completing this exercise you’ve automated the defragging of indexes on a SQL server database to run every day, at 4am.