Scenario: you want to set PITR to 35 days on your prod. That’s it.
That looks like an easy one.
No is not.
How hard can it be? Just use Terraform.
Generally speaking that would be the solution but it will not work.
Why?
Because people create databases through SSMS and that violate Terraform seamlessly.
Instruct your developers to… sorry, let me rephrase that.
Uh, use an Azure Policy instead.
Azure policy are the messy corner of your home.
That place under your stairs where you store the old vacuum cleaner and other object that half work.
That is Azure Policy.
But I found a blog post that says that it works.
Try, it will not.
Use ChatGTP to create a custom policy.
Tried, it doesn’t work.
Policies are complex JSON monsters and you are basically writing objects that you cannot really tell if they match the Azure backend or not.
We don’t have a list of elements to put in that JSON.
Open a ticket with Microsoft and ask for a policy like that.
Tried too. Microsoft Support has improved in the last 5 years, I see the effort.
And even if this problem looks quiet easy we couldn’t archive to reach the finish line.
Plus I have to tell you something about Policies.
Go ahead, no one knows Azure Policies anyway…
I have already wrote a blog post about policies and how hard are to setup.
In my mind, if I could reshape the Policy overview I would first put the left bar menu in this order:
Why?
Because this respects the logic order of the Policies.
Of course everyone knows the meaning of those 6 words. Can you just sum it up fast… in case someone forgot?
90% of Azure experts they don’t even know they have an old vacuum cleaner under the stairs so:
- Definition: define the policy, is where you put the JSON
- Assignments: the scope of the policy (Subscription, Resource Group, etc…)
- Remediation (AKA the broken part of the vacuum cleaner): you click on a button and you remediate the problems. And there is no automation 😱. So now that you have created a policy you have to come back here every now and then to click on that button. It drives me nuts!.
It would be easy to automate it with a simple cron job that could run daily, weekly or monthly but for unknown reasons this feature doesn’t exists. Microsoft thinks we live in a perfect world, somewhere over the rainbow, where skies are azure. But it’s a constant rain up here. Upvote if you agree. - Exemptions: This part is unused. I know you might think this should be on number 3 but you can already define exemptions in the Assignment. I think this part is just a repetition and you mostly use it after the Remediation when you realize that you have resources our of scope.
- Compliance: an overview that tells you how many resources are compliant or not with your policy. (You see Microsoft, skies are not blue. Sometimes you put a policy in place and for the reason X, Y, Z, that policy is not respected).
- Events: no one knows what is that for. I see it can be linked to an EventHub. Is the Rapid Egg Cooker that my dad bought one week after retirement. It was never used.
So now… what is the solution?
Create an Azure Automation account and put this runbook instead:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
# Authenticate to Azure Connect-AzAccount -Identity # Target subscription $targetSubscription = "Your-Subscription-name" # Set the subscription context Set-AzContext -SubscriptionName $targetSubscription # Retrieve all SQL Servers in the subscription $sqlServers = Get-AzSqlServer foreach ($server in $sqlServers) { Write-Output "Processing SQL Server: $($server.ServerName)" # Retrieve all databases for the current SQL Server $databases = Get-AzSqlDatabase -ResourceGroupName $server.ResourceGroupName -ServerName $server.ServerName foreach ($database in $databases) { # Skip system databases if ($database.DatabaseName -in @("master", "tempdb", "model", "msdb")) { Write-Output "Skipping system database: $($database.DatabaseName)" continue } Write-Output "Processing Database: $($database.DatabaseName)" # Get the current PITR retention policy $currentRetention = Get-AzSqlDatabaseBackupShortTermRetentionPolicy ` -ResourceGroupName $server.ResourceGroupName ` -ServerName $server.ServerName ` -DatabaseName $database.DatabaseName if ($currentRetention.RetentionDays -ne 35) { Write-Output "Updating PITR retention for Database: $($database.DatabaseName)" # Set the PITR retention to 35 days Set-AzSqlDatabaseBackupShortTermRetentionPolicy ` -ResourceGroupName $server.ResourceGroupName ` -ServerName $server.ServerName ` -DatabaseName $database.DatabaseName ` -RetentionDays 35 Write-Output "PITR retention updated to 35 days for Database: $($database.DatabaseName)" } else { Write-Output "PITR retention is already 35 days for Database: $($database.DatabaseName)" } } } |
This runbook will:
- Skip databases that already have 35 days as PITR
- Skip “master”, “tempdb”, “model”, “msdb”
- Apply 35 days where missing
And it works?
Point it to your sandbox subscription.
You will be delighted to see how easy is that.