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.

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.

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

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.

I filled in the above to give me the below.

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”.

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.

It will find it’s way to the script.

Import it.

This is now your new playground.

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.

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.

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.

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

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

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

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

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