SQL Server 2012 – Ola Hallengren DatabaseBackup to Azure Requirements

Do you know what version of each you are using? How close are you watching this in real time? I'm hoping the below quotes and links with the questions above will help spur you towards the right direction here.

When backing up to or restoring from Windows Azure storage, SQL Server acquires an infinite lease in order to lock exclusive access to the blob. When the backup or restore process is successfully completed, the lease is released. If a backup or restore fails, the backup process attempts to clean up any invalid blob.

Reference

The Lease Blob operation establishes and manages a lock on a blob for write and delete operations. The lock duration can be 15 to 60 seconds, or can be infinite. In versions prior to 2012-02-12, the lock duration is 60 seconds.

Starting in version 2012-02-12, some behaviors of the Lease Blob operation differ from previous versions. For example, in previous versions of the Lease Blob operation you could renew a lease after releasing it. Starting in version 2012-02-12, this lease request will fail, while calls using older versions of Lease Blob still succeed. See the Changes to Lease Blob introduced in version 2012-02-12 section under Remarks for a list of changes to the behavior of this operation.

Reference

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