Setting up a SQL Server reporting database using Azure Blob storage backup and restores

I have been tasked with creating a copy of a database that will be used for reporting purposes. There are several methods I have looked into such as Read-Scale Availability Groups, Log Shipping, Database Mirroring etc. however it is very likely that the owner of the database will never give us the permissions and assistance necessary to configure those options, which is why I considered some kind of transactional log backup and restore process as a simple but effective method to get a sufficiently up-to-date copy of the database. For discussion sake, let's say they may be willing to give the permissions necessary to configure this. The issue however is getting those backups off the server and somewhere we have access to. My understanding is that backups can be sent to Azure Blob Storage and that this job can be scheduled at regular intervals, and from there we can restore to a SQL Server hosted on Azure, perhaps automating this as well (not sure yet how).

Is this a sane approach? I want to avoid cobbling together something that I will regret, but my DBA experience is limited. Does anyone have experience with such a setup and if so are there any pitfalls I should look out for?

It is possible to implement lightweight log-shipping via blob store. I can't see why MS didn't include it as a standard option when they added backup-to-blob.

Although you can back up to blob, you can't query available blobs to identify whether new backups are available. Synchronisation can be done using

• a push mechanism for the entire operation (source server does backup and then triggers restore on destination e.g. via linked server and Agent jobs)
• a pull mechanism for the entire operation (destination server triggers backup on source and then restores it e.g. via linked server and Agent jobs)
• external synchronisation (build a list of available backups and restore them e.g. using azcopy)
• SQL synchronisation (build a list of available backups and restore them e.g. using a linked server to pull/push backup details from the system tables msdb.dbo.backupset and msdb.dbo.backupmediafamily)

If there are standard log backups for DR purposes then you will need to use those to avoid breaking the backup chain. Not that the database will also need to have either FULL or BULK-LOGGED as the Recovery model to allow you to use log bgackups.

Also, the copy on the reporting server will need to be restore in read -only/standby mode using:

RESTORE ... WITH STANDBY = '<path>'

Restoring the reporting copy of the database will block queries on it, so you'll need to handle that somehow (e.g. schedule when you know you can do it).

If the database is small and has regular full backups then maybe pulling those would be enough. Confirm the requirements on the reporting side - if the database is only needed for a single standard report, maybe restoring before it runs is all that is needed.

Also, consider whether you really need all the data from the database, or whether a more targeted extract would suffice e.g. ETL-ing a relevant data summary.