Disable database backups for a time window to increase I/O for batch processes
We are using SQL Server for an informational database (no online transactions) and are getting performance issues since database backups start at the time we are still processing ETL workloads (SSIS reading) this causes significant performance issues.
It should be nice for us to set an exclusion window or set an start/end signal so no backup is needed for this period (our processes are on the fly and there's no point in restoring to any time point between the beginning and the end of the proccess).
Other option is that this can be set by forcing database backups before our processes start.
I agree on this also we see performance degradation during etl workloads due to backup. As simple and bulk logged recovery model is not available would it be nice to disable backup for a time period or even in some of our cases backup is not even necessary
Matt Mellifont commented
I agree with your idea of being able to stop backups to beef up performance when its needed (batch processing etc.). I would like to add another scenario/add-on to this.
I believe we need to be able to declare business hours (or heavy load hours) for the instance. As this is not available, full backups and differentials can run during business hours. We have seen this in action and it reduces the level of confidence in the solution to run at its best consistently. I say this as when building a DB solution regardless of the purpose (OLAP, OLTP, etc.) the one thing you need is consistency, which you can not get if an automated backup process decides to run during business hours.
To confirm this would be fine if the backup process did not effect the performance of the MI, but we have many examples of the negative impacts that full and diff backups have on an MI with heavy workloads. I believe Hyperscale will solve this issue in the future, but if traditional MI/SQL DB's are still planned to be available as a service moving forward, this will need to be addressed.