SQL Server 2022: Backup Database Options to AWS S3 Bucket - Part 2

By: Rajendra Gupta   |   Updated: 2022-07-01   |   Comments (3)   |   Related: 1 | 2 | 3 | > SQL Server 2022

Problem

SQL Server 2022 Community Update 2.0 supports taking backups of a SQL Server database directly to an AWS S3 bucket. In part 1 of this tutorial series, we explored the AWS S3 bucket configuration, IAM policy, and user to get an access ID and secret access key.

In this second part, we will focus on creating a SQL Server database backups to an AWS S3 bucket along with the various supported backup options.

Solution

SQL Server 2022 is Azure enabled database for disaster recovery with improved performance, security innovation, analytics, and data governance. Until SQL Server 2019, we could use only Microsoft Azure storage for backing up databases directly into cloud storage.

SQL Server 2022 (16.x) enables object storage integration with the S3 compatible object storage in addition to Azure Storage. It contains a new S3 connection that uses S3 REST API for connecting to any provider of S3 compatible storage. Currently, many service providers offer S3 compatible storage in terms of software-defined hardware appliances or a combination of the hybrid cloud scenario.

A few examples of the object storage providers offering an S3 endpoint are:

• AWS Simple Cloud Storage (S3)
• Multi-Cloud Object Storage (MinIO)
• ECS Enterprise Object Storage (Dell Technologies)
• Nutanix Object Storage (Nutanix)
• Hitachi Content Platform (Hitachi Vantara)
• Pure FlashBlade (Pure Storage)
• OpenShift Container Storage (RedHat)

SQL Server 2022 database backup to AWS S3 bucket

Before moving forward, make sure you configure the following using part 1 of this tutorial series:

• AWS S3 bucket for storing database backups
• Noted URI and URL of the S3 bucket
• Noted IAM user access id and secret access ID.

I have the following values to start working with this tip:

• S3 bucket name: sqlserver2022
• Copy S3 URI: s3://sqlserver2022/fullbackups/
• Copy URL: https://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/
• IAM Access ID: AKIASEPWAFS67AHDKLOB
• Secret Key: 'AKIASEPWAFS67AHDKLOB:oXJ3OTofEPzbI4fBgL/yB1TJNTJyvJSHl96D0toX'

Note: Never reveal the IAM access id and secret key. I am doing it for demonstration purposes only.

Create credentials in on-premises SQL Server

Connect to your on-premises SQL Server instance and create credentials using the following syntax of CREATE CREDENTIAL statement.

CREATE CREDENTIAL [s3://<endpoint>:<port>/<bucket>]
WITH    
       IDENTITY = 'S3 Access Key'
      ,SECRET   = '<AccessKeyID>:<SecretKeyID>';

Let's understand the inputs required for the CREATE CREDENTIAL statement.

• [s3://<endpoint>:<port>/<bucket>]: The credential name must include the S3 bucket name. It starts with S3:// and follows the S3 bucket URL after removing the prefix HTTPS://. For example, in my case, the copied S3 URL is https://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/. Therefore, I specify the credential name as [s3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups]
• The IDENTITY value must be the S3 Access Key. It denotes that we are using an S3 connector in the credential.
• In secret, specify the access key id and secret key separated by a colon. The colon acts as a separator. Therefore, verify that your existing access id and secret key must not have a colon.

Create the SQL Server credential by executing the following T-SQL statement.

CREATE CREDENTIAL [s3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups]
WITH    
       IDENTITY = 'S3 Access Key'
      ,SECRET   = 'AKIASEPWAFS67AHDKLOB:oXJ3OTofEPzbI4fBgL/yB1TJNTJyvJSHl96D0toX';

Backup database statement format and consideration in SQL Server 2022

Use the following T-SQL statement to create a full SQL database backup to the AWS S3 bucket.

BACKUP DATABASE [DatabaseName] TO URL = 'CredentialName\Backupfilename.bak'

The S3 compatible object storage provider must split backup files into multiple blocks. These blocks are called parts.

• Each backup file can be split into 10,000 parts
• Each part size can be between 5 to 20 MB. You can control this range using the MAXTRANSFERSIZE in the T-SQL backup statement.
• SQL Server 2022 uses by default 10 MB value for the parameter MAXTRANSFERSIZE.
• It supports the maximum file size of a single file as 10,000 parts * MAXTRANSFERSIZE.
• You can split a more extensive backup into up to 64 URLs. Therefore, the maximum supported file size is 10,000 parts * URL * MAXTRANSFERSIZE.
• A single SQL database backup file can be up to 200,000 MB per URL (20*10,000)

Note: You must specify COMPRESSION in the backup statement to change MAXTRANSFERSIZE values.

Let's see the SQL Server 2022 backup to the AWS S3 bucket in action. I created a new database [demodb] with a sample table for the demo.

CREATE DATABASE demodb
GO

USE demodb
GO

CREATE TABLE T (id int)
GO

INSERT INTO T values (1)
INSERT INTO T values (2)

The script for taking a full backup of [demodb] to the S3 bucket that we configured earlier is below. Note the URL format is S3://credentialName/backupfile.bak. We did not specify the MAXTRANSFERSIZE parameter. Therefore, it uses the default 10 MB value.

BACKUP DATABASE demodb
TO      URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb.bak'
WITH    FORMAT 
       ,STATS  = 10
       ,COMPRESSION;

As shown below, the backup was completed successfully on the S3 bucket.

Let's switch to the AWS console and navigate to our S3 bucket to verify. We can see a backup file exists in the backup folder.

I performed some DMLs in my demo database and my database size is now 208 MB as shown below.

Let's specify the MAXTRANSFERSIZE as 20 MB(20971520 bytes), which overrides the default value of 10 MB.

BACKUP DATABASE demodb
TO      URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb.bak'
WITH    FORMAT 
       ,MAXTRANSFERSIZE = 20971520  
       ,STATS = 10
       ,COMPRESSION;

As shown below, my compressed backup size stored in the AWS S3 bucket is 3 MB.

As stated above, a single SQL database backup file can be up to 200,000 MB per URL (with MAXTRANSFERSIZE set to 20 MB). In this case, you can split the backup into 64 URLs. For example, the following query takes [demodb] backup and splits it into three URLs.

BACKUP DATABASE demodb
TO      URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb1.bak'
       ,URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb2.bak'
       ,URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb3.bak'
WITH    MAXTRANSFERSIZE = 20971520  
       ,STATS = 10
       ,COMPRESSION;

As shown below, it splits the backup into three files. I found that the first backup file size is the same as the single backup file size in my tests.

Supported backups in SQL Server 2022 on AWS S3 bucket

Below we cover what types of backups are supported with an AWS S3 bucket using SQL Server 2022.

SQL Server Full Backup to AWS

I have shown how to do full backups above.

SQL Server Differential Backup to AWS

Let's take a differential backup of [demodb] and store it in the AWS S3 bucket.

BACKUP Database demodb
TO      URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb_diff.bak'
WITH    FORMAT
       ,DIFFERENTIAL
       ,STATS = 10
       ,COMPRESSION 

SQL Server COPY_ONLY backup to AWS

This is supported as shown below.

BACKUP Database demodb
TO      URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb_copy.bak'
WITH    FORMAT
       ,COPY_ONLY
       ,STATS = 10
       ,COMPRESSION

SQL Server T-Log Backup to AWS

This is supported as shown below.

BACKUP Log demodb
TO      URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb_tlog.trn'
WITH    FORMAT
       ,STATS = 10
       ,COMPRESSION

SQL Server Encrypted Backup to AWS

This is supported as shown below.

We need a service master key and certificate that protects (encrypts) the database backup for the encrypted backup. The BACKUP DATABASE statement includes the encryption certificate name and encryption algorithm.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test@1234';
GO
 
CREATE CERTIFICATE DemoDB_cert WITH SUBJECT = 'DemoDB Backup Certificate';
GO
 
BACKUP database demodb
TO    URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb_encrypt.bak'
WITH  FORMAT
     ,MAXTRANSFERSIZE = 20971520
     ,COMPRESSION
     ,ENCRYPTION (ALGORITHM = AES_256,SERVER CERTIFICATE = DemoDB_cert)
GO

SQL Server FILE_SNAPSHOT Backup to AWS

This is not supported as shown below.

BACKUP database demodb
TO   URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb.bak'
WITH FILE_SNAPSHOT;

SQL Server MIRROR Backup to AWS

We can mirror a database backup on the S3 bucket using the MIRROR TO URL option.

BACKUP database demodb
TO     URL    = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb.bak'
MIRROR TO URL ='s3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb1.bak'
WITH   FORMAT
      ,COMPRESSION;

You cannot combine the S3 bucket and disk drive for mirror backups. If you do so, SQL Server 2022 raises the below error.

Next Steps

• Stay tuned for the next article on restoring a database from a backup stored in an AWS S3 bucket using SQL Server 2022.
• Read SQL Server documentation on Microsoft docs.
• Read these other tips on SQL Server 2022.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips