For a long time Sitecore have recommended that you run SQL Maintenance regularly and rebuild the indexes. However you can’t run maintenance plans like this (as you would in an On-Prem environment) in Azure.
So I did some research and it seems that Sitecore set these up for you if you using Managed Cloud. but I couldn’t find much further info on this.
However I did come across this SSE post with a very useful answer from Richard Hauer on using Azure Runbooks and PowerShell to run database maintenance.
There was unfortunately not a lot of detail on how to set it up or use it, I’d only really used Azure Runbooks once before for monitoring and re-starting Solr – so I am certainly no expert on this.
So having done this recently I thought I’d write this post to help others who need to do this, follow the steps below.
Step 1 – Create a new automation account
If you don’t have an existing Azure Automation Account you will need one so go to the Automation Accounts section in Azure Portal and create one.
If you have an existing Automation Account you can move on to Step 2.
Step 2 – Create Runbook & Add Script
Note: These need to be migrated to Extension Based Hybrid workers by August 2024. However Microsoft provide a simple approach to do this. I haven’t used these yet as I don’t have VMs available to run the workers but we will do this soon, so please bear this in mind.
Under Runbooks in the Automation account click ‘Create a runbook’:
Then and name it something like ‘Sitecore-DB-Maintenance-Plan-Workflow-RB’. Ensure you choose ‘Powershell Workflow’ as the Runbook Type – otherwise the script doesn’t work correctly:
Click on the Runbook you just created and choose ‘Edit in portal’:
Then paste in the script (see below):
This is the script to copy and paste. It’s modified version of the one Richard shared on SSE.
It includes more logging and comments. Note some of the additional logging shows up in the ‘All Logs’ section as is Verbose:
You can test this if you like in the test pane but once you are happy with it publish it.
Step 3 – Create Credentials
Now we need to add our SQL Admin user username and password as Azure Credentials. If you don’t have an existing SQL Admin user you can use then create one which has the access required to rebuild indexes.
Next add an new Credentials under the automation account by clicking ‘Add a credential’:
Add the credentials details like so called ‘DatabaseCred’:
Step 4 – Create Schedules
Now we need to create a schedule for each Sitecore database that we want to Re-Index. This will run the Runbook Workflow script on a schedule.
Under the automation account click ‘Add a schedule’:
Then add the Schedule details. For example the below is for the Master Database.
Sitecore recommend Indexing is done weekly and In my case we want to run it out of hours (3am) and not over a weekend of near a Monday (as that is the busiest day for this client). This may vary for you so adjust accordingly:
Repeat this for each Database you want to Re-Index. I setup schedules for the main databases: Master, Core and Web:
Step 5 – Link Schedules & Set Parameters
Now we need to link the existing Schedules to the Runbook. Go to the ‘Sitecore-DB-Maintenance-Plan-Workflow-RB‘ Runbook and click ‘Link to schedule’:
Then select the Runbook Schedule by clicking ‘Link a schedule to your runbook’:
And select a schedule from those you setup previously at Step 4.
Then click ‘Configure Parameters and run settings’:
Set the parameters like so for the SQLServer, Database and CredentialsName like so. Use the Credentials you setup at step 3:
Step 6 – Set up Logging & Alerts
Under the runbook ‘Logging and tracing’ turn on ‘Log verbose records’ like so:
You can setup alerts if you would like to for errors under the automation account by creating an alert rule and filtering on the Runbook logs:
Step 7 – Test and check Logs
Once the Runbook schedule has run you can check the output under the ‘Jobs’ section of the runbook:
Check the ‘All logs’ section too and you should see more information such as how fragmented the tables were and the number of fragmented tables found:
That’s it, you should now have a working Runbook Workflow that automates the re-indexing and prevents your databases from becoming slow. Hopefully this is useful for others too.
Here are some other useful links that I found to help with this:
https://gist.github.com/ivanbuzyka/70db190d540e34300dab5015f21d00bf
https://github.com/yochananrachamim/AzureSQL/blob/master/AzureSQLMaintenance.txt
https://segovoni.medium.com/automating-azure-sql-database-maintenance-tasks-overview-bdbadcb312bf
https://learn.microsoft.com/en-us/azure/automation/automation-runbook-output-and-messages
https://learn.microsoft.com/en-us/azure/automation/learn/automation-tutorial-runbook-textual