Azure SQL PITR - Option to disable or set to 1 day retention
We currently have an SQL managed database, which we use for development purposes.
We are developing how our data ingest will work, currently we drop the tables every day, and re-load all the data. As of the 31-AUG we started getting charged for PiTR which we didn't know about, until PiTR started costing us upwards of $120 per day.
What I found out is after the initial backup (free) any changes were replicated to 6 regions (GRS storage) and set to 35 days retention period. These are backups we don't want or need, as data is ingested from the Datalake. Currently there is no option to turn this feature off, and the min number of days retention is 7 days.
Cloud being pay for what you use, and use what you want. There is no way to avoid these charges, unless we pay for provisioned SQL via the DTU model where PiTR is not charged. But we are using the serverless feature of vCores.
Please provide an option to turn off PiTR or set it to 1 day.
Jamey Patterson
shared this idea
We are pleased to announce that this feature has now been completed and generally available (GA) for Azure SQL Managed Instance.
Setting PITR backup retention period for active databases from 1-35 days is now possible using Azure Portal and PowerShell, and setting backup retention period for deleted databases from 0-35 days is now possible using PowerShell only.
Official documentation page: https://docs.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?tabs=managed-instance#change-the-pitr-backup-retention-period-by-using-the-azure-portal
How-to blogpost: https://aka.ms/mi-backup-tuning
Thank you for your feedback, please keep it coming.
Azure SQL Managed Instance product group
15 comments
-
Anonymous
commented
This post is really useful and helpful to know more about the things which you have shared. I appreciate you for such a great amount of information. I assure this would be beneficial for many people. https://www.dumpscafe.com/Braindumps-70-487.html
-
Jamey Patterson
commented
If you look at my comment on May 12th below, it has instructions on how to change PiTR on Azure SQL Databases.
-
Anonymous
commented
Hello,
@James and folks, could you confirm that it is also possible to reduce the retention on Azure SQL Databases ? I'm stuck with it on Powershell. If it is not available yet, could you provide an ETA ?
Thanks ! -
Jamey Patterson
commented
Managed instance PiTR can now be changed to 1 day,
->One day backup retention is now available for Managed Instance using PowerShell only. Minimum required version of Az.SQL module is v2.6.0, or execute through CloudShell.
To configure PITR backup retention to 1 day, use this example
Set-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -ResourceGroupName “RG-NAME” -InstanceName “Instance-Name” -DatabaseName “DB-Name” -RetentionDays 1 -
Will
commented
thanks @jamey - can confirm the rest api works rather than waiting for az.sql powershell 2.7
-
Jamey Patterson
commented
Anyone wanting to set this on their Azure SQL Databases can do this via this link
https://docs.microsoft.com/en-us/rest/api/sql/backupshorttermretentionpolicies/createorupdateOn this link there is a Green box that says "Try It" Press this and login, fill in the form with the details of your database, policy is just the word default, fill in the body =
{
"properties": {
"retentionDays": 1
}
}and click run.
-
Bruce Zhu
commented
Set-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -ResourceGroupName "RG-NAME" -InstanceName "Instance-Name" -DatabaseName "DB-Name" -RetentionDays 1
-
Giray
commented
same error as Will. with fresh Powershell 7 Core install and new Az module.
Set-AzSqlDatabaseBackupShortTermRetentionPolicy: Cannot validate argument on parameter 'RetentionDays'. Backup retention must be in 7-day intervals (7, 14, 21, etc.)
-
Anonymous
commented
There really should be an option to disable it completely. There are use cases (as described by the opener) where you just du full loads of your data, and where you don't care about backups because in the case of a problem, it might be faster or fast enough to just reload the data instead of using a backup. On the other hand, even if cost is low, you might not want the performance impact of every insert being logged during the full load. Log I/O usage is definitely a bottleneck in one of our workflows currently, forcing us to scale up to a database tier we wouldn't otherwise need.
-
Brian McAuley
commented
This is a prohibitive problem for us as well. What is the anticipated timeline?
-
Matt Chance
commented
We are eager to see this option implemented as soon as possible.
-
Danny Diaz
commented
we are waiting this option in place to save costs and avoid the space use in non-production data.
-
Austin Deal
commented
Thank you for the feedback. We are planning to relax the minimum retention from 7 days to 1 day.
To give some additional context on why the minimum is 7 days: in order to perform point-in-time restore, a full backup is required followed by a sequence of log backup(s). If a differential backup is relevant to the requested point-in-time, the service will utilize it in order to reduce the time of the restore operation.
Azure SQL DB takes automated full backups of each database every 7 days which limits the minimum retention that is allowed to be configured.
-
Will
commented
this setting will benefit MS by not having client store data on MS infrastructure servers saving valuable space for those clients that need it, and benefits the client who doesn't want to do backups at all and save the cost. Only reason I see this strict setting is currently in place is to withdraw as much money from the client as MS can.
-
Nigel Rablin
commented
This is important to have the option. For our non-production workloads this is costing us when we really do not need to recover at point in time. For production workloads we also need the option to decide whether each database is important enough to keep a PIT.