Recently, I have been knee deep in doing a MySQL migration to Azure SQL.  I quickly learnt that Azure SQL required a lot more attention than its MySQL counterpart.  So an Index and Statistic maintenance plan was needed, and urgently to keep things humming along smoothly.

Much to my amazement, Azure SQL did not offer the typical SQL Server Agent!  Everything in Azure is extra!!!

So I started doing a little digging and found a couple options.  One potential solution which I was exploring was to create an Automation Account to run a Maintenance Stored Procedure on a daily/weekly basis.  I only mention this as it may be an avenue worth exploring for some.

In my case, because the client I was helping already was using flow and was already using the premium SQL Server connector, I decided to go the Power Automate Flow route to automate the maintenance.  Thus providing a solution at no extra cost to them.

The Overall Idea

My process is very straightforward:

• Create an Azure SQL Stored Procedure that performs the necessary index and statistic optimization
• Use Power Automates Flow to run it on the desired schedule

The Maintenance Plan Stored Procedure

Now how you wish to maintain your system is entirely up to you and it truly inconsequential to this process.

There are a number of excellent existing Maintenance scripts available online, some free, some not:

• Ola Hallengren’s SQL Server Maintenance Solution
• Microsoft’s Yochanan Rachamim’s Azure SQL Maintenance
• Brent Ozar’s First Responder Kit

or you could create your own.  The choice is yours.  All that matters is that you have a Stored Procedure in place that you can then automate through Flow.

Creating the Azure SQL Scheduled Maintenance Flow

The following steps illustrate how you can create a recurring scheduled flow to execute a Stored Procedure in Azure SQL (as of 2021-03-05).

1. Log into Power Automate
2. Create a New Flow
   
3. Select Scheduled cloud flow from the New flow drop-down menu
   
4. Fill in the Flow Scheduling Frequency Information as you see fit for your needs
   
5. The main Flow Editor should then load with a first Recurrence step in place and defined (per your entry in the previous dialog).Now, we are ready to add a new step to run the Azure SQL Stored Procedure, so click on the New step button.
   
6. In the Choose an operation form, search for the term Execute SP and then select Execute stored procedure from the list of Actions that appear.
   
7. Fill-in the pertinent information in the Execute stored procedure step that will appear: Server name, Database name, Procedure name & input value required by the Stored Procedure you selected to execute.
   

Now, you can stop here if you wish, the scheduled maintenance is setup and will execute the stored procedure based on the frequency you specified.  Be sure to SAVE the flow at this point if you are done.

That said, in my case, I wanted to know when the Flow failed, so I wanted to add some type of notification to it.  After a little playing around I managed to setup an email notification when the flow failed.  Below are the extra steps to make that happen.

8. Add a New Step
9. In the Choose an operation form, search for the term Condition and then select Condition from the list of Actions that appear.
   
10. Click in the 1st Choose a value field, click on the Expression ‘tab’, and enter the expression following the general format of outputs(‘ActionName’)[‘statusCode’].  In my case, the expression was outputs(‘Execute_stored_procedure_(V2)’)[‘statusCode’]An important thing to note here however is that when entering the ActionName, you must replace ‘spaces’ with underscores (_)!!!
    
11. Enter 200 in the 2nd Choose a value field.  By doing this we are making the condition evaluate the returned statusCode from the previous step.  200 indicates that it executed properly.
12. In the If no condition form, click on Add an action
    
13. In the Choose an operation form, select Mail from the list that appear.
    
14. Configure the e-mail parameters as you see fit: To, Subject, Body
    
15. Click on Save!

And you are done!

If you wish, you could repeat the same steps to receive a confirmation e-mail when the flow run and is successful.

The above sums it up pretty nicely.  Be sure to test your flow, and also log into the portal from time to time to review the flow history to make sure it is running as expected.

In the case of SQL Azure maintenance, you can/should also be checking the index fragmentation to ensure your script is having the expected results.

I hope this helps save a few of you out there the time and frustrations I went through to get this operational.