Erasure Code Calculator
Reference Hardware
Backing Up SQL Server 2022 Databases to MinIO
on
BC/DR
Microsoft took a big leap forward when it added the S3 Connector and Polybase to SQL Server 2022. As a result, enterprises can tap into the multitude of data they have saved to object storage and use it to enrich SQL Server tables. They can also leverage object storage to back up SQL Server, another huge leap forward in openness and cloud-native flexibility.
Object storage dominates the backup and recovery world because it is reliable, scalable, high-performance and immutable. Veeam Backup and Replication v12 treats object storage as a first-class citizen, as does Commvault. All major backup software has embraced object storage because it is a cost-effective and secure backup storage that meets the demands of today's data-intensive AI applications. NAS and SAN can't provide the same performance and efficiency, leaving object storage such as MinIO as the only option for on-premise SQL Server backup.
This blog post will walk you through the process of implementing and testing the backup to URL feature of SQL Server 2022 deployed on Microsoft Windows. You will learn how to deploy SQL Server, provision MinIO buckets, create an IAM policy to secure access to these buckets and create a service account in MinIO. We'll run through SQL Server backup and restore using backup to URL. Finally, we'll set ILM policy to tier old backups to another bucket and delete even older backups.
Deploy SQL Server 2022 Enterprise Edition
If you have SQL Server 2022 deployed, then feel free to use that.
If not, please install one of the following, making sure to install Polybase as well:
You will also need a way to interact with SQL Server:
Make sure that SQL Server is running and connect to it using either of the tools mentioned above.
To make sure everything is installed properly, run the following query to obtain the SQL Server edition, version and product level, as shown below.
SELECT SERVERPROPERTY('Edition') AS SQLEdition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel;
Deploy and Configure MinIO
If you’re not already running it, please install MinIO on bare metal, Docker, or Kubernetes.
MinIO Client (mc) is required to access the MinIO Server. Here’s how to install mc locally.
Make note of the credentials and S3 endpoint displayed the first time you run MinIO Server because you will need them to configure SQL Server for the external data source.
Create MinIO Bucket
Log into MinIO using a web browser with the administrator credentials, IP address and port provided in the previous step.
After logging in, click Create Bucket and name your bucket sql-2022-backups. Make sure to enable versioning.
Repeat the above steps to create a second bucket named sql-2022-archives.
Configure IAM Bucket Policy
Create an Identity and Access Management (IAM) policy to define the specific permissions needed to interact with the bucket you created in the previous step. The JSON below provides permissions for s3:ListBucket, s3:PutObject and s3:GetObject. The Resource statement defines the buckets to which the policy applies, and the trailing */* means that the policy applies to all buckets and prefixes starting with sql-2022-backups.
In the MinIO browser, click Policies in the left-hand menu. Then click Create Policy.
Copy and paste the JSON below into the new policy. Name the policy Backup and click Save.
{
"Version": "2012-10-17",
"Statement": [
{
"Action": [
"admin:SetTier",
"admin:ListTier"
],
"Effect": "Allow",
"Sid": "EnableRemoteTierManagement"
},
{
"Action": [
"s3:PutLifecycleConfiguration",
"s3:GetLifecycleConfiguration"
],
"Resource": [
"arn:aws:s3:::*"
],
"Effect": "Allow",
"Sid": "EnableLifecycleManagementRules"
}
]
}Create Access Key
Create an Access Key and attach the IAM policy created in the previous step. You will create a key named sql-2022-backups-user.
From the left menu, click Access Keys and create a new key. Make note of the Access Key and Secret Key (click the eyeball icon to reveal the Secret Key.
Toggle on Restrict beyond user policy and then copy and paste the following JSON into the policy editor window.
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3:ListBucket"
],
"Resource": [
"arn:aws:s3:::sql-2022-backups*/*"
]
},
{
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:GetObject"
],
"Resource": [
"arn:aws:s3:::sql-2022-backups*/*"
]
}
]
} Create SQL Server Credentials
Configure SQL Server to use the Access Key and Secret Key from the previous step to create a SQL Server credential.
Run the following T-SQL query to create a credential with the destination bucket, Access Key and Secret Key. The SECRET parameter includes both the Access Key and Secret Key of the account created earlier separated by a colon.
CREATE CREDENTIAL [s3://<your-MinIO-server>:9000/sql-2022-backups]
WITH IDENTITY = 'S3 Access Key'
, SECRET = '<Access Key>:<Secret Key>';Backup SQL Server 2022 Database to MinIO
You will need a database to back up, any database will do. I'm using the WideWorldImporters (WWI) sample database from Microsoft. Please see Data Science and AI with a SQL Server 2022 Data Lakehouse for instructions to download and install the WWI database and Wide World Importers sample databases for Microsoft SQL for additional information.
The T-SQL BACKUP DATABASE command requires several arguments:
- TO URL = xxx the complete path to the bucket and backup file
- WITH FORMAT allows existing backup files to be overwritten. Without this argument, the backup will fail if a file already exists. With versioning enabled on the bucket, files are protected from deletion
- COMPRESSION enables SQL Server Compression to create and send the smallest possible backup file
- MAXTRANSFERSIZE=20971520 (20 MB) defines the size of the largest backup file in bytes for multi-part upload.
BACKUP DATABASE WideWorldImporters
TO URL = 's3://<your-minio-server>:9000/sql-2022-backups/WideWorldImporters.bak'
WITH FORMAT, COMPRESSION, MAXTRANSFERSIZE = 20971520;After finishing successfully, the query will return something like:
Processed 1608 pages for database 'WideWorldImporters', file 'WWI_Primary' on file 1.
Processed 53112 pages for database 'WideWorldImporters', file 'WWI_UserData' on file 1.
Processed 3865 pages for database 'WideWorldImporters', file 'WWI_InMemory_Data_1' on file 1.
Processed 347 pages for database 'WideWorldImporters', file 'WWI_Log' on file 1.
BACKUP DATABASE successfully processed 58932 pages in 8.771 seconds (52.491 MB/sec).
Completion time: 2024-01-10T17:04:36.6181820-08:00Versioned Backups
When using the FORMAT argument, the existing backup will be replaced by a new one, but the older version is still available via MinIO versioning.
Run the above backup command 2 more times. MinIO is immutable and never overwrites data. As a result, the previous versions are retained and you can see them with the --versions flag.
$ mc ls --versions myminio/sql-2022-backups/
[2024-01-11 17:39:03 PST] 1.3GiB STANDARD c635324e-e643-441c-970f-3da5308e8bbd v3 PUT WideWorldImporters.bak
[2024-01-11 17:33:33 PST] 1.3GiB STANDARD a5f73f90-eb68-4f09-b5eb-0643ca413ccf v2 PUT WideWorldImporters.bak
[2024-01-10 17:04:36 PST] 135MiB STANDARD f2d9a2b3-3d94-49a4-a835-d815a14fac32 v1 PUT WideWorldImporters.bakFor more information about working with versioned objects, please refer to Continuous Data Protection with MinIO Versioning and Rewind.
Restore a Database
In the event of failure, you'll have to restore SQL Server backups in a logical sequence, and then recover the database. You can restore a complete database, a data file or a data page. In Microsoft parlance, restore refers to the multi-phase process of copying data and logs from a backup to a database, while recover means to return a database to a stable, consistent and usable state.
We're covering complete database backup and restore in this example. This is the most basic backup strategy. It's possible to restore and recover a full database backup. However, it may be necessary to restore the full database and then restore a differential backup.
Restoring a database is as straightforward as backing it up. The Database Engine guarantees that the whole database is available and logically consistent by executing three steps during a database restore:
- Create the database and transaction log if they don't already exist
- Copy all data, log and index pages from the backup to the database files
- Applies the transaction log to recover
You have two options when restoring a database:
- RESTORE…WITH REPLACE overwrites an existing database when conducting a restore. SQL Server will ignore any active contents in the transaction log and simply restore the database backup.
- The second option is to restore as a new database, leaving the original intact.
To restore and replace a database:
RESTORE DATABASE WideWorldImporters
FROM URL = 's3://minio.example.net:9000/sql-2022-backups/wideworldimporters.bak'
WITH REPLACE;To restore a database as a new database:
RESTORE DATABASE [WideWorldImporters-copy]
FROM URL = 's3://minio.example.net:9000/sql-2022-backups/WideWorldImporters.bak'
WITH FILE = 1,
MOVE N'WideWorldImporters' TO N'C:\SQL2022\Data\WideWorldImporters-copy.mdf',
MOVE N'WideWorldImporters_log' TO N'C:\SQL2022\Log\WideWorldImporters-copy_log.ldf'Lifecycle Management for SQL Server 2022 Backups
Most enterprises don't simply back up all their databases (and keep all versions) to the same bucket. It isn't efficient to store old versions of backups on a high-performance hot tier, as they're likely to only be accessed in a database recovery scenario. We're going to move backups to an alternate storage tier based on retention policy.
We'll leverage MinIO's lifecycle management features to transition non-current versions of backups that are older than 10 days from the original bucket to a second bucket on a cluster with cold-tier (optimized for capacity) storage. We are going to use the free MinIO Play.
Create a bucket on a second MinIO cluster.
mc mb play/sql-2022-archivesCreate a user and policy for lifecycle management. Download and customize the JSON example policy, use the alias for the origin cluster and make sure to assign a secure secret key.
wget -O - https://min.io/docs/minio/linux/examples/LifecycleManagementAdmin.json | \
mc admin policy create myminio LifecycleAdminPolicy /dev/stdin
mc admin user add myminio myminioLifecycleAdmin jki234huihsdu23
mc admin policy attach myminio LifecycleAdminPolicy --user=myminioLifecycleAdminConfigure the remote storage tier
mc ilm tier add minio myminio ARCHIVE --endpoint https://play.min.io:9000 --access-key Q3AM3UQ867SPQQA43P2F --secret-key zuf+tfteSlswRu7BJ86wekitnifILbZam1KYY3TG --bucket sql-2022-backups --storage-class STANDARD --insecure
Added remote tier ARCHIVE of type minioCreate and apply the transition rule. You will specify the source cluster and bucket, the tier you are transitioning to, and transition rules. The command below creates a policy that transitions all non-current versions of backups that are older than 10 days to the tier you just created.
mc ilm rule add myminio/sql-2022-backups --noncurrent-transition-days 10 --noncurrent-transition-tier ARCHIVE
Lifecycle configuration rule added with ID `cmgr9qht2ketkof3o2h0` to myminio/sql-2022-backups.Finally, make sure the rule is configured correctly:
mc ilm rule ls myminio/sql-2022-backups
┌─────────────────────────────────────────────────────────────────────────┐
│ Transition for older versions (NoncurrentVersionTransition) │
├──────────────────────┬─────────┬────────┬──────┬──────────────┬─────────┤
│ ID │ STATUS │ PREFIX │ TAGS │ DAYS TO TIER │ TIER │
├──────────────────────┼─────────┼────────┼──────┼──────────────┼─────────┤
│ cmgr9qht2ketkof3o2h0 │ Enabled │ - │ - │ 10 │ ARCHIVE │
└──────────────────────┴─────────┴────────┴──────┴──────────────┴─────────┘For additional details, please see Transition Objects to Remote MinIO Deployment.
Backup to Multiple URLs
Microsoft states that you can improve performance by striping the backup across multiple objects and writing in parallel using multiple URLs. You can use up to 64 URLs, each URL equates to an object that is part of the backup. Currently, the size of a single backup file is limited to 100GB; if the database you want to back up is over 100GB then you will need to use multiple URLs.
All you need to do is add as many TO_URL parameters as stripes and SQL Server handles the rest. Backup names can be anything you want, except the parts need to be numbered in order.
Here's an example with 6 URLS
BACKUP DATABASE WideWorldImporters
TO URL = 's3://minio.example.net:9000/sql-2022-backups/wwi-part1.bak',
URL = 's3://minio.example.net:9000/sql-2022-backups/wwi-part2.bak',
URL = 's3://minio.example.net:9000/sql-2022-backups/wwi-part3.bak',
URL = 's3://minio.example.net:9000/sql-2022-backups/wwi-part4.bak',
URL = 's3://minio.example.net:9000/sql-2022-backups/wwi-part5.bak',
URL = 's3://minio.example.net:9000/sql-2022-backups/wwi-part6.bak'
WITH FORMAT, COMPRESSION, MAXTRANSFERSIZE = 20971520; After finishing successfully, the query will return something like:
Processed 2032 pages for database 'WideWorldImporters', file 'WWI_Primary' on file 1.
Processed 324296 pages for database 'WideWorldImporters', file 'WWI_UserData' on file 1.
Processed 82130 pages for database 'WideWorldImporters', file 'WWI_InMemory_Data_1' on file 1.
Processed 43238 pages for database 'WideWorldImporters', file 'WWI_Log' on file 1.
BACKUP DATABASE successfully processed 451696 pages in 67.187 seconds (52.523 MB/sec).
Completion time: 2024-01-11T17:27:15.0091707-08:00As you can see, using multiple URLs slowed down the backup in this case.
On-Premise SQL Server 2022 Backups
Performance and reliability are key factors in determining the success or failure of disaster recovery efforts. SQL Server 2022, with the ability to backup to and restore from object storage, leverages MinIO fully for a scalable, speedy and simple backup store.
MinIO provides a software-defined backup target that is simple to operate yet performant and scalable. The result is that SQL Server 2022 customers using MinIO as a backup target realize enormous cost savings and increased flexibility for on-premise backups.
Download MinIO today and learn about SQL Server 2022's object storage integrations. As always, reach out to us on Slack if you have any questions.