SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Azure Automation and SQL Index Maintenance

Azure does a lot for your SQL Database, from backups to automatic tuning but it still doesn’t have an index maintenance policy straight out of the box via the portal. Some may not care about rebuilding your indexes but it is still something I like to do, the question is, how can I automate this because I am not a fan of manually running code for index rebuilds.

The answer is via Azure Automation.

At a high level this is what I did.

  • Create an Automation Account.
  • Create a credential.
  • Create a PowerShell Runbook which has the code for index rebuilds.
  • Create a schedule and link it to the above.
  • Configure parameters within the schedule (if any).
  • Configure logging level (if desired).

The Automation Account

The first step and the easiest one is to navigate to the Automation Accounts section. Typically, what I usually do is just type few letters in the search box and the “live” search will get me to it.

automation

Simply enter a name for the account and then select your subscription and resource group. Notice the warning triangle (below)? It states “You do not have permissions to create an Azure Run As account (service principal) and grant Contributor role to the service principal. Please follow the directions in this document to create one with the help of the subscription admin.” Basically this is because my account is not a member of the Subscription Admins role and co-admin of the subscription so I got an admin to complete this section.

MainPage

Once it has been built the main menu will look like the below.

AccountAutomation

Credentials

At this stage I feel it’s best to create a credential. This is a login and password that will authenticate to the database to carry out its tasks.

credential

I filled in the above to give me the below.

sqlcred

Just a little pointer I made the user name db_owner within database.

Runbook

This is where all the magic happens. Under process automation you will see runbook option, click that then “Browse Gallery”.

RunBooks

The idea here is to use a pre-built PowerShell Workbook which I import then modify to my liking. I changed bits of the code to look at certain tables that are greater than 1000 pages and omitted tables.

I search for “index” and click enter.

BrowseGallery

It will find it’s way to the script.

indexcode

Import it.

Imported

This is now your new playground.

menuBar

When I first started with this tool I used to test the code via the Test Pane. If you click it here it gives you a chance to change code and run it interactively. This is something I definitely suggest doing.

You will see the parameter section on the left side of the image below, don’t worry all this will be entered for the schedule. Enter the details for your test area and click the start button, you will see RUNNING then COMPLETED.

The code for the rebuild is actually working at the table level and notice the ONLINE = ON option.

        $SQLCommandString = @”  EXEC(‘ALTER INDEX ALL ON $Using:TableName REBUILD with (ONLINE=ON)’)”@

Feel free to modify the current runbook to your liking, that is if you want to be more granular and work at index level rather than table level.

TestIt

Once you have successfully tested it you need to PUBLISH it and next link it to the credentials mentioned earlier and setup the schedule.

Scheduling

Scheduling is best done within the runbook main menu bar.

ScheduleMenu

Here you link (or create a schedule) to the runbook AND configure your parameters that you want to get pass in every time it executes.

WhatTime

The parameter section is very similar to the test pane shown earlier.

Para

Save it and let it run, what you want to see is completed green output!

Green

Just to confirm the fragmentation levels I hop onto SQL Server Management Studio and issue the following query.


         SELECT a.object_id, avg_fragmentation_in_percent, index_type_desc
        FROM sys.dm_db_index_physical_stats (
               DB_ID(N'TestDB')
             , OBJECT_ID(0)
             , NULL
             , NULL
             , NULL) AS a
        JOIN sys.indexes AS b
        ON a.object_id = b.object_id AND a.index_id = b.index_id
		ORDER BY avg_fragmentation_in_percent

FragLEVELs
By the way if you want to tweak logging levels then the setting is found within the actual runbook under runbook settings.

logging

One tiny thing which slightly bemuses me is that I cannot rename the account? I am still trying to find out where that option is.


Filed under: Automation, Azure Tagged: Automation, Azure, Indexes, SQL, SQL database, SQL server

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...