Sql-server – Changing backup interval

backupsql server

I'm considering changing the SQL Server differential backup interval for simple recovery databases from every 2 hours to every 4 hours with one daily full backup. A four hour data loss is acceptable per SLA. Considering SQL Server will automatically issue checkpoints, log clearing will continue and doesn't appear to be a concern at this point. Before making this change should I monitor data churn or checkpoint occurrences? Thus far I'm thinking this is truly a RPO/SLA concern or issue. Let me know if I'm missing anything.

Thanks in advance!!!

Best Answer

Considering SQL Server will automatically issue checkpoints, log clearing will continue and doesn't appear to be a concern at this point. Before making this change should I monitor data churn or checkpoint occurrences?

If these haven't historically been issues for you, then I think you are fine. It does indeed appear to be purely an RPO/SLA/SLO based issue.

That being said, you didn't mention what precipitated this change, but something to consider from Google's SRE book around SLA/SLOs:

Using a tighter internal SLO than the SLO advertised to users gives you room to respond to chronic problems before they become visible externally. An SLO buffer also makes it possible to accommodate reimplementations that trade performance for other attributes, such as cost or ease of maintenance, without having to disappoint users.

So if there isn't an active problem necessitating this change, it might be worth keeping the more frequent differential backups in my opinion.

Related Question