SQL Server 2022 (16.x) is now in preview, and it introduces a bunch of new features. One of the coolest features I found is the backup and restore to a URL for S3-compatible object storage.
Object storage integration enables you to integrate SQL Server with S3-compatible object storage.
In this article, I will show how to configure the S3 REST API to connect to any provider of S3-compatible object storage, and then I will perform a backup and restore a database from SQL Server 2022.
Before starting, we need to review the supported features:
- A single backup file can be up to 200,000 MiB per URL (with MAXTRANSFERSIZE set to 20 MB).
- Backups can be striped across a maximum of 64 URLs.
- Mirroring is supported, but only across URLs. Mirroring using both URL and DISK is not supported.
- Compression is supported and recommended.
- Encryption is supported.
- Restore from URL with S3-compatible object storage has no size limitation.
- When restoring a database, the MAXTRANSFERSIZE is determined by value assigned during the backup phase.
- URLs can be specified either in virtual host or path style format.
- WITH CREDENTIAL is supported.
- REGION is supported and the default value is us-east-1.
- MAXTRANSFERSIZE will range from 5 MB to 20 MB. 10 MB is the default value for the S3 connector.
Now, let’s start with an example.
Creating an AWS S3
From the AWS Console, go to the services menu > Storage > S3. Click Create Bucket.
Enter the bucket name. The bucket name must be globally unique and must not contain spaces or uppercase letters.
Chose the AWS region and click Create Bucket.
For this example, I created a bucket called vnasqlbackup:
Once the bucket is created, I will add a new folder called backups. This is just to keep my backup files in order.
AWS User and privileges
From the AWS Console, go to the services menu > Security > Identity & Compliance > IAM. Go to the Users menu and click Add Users button.
Enter the User name (I used sqlbackup) and check the option Access Key – Programmatic Access to enable an Access Key Id. We will use the Access Key ID and the Secret Access Key later for the SQL Server Credential.
In the next step, select Attach Existing policies directly and check the AmazonS3FullAccess policy. You can use the search bar to find the policy.
Leave all the remaining options as default and click Create User. You will see a screen with the user details. Download the .csv file with the Access Key ID and value. For purposes of this example, I will use the following data:
Access key ID: AKIA5U2W434LRDBGEEJD
Secret access key: nyqxvp6378Gq7dk0DSFZD81b9hX0TcIt/cmi2aDO
Note: Never reveal the IAM access id and secret key. I am doing it for demonstration purposes only.
Create credentials in SQL Server 2022
Connect to your SQL Server 2022 instance and create a new credential with the following syntax:
CREATE CREDENTIAL [s3://<endpoint>:<port>/<bucket>/<folder>]
WITH
IDENTITY = 'S3 Access Key'
,SECRET = '<AccessKeyID>:<SecretKeyID>';
I took the bucket’s URL from the AWS console: vnasqlbackup.s3.us-west-2.amazonaws.com/backups/
Using the data from the CSV file of the user, I will define the SECRET: AKIA5U2W434LRDBGEEJD: nyqxvp6378Gq7dk0DSFZD81b9hX0TcIt/cmi2aDO
The credential will be as follows:
Execute the commands, and the credential will be ready to use for backups and restores.
Backup to URL
The syntax for backing up a database is quite simple. Use the following statement:
BACKUP DATABASE [Database] TO URL = 'Credential\Backupfile.bak'
You can use any WITH option. Just remember the supported features at the beginning of this article.
I will create a simple backup for a database called VNADB:
BACKUP DATABASE [VNADB] TO URL = 's3://vnasqlbackup.s3.us-west-2.amazonaws.com/backups/VNADB.bak'
WITH NOFORMAT,
NOINIT,
SKIP,
NOREWIND,
NOUNLOAD, STATS = 10
GO
Once the backup has finished, we can check the file in our S3 bucket:
One more example: I will create a mirror database backup using the same TO URL statement.
BACKUP DATABASE [VNADB] TO URL = 's3://vnasqlbackup.s3.us-west-2.amazonaws.com/backups/VNADB_1.bak'
MIRROR TO URL = 's3://vnasqlbackup.s3.us-west-2.amazonaws.com/backups/VNADB_M.bak'
WITH FORMAT,
COMPRESSION;
GO
And now, I have three copies of my backup in the S3 bucket:
Restore from URL
The Restore command uses the same syntax as Backup.
We can restore any, full, differential, and log backup from the AWS S3 bucket.
Use the following syntax to restore your database. I’m using the REPLACE option to replace my current VNADB database.
Restoring from an S3 Bucket has the same restrictions mentioned before, and you must have the required privileges to restore a database.
As you can see, SQL Server 2022 extends the existing BACKUP/RESTORE TO/FROM URL syntax by adding support for the new S3 connector using the REST API. Several cloud vendors provide S3 storage including Amazon, Cloudian, Dell, Hitachi, HPE, Netapp, Nutanix, Pure Storage, Red Hat, and more.
You can download the Evaluation Edition from here Get SQL Server 2022 Release Candidate (RC 1) Evaluation Edition. Or, install it from any Azure SQL flavor available.
Additional notes:
- At least one bucket has been configured. Buckets cannot be created or configured from SQL Server 2022 (16.x).
- To connect SQL Server to an S3-compatible object storage, two sets of permissions need to be established - one on SQL Server and also on the storage layer.
- In this article, I’m assuming all the network and firewall rules are already configured to reach an S3 bucket from my on-premise SQL Server 2022 instance.
- For a complete reference of the feature review, check this out: SQL Server backup to URL for S3-compatible object storage
SUBMIT YOUR COMMENT