Adding support for S3 Backup URLs in SQL 2022

[benevoldson]

Modified logic checks for @URL to allow s3:// format URLs to support SQL 2022. Tested with EC2 SQL 2022 standard.

[mbrrg]

Great initiative! Improvement suggestion: support MAXTRANSFERSIZE up to 20 MB as supported by the S3 backend. Currently because of the parameter checks (max transfer size 4 MB) only databases up to 40 GB in size can be backed up. If I'm not mistaken.

[mbrrg]

...if you don't provide multiple URLs, that is. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage?view=sql-server-ver16#part-numbers-and-file-size-limitations

[benevoldson]

Great find! The new commit has checks specific to MAXTRANSERSIZE range for S3 as well as unsupported INIT option.

[alexsinkevich]

I have modified the jobs on our test servers to backup straight to S3 using the changed backup procedure as listed in the PR. It works most of the time, however on a server with multiple databases in sizes up to 800GB, it started crashing the availability group and causing AG failovers. I am using the following parameters in the call. Note that I am generating the command sting so I can do uppercase on server name and AG name as we have naming inconsistency in our environment and S3 names are case sensitive:

DECLARE @AvailabilityGroupName sysname;
DECLARE @ServerName sysname;
DECLARE @URL VARCHAR(512);
DECLARE @Credential VARCHAR(512);

SELECT @ServerName = UPPER(@@SERVERNAME);
SELECT @AvailabilityGroupName = UPPER(Name) FROM sys.availability_groups;

-- If @AvailabilityGroupName is null, set it to the value that Ola's script is designed to process
SET @AvailabilityGroupName = ISNULL(@AvailabilityGroupName, 'AvailabilityGroupName');

SELECT @Credential = name FROM sys.credentials WHERE name LIKE 's3://%';

SELECT @URL = @Credential

DECLARE @SQL VARCHAR(MAX)
    = '
EXEC dbo.DatabaseBackup  
        @Databases =  ''USER_DATABASES'',
        @URL = ''' + @URL + '/'',  
        @Credential = ''' + @Credential + ''', 
        @BackupType = ''Full'',
        @Verify = ''Y'',
        @Checksum = ''Y'',
        @Compress = ''Y'',
        @MaxTransferSize = 20971520,
        @DatabaseOrder=''DATABASE_SIZE_DESC'',
        @MaxFileSize = 204800,
        @DirectoryStructure =''' + @ServerName     + '{DirectorySeparator}{BackupType}{DirectorySeparator}{DatabaseName}'',
        @AvailabilityGroupDirectoryStructure =''' + @AvailabilityGroupName     + '{DirectorySeparator}{BackupType}{DirectorySeparator}{DatabaseName}'',
        @FileName=''' + @ServerName     + '${InstanceName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}_of_{NumberOfFiles}.{FileExtension}'',
        @AvailabilityGroupFileName=''' + @AvailabilityGroupName    + '_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}_of_{NumberOfFiles}.{FileExtension}'',
        @LogToTable = ''Y''
        ';

--PRINT @SQL;
EXEC (@SQL)

Have anybody else experienced this on SQL 2022? I mean the AG failovers when using backups of big databases to S3.

[jordiporto]

Hello, do you have any forecast to merge this change ? Thanks.

[dbaduck]

But you may also need to add the provision to allow s3:// and no @Credential clause when it comes to errors it throws when you don't supply a credential for Azure. S3 does not require a credential be specified. So the line IF @Credential IS NULL AND @URL IS NOT NULL AND NOT (@Version >= 13 OR SERVERPROPERTY('EngineEdition') = 8) needs to add AND @URL LIKE 'https://%/%' so that you don't get an error of 'The value of @Credential is not supported' when using S3 since it does not require a credential to be specified.

[benevoldson]

But you may also need to add the provision to allow s3:// and no @credential clause when it comes to errors it throws when you don't supply a credential for Azure. S3 does not require a credential be specified. So the line IF @Credential IS NULL AND @URL IS NOT NULL AND NOT (@Version >= 13 OR SERVERPROPERTY('EngineEdition') = 8) needs to add AND @URL LIKE 'https://%/%' so that you don't get an error of 'The value of @credential is not supported' when using S3 since it does not require a credential to be specified.

Thank you, this modification has been made.

[Kevin-S-Lewis]

I only just found this after hacking around myself... https://github.com/olahallengren/sql-server-maintenance-solution/discussions/629

"The IDENTITY should always be 'S3 Access Key' when using the S3 connector."

Given the above... may want a credential check as something like this (then its similar to Azure block blob):

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'))

Note its only Azure Page Blob that requires a credential in the backup string, Azure Block Blob and S3 backups don't, thus this bit of code could be reverted:

IF @Credential IS NULL AND @URL IS NOT NULL AND NOT (@Version >= 13 OR SERVERPROPERTY('EngineEdition') = 8) AND @URL LIKE 'https://%/%'

Back to:

IF @Credential IS NULL AND @URL IS NOT NULL AND NOT (@Version >= 13 OR SERVERPROPERTY('EngineEdition') = 8)

[fill-e]

benevoldson are you still using this ?

[olahallengren]

I have finally started working on this. It is in this pull request: https://github.com/olahallengren/sql-server-maintenance-solution/pull/822

[benevoldson]

benevoldson are you still using this ?

Unfortunately internal priorities shifted so I've not been able to fully implement it, been put on the backlog.

I have finally started working on this. It is in this pull request: #822

Great news, thank you! Hoping we can revisit and having the changes in the official source will help.

[olahallengren]

I have added support for backup to S3 storage. https://ola.hallengren.com/versions.html