R2 is Cloudflare’s own implementation of AWS S3 storage, with some big benefits – one of them being no egress fees, which is great if you want to publish or distribute a lot of data (like I did with this demo database). In this post, I thought I’d briefly document how to set up R2, and how to use it to back up and restore your SQL Server databases.
You’ll need a Cloudflare account to follow along. The account and a lot of their services are free, but R2 storage obviously comes with a small cost. For scale, I’m running an almost-terabyte bucket at just a couple of dollars per month.
Creating an R2 bucket
In S3/R2 wording, a bucket is a logical storage unit, like a blob storage account in Azure. To create your first bucket, go to the Cloudflare dashboard,
- In the menu, under “R2” select “Overview“.
- Click the “Create bucket” button.
We’re going to call our bucket “test-bucket”
On the settings tab of your newly created bucket, you’ll find the S3 API location, which you’re going to need later on:
You can also do other things, like connecting a custom domain name, setting data retention rules, and more. If you want to make the bucket publicly accessible, this is where you would set that stuff up. Cloudflare handles all of the messy things like TLS certificate stuff for you.
You can upload files up to 300 MB right there in the Cloudflare dashboard using just your web browser, so if that’s all you want to do, feel free to skip the rest of this tutorial.
Creating the API key
In order to connect programmatically (using the S3 tooling or SQL Server), you’ll need to create an API key. From the overview page, in the upper-right corner, click “Manage R2 API Tokens“.
Click the “Create API token” button. Then specify the name of the token (any plaintext will do, this is just a label), along with the appropriate permissions. For best practice, apply the principle of least privilege!
Once you’ve created the token, you will be shown two different keys; we won’t be using the Cloudflare API token for this example, so what you’re looking for is the S3 client tokens.
Copy the Access Key ID and Secret Access Key and store them somewhere safe.
Installing and configuring the S3 command-line tools
Note: You probably won’t need this tool if all you want to do is native backups/restores in SQL Server 2022.
Install AWS S3 command-line tooling to work with your R2 buckets from the commandline or from an automation. The tooling is available for Linux, Mac OS and Windows, and works with R2 with just a few additional command-line parameters.
Once you’ve installed the CLI tool, you need to configure it with your keys. From a command prompt, run
aws configureWhen asked, enter the access key and secret access key that you copied in the previous step. You can leave the default region and output format values blank if you want.
Using the command-line tool
You can now use the command-line tool to perform basic tasks:
aws s3api list-buckets --endpoint-url https://e1c00000000000000000000000000000.r2.cloudflarestorage.com
aws s3api list-objects-v2 --endpoint-url https://e1c00000000000000000000000000000.r2.cloudflarestorage.com --bucket test-bucket
aws s3 cp D:\Folder\Something.bak s3://test-bucket/ --endpoint-url https://e1c00000000000000000000000000000.r2.cloudflarestorage.comLimitations
SQL Server 2022 introduces native backup and restore on S3 compatible storage, but there are some significant limitations that you should be aware of.
- The S3 protocol splits files into “parts” when uploading them, and each part has a maximum size of 20 MB (although the default is 10 MB). A file can consist of no more than 10,000 parts. You can backup a database to more than one file (URL). So effectively, the maximum size of your backup (after compression) is (number of parts) * (part size) * (number of files). Unless your backups are more than 200 GB (or 12 TB with multiple files), you may never run into this limitation in practice.
- On the other end of the size spectrum, S3 backup/restore is not available for Express Edition.
Setting up the SQL Server 2022 credential
Setting up your R2/S3 bucket in SQL Server, you need to create a credential that hold the access key and secret access key.
The name of the credential is constructed using the s3:// prefix, followed by
- the endpoint,
- the port (443 for https), and
- the bucket name, all separated by forward slashes.
So you get something like this:
CREATE CREDENTIAL [s3://e1c00000000000000000000000000000.eu.r2.cloudflarestorage.com:443/test-bucket]
WITH IDENTITY='S3 Access Key',
SECRET='69e5df00000000000000000000000000:02c7e0000000000000000000000000000000000000000000000000000000c9b8';
Note: the identity (the username) of the credential is always “S3 Access Key” and the secret is the access key and secret access key, separated by a colon.
Backing up to S3/R2 in SQL Server 2022
The backup itself is pretty straightforward and mainly follows the regular BACKUP DATABASE syntax:
BACKUP DATABASE [Something]
TO URL='s3://e1c00000000000000000000000000000.eu.r2.cloudflarestorage.com:443/test-bucket/Something_01.bak',
URL='s3://e1c00000000000000000000000000000.eu.r2.cloudflarestorage.com:443/test-bucket/Something_02.bak',
URL='s3://e1c00000000000000000000000000000.eu.r2.cloudflarestorage.com:443/test-bucket/Something_03.bak',
URL='s3://e1c00000000000000000000000000000.eu.r2.cloudflarestorage.com:443/test-bucket/Something_04.bak'
WITH FORMAT,
STATS=10,
COMPRESSION;
When you’re backing up to regular AWS S3 storage, you can specify the target region using the BACKUP_OPTIONS argument, but I could not get that to work with Cloudflare. Instead, Cloudflare seems to implement the region in their endpoint URL – note the “eu” part right before r2.cloudflarestorage.com.
Restoring from S3/R2 in SQL Server 2022
Same thing goes with RESTORE DATABASE:
RESTORE DATABASE [Something]
FROM URL='s3://e1c00000000000000000000000000000.eu.r2.cloudflarestorage.com:443/test-bucket/Something_01.bak',
URL='s3://e1c00000000000000000000000000000.eu.r2.cloudflarestorage.com:443/test-bucket/Something_02.bak',
URL='s3://e1c00000000000000000000000000000.eu.r2.cloudflarestorage.com:443/test-bucket/Something_03.bak',
URL='s3://e1c00000000000000000000000000000.eu.r2.cloudflarestorage.com:443/test-bucket/Something_04.bak'
WITH REPLACE, STATS=10;
1 comment