none
SQL Managed Instance PITR Backup Costs September 1 2019 - Help me understand the architecture RRS feed

  • Question

  • Hi

    I am trying to forecast the costs of our PITR backups on Azure SQL Managed Instance (MI). As per MS documentation it states that as of September 1st 2019 customers will be charged USD$0.24 per additional GB of storage used on this service. From my investigations, I predict that the same solution will be put in place as the SQL DB, meaning that your instance/DB size will be covered, (e.g. 7 days of PITR backups + ~4% additional for Logs and Diffs) but any additional retention period is billable @ USD$0.24 p/GB.

    Now my question is this:

    How do we accurately predict the cost implications of this change?

    Now to answer the above question we need to understand the following architecture of the solution, which I have not 100% been able to find out via official MS documentation:

    1. How does the backup architecture work for this service?

    Notes on above: Now this is in part answered in MS documentation, which states that for every 7 days of retention the following backups are taken, 1 Full DB backup, 14 differentials (2 per day), logs every 5 minutes. Now in theory if for every 7 days we follow that full backup methodology, we would see the following pattern;

    7 days = 1 Full, 14 diffs, logs

    14 days = 2 full, 14 diffs, logs

    21 days = 3 full, 14 diffs, logs

    28 days = 4 full, 14 diffs, logs

    35 days = 5 full, 14 diffs, logs

    42 days = 6 full, 14 diffs, logs

    Why did I include 42 days?

    This is because if we wanted to PITR smack on 35 days, we would need a full backup to restore along with the diffs and logs, therefore, if the fulls are taken every 7 days, we may require 42 days back (theoretically the worst case) in order to PITR to day 35. This is again not clearly stipulated so these are essentially my assumptions.

    What does this look like in actual figures?


    This is were it gets interesting, without understanding how the backup architecture works (in detail), and with no calculators available for these figures (SQL Managed Instance), I have put together the below table with my assumed costs.

    GB (DB's size) 7 days 14 days 21 days 28 days 35 days 42 days Differentials % Logs % Full % Cost USD
    1000 $24.80 $304.80 $584.80 $864.80 $1,144.80 $1,424.80 0.02 0.02 1 0.24
    2000 $49.60 $560.00 $1,120.00 $1,680.00 $2,240.00 $2,800.00 0.02 0.02 1 0.24
    3000 $74.40 $840.00 $1,680.00 $2,520.00 $3,360.00 $4,200.00 0.02 0.02 1 0.24
    4000 $99.20 $1,120.00 $2,240.00 $3,360.00 $4,480.00 $5,600.00 0.02 0.02 1 0.24
    5000 $124.00 $1,400.00 $2,800.00 $4,200.00 $5,600.00 $7,000.00 0.02 0.02 1 0.24
    6000 $148.80 $1,680.00 $3,360.00 $5,040.00 $6,720.00 $8,400.00 0.02 0.02 1 0.24
    7000 $173.60 $1,960.00 $3,920.00 $5,880.00 $7,840.00 $9,800.00 0.02 0.02 1 0.24
    8000 $198.40 $2,240.00 $4,480.00 $6,720.00 $8,960.00 $11,200.00 0.02 0.02 1 0.24
                         
    Based on                    
    weekly full backup       Full size            
    differentials % of DB size p/week       ~2%            
    log backups % of DB size p/week       ~2%            
    $USD Cost       0.24            
    *Payable as of Sept 1 2019                  

    Now this is where I ask the community for your insights, as I need to accurately predict the impact of our 35 day retention settings on our SQL Managed Instances.

    I hope the above information is informative at the very least, and I did not make any misleading assumptions. I look forward to hearing more on this topic.


    Thursday, July 11, 2019 12:18 AM

Answers

  • Hi Mike

    Thanks for the response, very much appreciated! I have upvoted the User Voice entry.

    Interesting point you raise about the first 7 days PITR costs that I calculated, as I was under the impression that PITR was credited in a GB for GB way, what I mean by that is if you have a 1000GB instance (1024GB in 32GB chunks), then the first 1000GB is credited, then you start paying for PITR for every GB after that, which is how SQL DB single DB works (see article here that supports this line of thinking https://azure.microsoft.com/en-us/pricing/details/sql-database/single/). This is how I got my original estimate of $24.80, as I assumed the first Full backup was credited and then I just accounted for an estimated 4% change in the DB (for diffs & logs) resulting in a total original cost of;

    Full backup = 1000GB (credited)

    Diff (2%) = 20GB (billable)

    Log (2%) = 20GB (billable)

    Costs = 40GB @ $0.24 = $9.60

    Apologies my original table of costs was a rushed one, I over calculated for the diffs & logs.

    Summary: So in conclusion, is it safe to say that my understanding of the database instance size being credited for backups is false? (as per example above) Therefore, as of September 1st 2019 we can expect to see costs being incurred for every GB over 32GB for backups, and as 7 days minimum is required by default for MI, at a minimum for a 1000GB instance we would be looking at an additional $198.40?

    Again, thank you for your time and efforts on this question, it is greatly appreciated.

    Cheers

    Matt

    Update: You are correct Matt. Not incorrect. Any billing related issues you experience can be addressed through opening a billing related support request. No support plan required.

    Thursday, July 18, 2019 11:53 PM

All replies

  • Hi CloudMatt,

    A couple corrections here.

    Firstly, you have 32GB of backup storage included with the service. Each additional GB is billed at $.20/GB. Per the pricing page for Manged Instance (link).

    STORAGE PRICE
    First 32 GB/month Included
    Additional storage* $0.115/GB
    Backup GB/month** $0.20

    Secondly, the backup retention period is between 7 and 35 days and is configurable (link). Backups for long-term retention can be held for up to 10 years (link).

    After the first full back-up for a 1000GB database (measured as 1024GB - increments of 32), I am showing a starting cost of $198.40 after you take the first 32GB as being included with the service. Not sure how you arrived at $24.80. 

    What is missing from your calculation is rate of growth of the database. This determines the log and diff file factors.

    This blog discuses the PITR architecture in greater detail: Azure SQL Database Point in Time Restore (link)

    Additionally, if you could up-vote and comment on the following User Voice entry (link) to also include a suitable means of calculating PITR costs before incurring charges. 

    Regards,

    Mike

    Thursday, July 18, 2019 12:11 AM
    Moderator
  • Hi Mike

    Thanks for the response, very much appreciated! I have upvoted the User Voice entry.

    Interesting point you raise about the first 7 days PITR costs that I calculated, as I was under the impression that PITR was credited in a GB for GB way, what I mean by that is if you have a 1000GB instance (1024GB in 32GB chunks), then the first 1000GB is credited, then you start paying for PITR for every GB after that, which is how SQL DB single DB works (see article here that supports this line of thinking https://azure.microsoft.com/en-us/pricing/details/sql-database/single/). This is how I got my original estimate of $24.80, as I assumed the first Full backup was credited and then I just accounted for an estimated 4% change in the DB (for diffs & logs) resulting in a total original cost of;

    Full backup = 1000GB (credited)

    Diff (2%) = 20GB (billable)

    Log (2%) = 20GB (billable)

    Costs = 40GB @ $0.24 = $9.60

    Apologies my original table of costs was a rushed one, I over calculated for the diffs & logs.

    Summary: So in conclusion, is it safe to say that my understanding of the database instance size being credited for backups is false? (as per example above) Therefore, as of September 1st 2019 we can expect to see costs being incurred for every GB over 32GB for backups, and as 7 days minimum is required by default for MI, at a minimum for a 1000GB instance we would be looking at an additional $198.40?

    Again, thank you for your time and efforts on this question, it is greatly appreciated.

    Cheers

    Matt

    Update: You are correct Matt. Not incorrect. Any billing related issues you experience can be addressed through opening a billing related support request. No support plan required.

    Thursday, July 18, 2019 11:53 PM
  • Hi Matt, wanted to follow up with you on this topic. You are correct that the first full backup is credited back to your subscription. So, you are not incorrect but are pretty much spot on. I apologies for the delay on this but wanted to catch-up on this thread. I am going to propose your response as the answer for the purposes of helping others who are investigating this same topic. Also, that you for the feedback on the User Voice entry as that tool would be very helpful in this situation.

    Regards,

    Mike

     
    Monday, July 29, 2019 3:16 AM
    Moderator