Before addressing the issue of backup size reduction with compression enabled, let me provide some context.
When SQL Server is hosted on AWS EC2 instances, you’ll need to manage the backup strategy appropriate to your business.
In order to persist and archive your SQL Server backups securely and permanently, AWS S3 is a prime target: it’s the service with the best availability and durability in AWS.
However, if you don’t have a version prior to SQL Server 2022, you can’t natively backup to S3.
In this context, you need to use an intermediary service: a Storage Gateway with File Gateway.
The Storage Gateway is a storage service that enables you to replicate (and extend) your data in the S3 Cloud. It can be deployed either on-premises or in the Cloud, making it an hybrid cloud solution.
To use it with SQL Server, you need the File Gateway type, which exposes an SMB file share; in the context of Windows Server.
Here is a simple architecture example with a SQL Server hosted on EC2:
In the S3 bucket, a classic structure is to create several prefixes (kind of folders) as follows:
Questions about your own setup? A dbi services consultant can look at your case.
Talk to an expert (opens in a new tab)- A prefix for daily backups with a short-term retention policy
- A prefix for monthly backups with a middle-term retention policy
- A prefix for yearly backups with a long-term retention policy
Of course, storage class may differ between these different prefixes to optimize cost.
In addition, for security and cost reasons, you’ll want to use an S3-level retention policy to remove backups over time (called S3 Lifecycle rules).
For added security, and to prevent backups being modified or deleted from the SMB fileshare, access can be restricted to read and write permissions only: this is what we’ve done in our context.
Moreover, to save space as much as possible, we enabled compression at SQL Server level.
This is the combination of these specifications which can, randomly, introduce the following error: Unable to open the physical file. Operating system error 5: “5(Access is denied)”.
To understand this error, let’s see how backup compression works with space allocation for the backup file.
SQL Server will first estimate the size of the compressed file (with a preallocation algorithm), but the final size may vary (can be at the end higher or lower) depending on how compressible the data is.
The above error occurs when the backup file is higher than needed, and the system tries to shrink the file to release unused space. This action requires Modify permissions, and an Access Denied will be triggered.
In our context, Modify permissions was not an option for security reasons.
As a workaround, we did enable Trace Flag 3042 which bypass preallocation algorithm. As a result, the file will grow as the backup progresses, requiring only Write permissions.
Keep in mind that increasing backup files over the time are less performant than preallocated algorithm: backup duration may increase for databases); but acceptable in our context.