Command to test an environment for compatibility with S3 backups

[m82labs]

Summarize Command's Functionality

When backing up to S3 there are certain risks involved. Because of a combination of limitations and behaviors of both SQL Server and the S3 multi-part API, you can easily get yourself into a situation where backups start to fail.

The S3 multipart API is essentially stateless, you can just start uploading parts to it. It has no idea how many parts it will receive, so it cannot track "progress". In the context of SQL Server this means a backup could breach S3 limits (10,000 parts per backup object/URL) after sending 100's of GB of data, and unexpectedly fail. This then requires the DBA to use the S3 API, or configure policies, to clean up orphaned object parts from a failed upload. Otherwise those failed object parts would just stay there consuming space.

This command can be used to see how close your existing backup configuration would be to breaching those limitations, and in cases where you are close (or exceeding those limits) this command would give recommendations on how to get back under those limits while also pointing out how those changes may impact your host SQL Server instance and/or the network and target storage. The command will also have a mode that can be used in automation/backup scripts to try to fail early when a database gets close to a threshold.

Is there a command that is similiar or close to what you are looking for?

No

Technical Details

The general flow of this command is that it would:

• Read backup history for the DBs in question
• Take in the desired MaxTransferSize as a parameter
• Determine if, based on current backup file/URL counts from existing backups, backing up to S3 might breach the 10,000 part limit
• It will then try to calculate a potential solution by adjusting MTS and file count. It prioritizes lower-impact changes before suggesting higher-impact changes

When in monitor mode it will output only databases that need attention
When in Exception mode it will throw an error when a database is found that may breach limits. This can be used in automation workflows.

I have a full working concept I have been working on after doing research on this topic.