0

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.

2

Azure SQL Database doesn't support Database Snapshots, so there's no other option besides:

  1. Running the reporting queries in a SNAPSHOT transaction.

or

  1. Creating a consistent copy of the database with COPY or point-in-time restore.
4
  • Thanks for the answer. I take it "COPY or point-in-time restore" is an actual "copy all data" approach and won't be good to run every 30 minutes on a PROD DB with alreqdy considerable load? Jul 24 at 21:14
  • The implementation details of COPY are not public, but at least restore doesn't touch to the production database directly. Jul 24 at 21:17
  • My concern is, how heavy is the creation of a "point in time restore"? Is it closer to Snapshot or to Backup? Can it be done 48 times in 24 hours? Or is it incremental? Jul 25 at 12:03
  • It's a full restore, so for a large or busy database it can take quite a while, although it won't affect your primary database. docs.microsoft.com/en-us/azure/azure-sql/database/… Jul 25 at 13:40
1

You can use read only replicas but they are not frozen so they can receive some new data.

https://docs.microsoft.com/en-us/azure/azure-sql/database/read-scale-out

Maybe you can fix the issue with some kind of filter based on insert/update datetime.

2
  • Any chance replication could be paused for about 10 minutes? Jul 25 at 12:00
  • You can stop replication, but you can't restart it. So no pause. Jul 25 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.