I have an Azure SQL DB (OLTP), that is under considerable load, let's call it AppDB. AppDB is transactionally consistent, but new rows are written to it constantly. Now I need to populate ReportingDB based on AppDB state every 30 minutes. Reporting population job runs several moderately big queries against AppDB, those queries, unfortunately, can't be wrapped in a transaction but still have to all run on consistent data. That is, I can't have situation
Query 1 runs=>
new rows inserted into AppDB =>
Query 2 runs. All my queries have to see data the way it was when Query 1 started.
I was hoping I can use Snapshots to create a read-only snapshot for Reporting job to use. From description, creation of such a snapshot should be fast and subsequent "copy on write" performance hit should be manageable. Snapshots lifetime would be under 10 minutes on average.
But now it looks like Azure SQL does not support
CREATE DATABASE ... AS SNAPSHOT OF ..., it only supports
CREATE DATABASE ... AS COPY OF ..., which I expect to be a lot slower (probably not meant to be used for reporting snapshot).
What would be a good way to create quick and short-lived read-only snapshots for reporting in Azure SQL DB?
P.S. We have considered replication, but it is not an option for us at the moment due to policy limitations.