How to estimate storage size for Azure SQL PiTR backups

Question

How can I estimate how much storage will Point in Time Restore consume for a single instance of Azure SQL database? We want to switch from DTU to VCore model and I need to do some estimates for the management.

I am aware that this depends largely on the number of transactions that DB has, but I got some really worrying answers from MS support, so maybe someone with more hands-on experience can help me.

Let's say that I have a 100GB database with 7 days PiTR enabled. Roughly speaking, how much storage will this feature induce?

Thanks.

Answer 1

(I'll preface my answer with some exposition)

• Point-in-Time-Restores (PITRs) work by first restoring the most recent Full backup, then applying differential/incremental restores on-top, followed by replaying the Transaction Log backups. Azure SQL maintains these backups for you and stores them together in their managed Azure SQL backup storage area.

• PITR restores are handled separately from LTR (Long Term Retention) backups and restores, and LTR backups/restores are out-of-scope of this StackOverflow QA.

• Creating and maintaining the backup files, and performing a restore, is fully managed by Azure - so you don't get to have any access to the raw files (which is probably a good thing).

• Azure SQL DTU databases cost a flat monthly fee which includes the cost of PITR backups, up to the 35-day limit.

• Azure SQL vCore databases charge around $0.12 USD/GB/month for LRS storage of PITR backup data.

  • But most (but not all) vCore DB tiers include enough free PITR backup storage space to ensure you have at least a Full backup (See here and see here too):

    The backup storage amount equal to the maximum data size is provided at no extra charge.

    • Azure SQL Hyperscale databases also have their own separate system which is even harder to predict storage-requirements for, ugh.

---

How can I estimate how much storage will Point in Time Restore consume for a single instance of Azure SQL database? We want to switch from DTU to vCore model and I need to do some estimates for the management.

(My answer ignores Azure SQL Hyperscale databases, which implement PITR backups in a very different way compared to other Azure SQL SKUs - so if you're thinking of using Azure SQL vCore General Purpose or Azure SQL vCore Business Critical then this answer should help you)

First, ensure you've read the current Backup storage consumption documentation, which my answer is based on.

• First, a separate Full backup file will exist for every week in your policy.
  • If you have a 7-day PITR policy, then a single Full backup will need to exist.
  • If you have an 8-day PITR policy, then Azure will create two Full backups - for this reason you may-as-well have a 14-day PITR policy instead of 8 days, as you'll have two Full backups in both cases.
  • The size of each full-backup (uncompressed) should (in my estimation) be slightly larger than the sum of the size of all allocated pages in your database - you can get this by doing SELECT SUM( a.used_pages * 8192 ) AS TotalSizeInBytes FROM sys.allocation_units AS a.
  • MS' documentation says Full-backups are compressed, typically with a compression ratio of 3:1 or 4:1.
  • So that gives us SELECT SUM( a.used_pages * 8192 ) AS TotalSizeInBytes, ( SUM( a.used_pages * 8192 ) / 3 ) AS CompressedFullBackupSizeEstimate FROM sys.allocation_units AS a
• Second, are the Differential backup files that Azure makes either every 12 - or 24 hours.
  • Differential backups always contain all changes (at the page-level) made since the last full-backup and not since the last differential backup. Azure says Differential backups are also compressed just like with Full backups, and I assume they also get a 3-4:1 compression ratio.
  • You can get the number of modified pages since the last Full backup by running SELECT modified_extent_page_count FROM sys.dm_db_file_space_usage.
  • So I estimate the size of the next new (compressed) Differential backup file to be (slightly larger than) SELECT ( modified_extent_page_count * 8192 ) / 3 AS NextCompressedDifferentialBackupFileSizeEstimate FROM sys.dm_db_file_space_usage - if that differential-backup file were to be made at this very instant in time.
  • Assuming a 24-hour differential backup schedule, then try to get a rough estimate of how many DB pages are modified per day, then make a cumulative sum over 7 days, e.g. if 6,000 pages are modified per day (page size = 8KB) and compressed with a 1:3 ratio then between each Full backup you'll have 7 differential-backup files, sized [ 15MB, 31MB, 46MB, 62MB, 78MB, 93MB, 109MB ] for each of those 7 days, respectively.
• Finally, the transaction-log backups are made every 10 minutes, which should be around the same size as your actual transaction-log file would be if it weren't truncated (don't forget to apply the compression ratio too).
  • You can get a rough estimate of how the txn-log file grows over time by checking delta_log_bytes_used in sys.dm_resource_governor_workload_groups_history_ex; if there's insufficient history then you'll need to monitor how the log-file grows before it's truncated (which can be done with log_space_in_bytes_since_last_backup in sys.dm_db_log_space_usage - or sys.dm_db_log_stats ( database_id )
• So then add all those numbers up, and that's your zeroth-order estimation of how much space your PITR backup files will need - then subtract the current size of your database (to account for the free storage you get by default) and that gives you the billable storage you'll need to pay for.

Let's say that I have a 100GB database with 7 days PiTR enabled. Roughly speaking, how much storage will this feature induce?

Well, if zero writes happen to your 100GB database at all, then your PITR will only need 100GB for the Full-backup as the Differential and Txn-Log backups will all be empty because nothing happened...