Sometimes I get sidetracked.
Don’t tell that to your boss! It’s OK. He knows that I listen to Kate Perry while I’m analyzing data and he trusts me.
How much time have you spent on Kate-Perry-non-Jira-tickets? Let’s say that this investigation started around Q3 2024 and it took me a lot of emails back and forward to the Microsoft support in order to understand what was going on. And the root cause.
Is this worth to be posted on a blog? I’m pretty confident that I’m the first one rising this problem in the blogosphere.
You can show off now, we are between friends. What are you talking about? OK, drop everything like it’s hot and do this: go to your Cost analysis and put
Service name = SQL DatabaseGroup by = Meter subcategory
Everything looks normal, right?
Mmm… yes. Well this is the nasty part: if you have a database park of hundreds of databases everything looks normal.
It’s because of the forest that you cannot see the tree. Not the other way around. Now set:
Resource = <your-database-name-here>
And start to pick up databases on vCore randomly. You might start to see a trend…
Not in every database… just in some of them… only on vCores…. sometimes you see this:
What??? What in the fucking hell is going on here??? What is the tier of this database? This is a 6 vCores
6 vCores? This should cost 1’083.84USD per month instead it’s x4 the price!!! What is going on? Where is the fire extinguisher? I know, I had the same reaction.
No, really… how can I pay ~$1 in Storage, ~$10 in Compute, ~$10 in License…. and >$130 in the fucking backup??? What the hell is going on here??? Why am I paying this? You are paying for you sins.
My sins? Yes, the 7 deadly sins:
- Gula (gluttony): You created useless pipelines in ADF. You ingested tons of useless data and not content with endorsing such nonsense you executed those pipelines every 5 minutes.
- Luxuria/Fornicatio (lust, fornication): You created an orgy in the database: workers, sessions… everyone can connect.
- Avaritia (greed): You reduced the costs of implementation at the expenses of code quality.
- Tristitia (sorrow, despair, despondency): You postponed, procrastinated, hoping the next DBA/Developer would pick up the problem.
- Ira (wrath): Enraged with low performances you scaled up the tier of your Azure SQL Database. To 4 vCores, to 8 vCores… to whatever vCores….
- Acedia (sloth): You avoided indexing and performance tuning, knowing that that would have been the easy solution and your duty.
- Vanagloria (vanity vainglory): You passed from DTU to vCores hoping that the DevOps team would clap their hands to you for that decision.
- Superbia (pride): You defined yourself a “DBA in the Cloud” hoping that that would meant “I don’t have to care about backups any more”…. yeah… this blog post it’s all about that…. backups…
…Burn! Burn! Burn!
Nooooohhhhh!!!
…No, really, what is this all about?
Why this is happening?
PITR is the sum of :
- Full backup
- Differential backup
- Log backup
Full backup and differential backups are not to blame. The problem is the Log backup.
Your database is under constant stress (ETLs, workload, connections, recurrent jobs, etc…). The Log backup is recording this endless amount of transactions and it basically never stops recording.
In the Cloud you pay the poor quality of your code 💸. Do you remember your 7 sins?
When this has started? If you use the Cost Management and you go back in time you can see clearly when the problem started:
This is the timeline:
- Before the 5 of August the database was on an S2. The cost is flat.
- 5th of August the database was moved to vCores.
- From the 5th of August to the 10th of September: The PITR is set to 35 days, Azure is accumulating the backups day by day. The cost increase each day.
- 10th of September Each day a new backup is taken. Because 35 days have passed the older is now deleted for the first time (the one that is 36 days old). The cost now doesn’t increase gradually but it has reached what I call a “plateau”.
Can I manage the backups? On-prem you might tune it at your wish (and I suggest you to follow the best practice) but in Azure SQL Database you cannot manage that. And for a good reason. You actually love the Cloud for this: because you don’t care any more about your backups, right?
But why some databases are affected by this problem and other not? I’m going to explain that to you in a phrase:
“As long as the size of the PITR is less or equal to the size of the database you don’t pay.
If the size of the PITR is bigger than the database you pay for each gigabyte after the size of your database”
The total billable backup storage, if any, is charged in gigabytes per month according to the rate of the backup storage redundancy that you’ve used.
And it’s billed following this mathematical rule:
Total billable backup storage size = (size of full backups + size of differential backups + size of log backups) – maximum data storage
How can you fix this problem?
I’m going to give you two ways to fix this problem, one is fast and easy but it’s just a patch; the other is the true solution but it requires more work.
OK, give me the fast and easy first: Scale your Azure SQL Database to DTU. Standard or Premium. You will stop being billed for the PITR in the next hour or so. And yes, you will still have your backups.
And the long but better solution: Do you remember your 7 sins? You now have to refactor your code.
You can start from the indexes. They are the easy ones and…
…hey, where are you running?
..come back here, I have to tell you how to do all that work…
…oooh, crap…
