Automating Azure SQL DB index and statistics maintenance using Azure Automation


To provide complete solution to maintain you Azure SQL DB statistics and maintenance we provide our maintenance script here

In this article, we will explain step by step how to automate this maintenance on Azure (You can also use that to automate your own T-SQL tasks)

General steps:

  1. Create Azure automation account
  2. Import SQLServer module
  3. Add Credentials to access SQL DB
  4. Add a runbook to run the maintenance
  5. Schedule task

Step by step instructions:

  1. Create new automation account
  2. Login to your Azure portal and click "New" (the green plus sign)
  3. Type "automation" in the search box, and choose automation.

    Figure 1 – new automation account

  4. Click "create"
  5. Fill the form, choose a name for your automation account, and choose in which resource group it will be placed.

    make sure you choose "YES" for the Create Azure Run As account.

    Figure 2 – add automation account form.

  6. Click "create" and wait for the account to be created. The new automation configuration blade will be opened once the provision completed.
  7. Import SQLServer module
  8. Click on "Modules" at the left options panel, and then click on "Browse Gallery" and search for "SQLServer"

    Figure 3 – add module

  9. Choose "SqlServer" by matteot_msft

    Figure 4 – module name

  10. Then click on "import" and the "OK"
  11. Wait for the import to complete
  12. Add Credentials to access SQL DB
  13. This will use secure way to hold login name and password that will be used to access Azure SQL DB

    You can skip this and use it as clear text if you like to use clear text skip to the next step.

  14. Under "Shared Resources" click on credentials

    Figure 5 – Add new credential object

  15. Then click on "Add Credential"
  16. Type "SQLLogin" as the name of the credential.
  17. In the username field type the SQL Login that will be used for maintenance and its password.
  18. Click "Create"
  19. Add a runbook to run the maintenance
  20. Click on "runbooks" at the left panel and then click "add a runbook"

    Figure 6 – Add a runbook

  21. Choose "create a new runbook" and then give it a name and choose "Powershell" as the type of the runbook and then click on "create"

    Figure 7 – add new PowerShell runbook

  22. Copy and paste the following row to the new runbook.

    Make sure you change your database properties.

    $AzureSQLServerName = "<ServerName>"
    $AzureSQLDatabaseName = "<DatabaseName>"
    
    $AzureSQLServerName = $AzureSQLServerName + ".database.windows.net"
    $Cred = Get-AutomationPSCredential -Name "SQLLogin"
    $SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName -Query "exec [dbo].[AzureSQLMaintenance] @Operation='all' ,@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1
    
    Write-Output $SQLOutput

     

    TIP: to redirect the verbose output to the runbook log we use the technique as described here

  23. Click on Publish and confirm.
  24. Schedule task
  25. Click on Schedules

    Figure 8 – Schedules

  26. Click on "Add a schedule" and follow the instructions to choose existing schedule or create a new schedule.
  27. Choose a time when the application is in the idlest figure, as running the maintenance might impact on performance while it's executing.

    Figure 9 – Create new Schedule

Monitoring

you can monitor the success of the job by reviewing the Automation overview page

Figure 10 – Job Overview.

then you can click on each category and drill down...

Figure 11 – Job executions

then you can click on a specific execution and get more details about it including the output of the script

Figure 12 – Details of job execution and output information.

I hope you enjoy this post, please share any thoughts on a comment here in this post.

Notes:

  • Automation account has a limit of 500 minutes of execution time per subscription per month on its free tier. More information about automation account limits can be found here

More information:

Getting Started with Azure Automation

Create a standalone Azure Automation account

My first PowerShell runbook

 


Comments (11)

  1. Brian Tower says:

    I’ve been working with your AzureSQLMaintenance sp for about 6 months or so now, and I even tried automating the process via Azure Automation in a similar process to what you’ve described here. The problem I keep running into is that my databases and indexes are quite large and numerous. The jobs will never complete within the maximum amount of time you can keep an automation session running. I’ve modified your SP to limit the impact running the stored procedure has on DTU consumption of the database, and find that it really only works on very small databases. Wondering if you have any suggestions on how to perform the same sort of work at a larger scale.

    1. Azure automation have limited job running time for the free tier if you have larger database you might consider using your app jobs mechanism (like using web jobs on your app service)
      anyway, I would be very happy to hear about your experience and modifications that you find useful for your case.

      1. Gunnar007 says:

        @Yochanan – Great post ! I was writing an article comparing runbooks and Azure functions for small workloads and your detailed instructions helped me avoid documenting all that, I am directing readers to your post instead https://www.linkedin.com/pulse/migrating-azure-sql-database-missing-agent-see-how-functions-campo/ .

        @Brian – Just bouncing off an idea, but if you are able to temporarily bump up the DTUs of that database you might be able to complete within the desired window, this might help https://gallery.technet.microsoft.com/scriptcenter/Azure-SQL-Database-e957354f . Once the maintenance work is done you could scale it back down . Other option could be leveraging the resumable online index rebuild https://azure.microsoft.com/en-us/blog/resumable-online-index-rebuild-is-generally-available-for-azure-sql-db/ , and do it in several chunks. Happy coding!

        1. Hi Gunnar, thank you very much for your feedback, I’m very happy that you find that useful.
          note that about the recent SQLServer module (that have an issue with invoke-sqlcmd) we already shared the feedback with the developers and they are working on releasing fixed version soon.

          1. csudr says:

            @Yochanan,

            Thanks for the guide. I have found that I needed to add the following to the top of step 22.

            Import-Module -Name SQLServer

            Might be a good idea to update the directions.

          2. Hi, Thank you for your comment. in recent PS modules should be loaded automatically. why did you use the import-module?

  2. csudr says:

    Based off of the guide that was my assumption too but the job would fail as it was unable to find Invoke-SqlCmd. I checked out the SQLServer module project and ran across an unrelated suggestion to import the module which corrected the problem for me. Thought I’d share :-).

    1. @csudr, Thank you for sharing this information, as mentioned this should be loaded automatically as it should be starting by PowerShell 3.0 and it’s documented here: https://msdn.microsoft.com/en-us/library/dd878284(v=vs.85).aspx anyway if you find it useful you can still use it to explicitly load the module.
      aga in – thank you for reading and commenting my my post.

  3. ashkansiroos says:

    It returns error when I try to execute the runbook:
    Invoke-Sqlcmd : Could not find stored procedure ‘dbo.AzureSQLMaintenance’.

    And I have SqlServer version 21.0.17262 in my modules. What am I missing?

    1. ashkansiroos says:

      I’ve even tried to add Import-Module -Name SQLServer on top of my powershell, but still the same error

      1. you need to make sure you have the maintenance procedure in your database.
        this mentioned at the top of this article.
        here is the link: https://blogs.msdn.microsoft.com/azuresqldbsupport/2016/07/03/how-to-maintain-azure-sql-indexes-and-statistics/

Skip to main content