0

There has been a previous question on this and the accepted answer was Azure Elastic Job agent. The problem I have is that the feature is in preview and it still lacks a lot of functionality like diagnostics and alerting. I also find it to be very unreliable as job get randomly cancelled because of service restarts.

Azure Automation Accounts also works, but it only has a execution/running time of 3 hours. So if your maintenance takes more than 3 hours, this is not an option.

I have previously developed my own application for doing this, but the maintenance and management of this can become a headache. Another alternative could be to just leverage Azure Data Factory perhaps, but this is a route I have not yet followed.

So what are people actually using to do long running maintenance against Azure SQL Databases that has enough diagnostic information in case something goes wrong and has at least some level of alerting?

PS: The database I need to do maintenance on is not small.

Michéle Johl
  • 91
  • 9
  • Could you describe what maintenance tasks you perform? – Alberto Morillo Apr 28 '20 at 12:00
  • I perform daily maintenance tasks on Azure SQL databases of 400+ GB – Alberto Morillo Apr 28 '20 at 12:02
  • @AlbertoMorillo In my case, statistics across all the indexes where modification counter > 0. Takes about 8 hours to complete. – Michéle Johl Apr 28 '20 at 13:23
  • If you already performed rebuild of indexes just update columns statistics. Index statistics will be already updated – Alberto Morillo Apr 28 '20 at 13:39
  • The question is not about how to do the actual maintenance. It is about what do people use to do long maintenance operations Azure SQL. So I quote myself, "So what are people actually using to do long running maintenance against Azure SQL Databases that has enough diagnostic information in case something goes wrong and has at least some level of alerting?" – Michéle Johl Apr 29 '20 at 17:56
  • 1
    I use Ola Hallengren scripts with Azure Automation without any issues. You can configure the timeout on Azure Automation. – Alberto Morillo Apr 29 '20 at 19:53
  • Never knew you could go past the 3 hour limit. According to the resource limit documentation the maximum is 3 hours and not configurable. Mind sharing how you set it past 3 hours? – Michéle Johl Apr 30 '20 at 07:21
  • Try adding this on the RunBook. $DatabaseCommand.CommandTimeout = 0 – Alberto Morillo Apr 30 '20 at 14:09
  • Does not work. Azure Automation Account could not care less about that command and still stops executing. In the portal the run book physically states stopped. – Michéle Johl May 01 '20 at 08:00

0 Answers0