Sql-server – Why is the lease locked for a some full sql backups to Azure Blob using Ola Hallengren backup scripts

[T]he suggestion was to do the database integrity check before the backups so I was think maybe 30 mins before the main backup and then run the index optimize when a known time of little load. Would that make sense as a schedule for SQL Server backups?

A few thoughts:

• Index maintenance may not be necessary as often as you think. Usually index fragmentation has a very low impact on performance with modern hardware, especially when you take into account that all logs involved with index rebuilds have to be shipped to your Availability Group secondaries. I would suggest considering a daily (or even more frequent) statistics update, using Ola's maintenance solution, and make sure that you use this example from his site (take note of the NULLs for index fragmentation):

D. Update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

• If you find that index fragmentation does cause performance problems, or you just want to keep them in decent shape, you can schedule that on a less frequent basis. Depending on the database and index size, maybe once a week or once a month.

• This leads me into answering your "guesstimate" of 30 minutes before backups for consistency check. You won't know until you actually get the process running. If your monthly index maintenance takes three hours, you'll want to kick that off earlier than the statistics update. If CHECKDB takes five hours, then schedule it to run on an AG secondary, or restore the database to a stand-alone server and run it there (search for "offloading CHECKDB").

• Differential backups every hour is overkill, in my opinion. I generally do these once a day, and possibly twice a day depending on the environment. You have your log backups, and you need to keep in mind that differentials are not incremental. They will continue growing until the next full backup, because they keep track of all changes since the last full backup. In some cases, differential backups can exceed the size of the full backup. In those cases, you'll need to evaluate whether a full backup is better than a differential.

• Do you have enough disk space to keep 5-minute transaction log backups? Is that what the business needs (SLA, RTO, RPO)? Can you get away with 6 minutes? Over a three-month period, that extra minute makes a big difference in the number of files you're storing. Often I find most of my clients satisfied with a trade-off of 10- or 15-minute log backups.

• Full backups could be done daily, but depending on your retention policy, as well as Recovery Time Objective and Recovery Point Objective, you might be more than happy with a weekly full backup of user databases, a daily full backup of system databases, and the differentials can fill in the gaps where necessary.

Ultimately, this all depends on what the business needs to satisfy service level agreements internally and externally. If a reporting / OLAP style database can be regenerated in two hours from an OLTP source, then you don't need to worry as much about it as the OLTP database.

I hope this helps you come up with an appropriate strategy for your business. That's what should drive these decisions.

In order to clean up files in Azure blob storage requires PowerShell or going through some app like Azure Explorer. So it is likely that Ola's backup script does not support it since there is no native T-SQL command or procedure to access blob storage in that manner.

When you are deleting files you will need to account for any that may still have an active lease. BOL has code to handle dropping the active leases.