Backing up to Amazon S3 bucket.

[amsbarry]

Hello, I have gone through this discussion and I didn't see any discussion about using the backup solution to send SQL Server backups to S3 buckets. The official faqs on the maintenance solution site indicated that the solution is supported in EC2 instances but no mention of S3. Anyone here have [SQL Server Backup] solution sending SQL Server backups to S3?

[alexsinkevich]

In SQL Server 2022 Microsoft finally created a way to backup directly to S3 bucket in AWS.

Below is the document describing the steps:
https://aws.amazon.com/blogs/modernizing-with-aws/backup-sql-server-to-amazon-s3/

The backup command syntax is similar, but slightly different from backup to URL, like so:
BACKUP DATABASE db1
TO URL = 's3://sql-backups-2022.s3.us-east-1.amazonaws.com/backups/db1.bak'
WITH FORMAT, COMPRESSION, MAXTRANSFERSIZE = 20971520;

Is there a way to include this functionality into the solution? That would be a life changing modification, allowing AWS/SQL customers to save a ton of money by not keeping the volumes for the backups and not paying for them.

Please, @olahallengren ?

[Kevin-S-Lewis]

From the Version: 2022-12-03 17:23:44... this is only tested with S3, I've not verified it back against running to Azure blob, plus the other backups.

Change line 711 from:
IF @DefaultDirectory LIKE 'http://%' OR @DefaultDirectory LIKE 'https://%'
To:
IF @DefaultDirectory LIKE 'http://%' OR @DefaultDirectory LIKE 'https://%' OR @DefaultDirectory LIKE 's3://%'

Reason: The URL for S3 will always be prefixed with s3

Change line 964 (Check URL's) from:
IF EXISTS(SELECT * FROM @URLs WHERE Mirror = 0 AND DirectoryPath NOT LIKE 'https://%/%')
To:
IF EXISTS(SELECT * FROM @URLs WHERE Mirror = 0 AND DirectoryPath NOT LIKE 'https://%/%' AND DirectoryPath NOT LIKE 's3://%/%')

Change line 984 (Check URL's.. interesting previously we check for both http and https but do it slightly differently here) from:
IF EXISTS(SELECT * FROM @URLs WHERE Mirror = 1 AND DirectoryPath NOT LIKE 'https://%/%')
To:
IF EXISTS(SELECT * FROM @URLs WHERE Mirror = 1 AND DirectoryPath NOT LIKE 'https://%/%' AND DirectoryPath NOT LIKE 's3://%/%')

Change line 1298 from :
IF @MaxTransferSize < 65536 OR @MaxTransferSize > 4194304
To:
IF (@MaxTransferSize < 65536 OR @MaxTransferSize > 4194304 AND (@URL NOT LIKE 's3://%/%%' OR @URL IS NULL)) OR (@URL LIKE 's3://%/%%' AND (@MaxTransferSize < 5242880 OR @MaxTransferSize > 20971520))

Reason: MaxTransferSize by default is 10MB, but can be anything from 5MB to 20MB.. this is quite different compared to Azure Blob Storage or local shares/disk. Supported Features

Change line 1741 (Credential section.. note Credential parameter is supported but I've not tested) from:
IF @URL IS NOT NULL AND @Credential IS NULL AND NOT EXISTS(SELECT * FROM sys.credentials WHERE UPPER(credential_identity) = 'SHARED ACCESS SIGNATURE')
To:
IF (@URL LIKE 'https://%/%' AND @Credential IS NULL AND NOT EXISTS(SELECT * FROM sys.credentials WHERE UPPER(credential_identity) = 'SHARED ACCESS SIGNATURE')) OR (@URL LIKE 's3://%/%' AND @Credential IS NULL AND NOT EXISTS(SELECT * FROM sys.credentials WHERE UPPER(credential_identity) = 'S3 ACCESS KEY'))

Reason: With Azure Block Blob one would use a SAS key, the documentation for S3 has examples where the credential is stored with an identity of 'S3 ACCESS KEY', so makes sense to validate appropriately. Access Key example

Then either add a check to make sure S3 destination isn't being specified for versions earlier than SQL Server 2022, or alter the S3 Access Key line from above to:
IF (@URL LIKE 'https://%/%' AND @Credential IS NULL AND NOT EXISTS(SELECT * FROM sys.credentials WHERE UPPER(credential_identity) = 'SHARED ACCESS SIGNATURE')) OR (@URL LIKE 's3://%/%' AND @Credential IS NULL AND (NOT EXISTS(SELECT * FROM sys.credentials WHERE UPPER(credential_identity) = 'S3 ACCESS KEY') OR @Version < 16))

Now I realise that the Microsoft documentation states that Region is an available parameter, however its not used in the AWS documentation here.

Using "sub-folders" works in the same principal as per Azure blob backups with the proc. The striping using @NumberOfFiles also works without issue.

Obviously this is only partially tested, caveats apply, etc, etc.

@olahallengren for your consideration.

[Kevin-S-Lewis]

Ah and now seen this... #714

[olahallengren]

Backup to S3 storage has now been added.
https://ola.hallengren.com/versions.html