All we need is an easy explanation of the problem, so here it is.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Azure SQL Database doesn’t support Database Snapshots, so there’s no other option besides:
- Running the reporting queries in a SNAPSHOT transaction.
- Creating a consistent copy of the database with COPY or point-in-time restore.
You can use read only replicas but they are not frozen so they can receive some new data.
Maybe you can fix the issue with some kind of filter based on insert/update datetime.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂