Muokkaa

Jaa

SQL Server backup and restore with S3-compatible object storage

  • Artikkeli

Applies to: SQL Server 2022 (16.x)

This article introduces the concepts, requirements, and components necessary to use S3-compatible object storage as a backup destination.

Overview

SQL Server 2022 (16.x) introduces object storage integration to the data platform, enabling you to integrate SQL Server with S3-compatible object storage in addition to Azure Storage. To provide this integration, SQL Server provides an S3 connector, which uses the S3 REST API to connect to any provider of S3-compatible object storage. SQL Server 2022 (16.x) extends the existing BACKUP/RESTORE TO/FROM URL syntax by adding support for the S3 connector using the REST API. For information on supported platforms, see providers of S3-compatible object storage.

This article contains information on using Backup to URL for S3-compatible object storage. To learn more about using Backup to URL for S3-compatible object storage, see SQL Server backup to URL for S3-compatible object storage.

Backup and Restore to S3-compatible storage

The BACKUP TO URL and RESTORE FROM URL syntax support the S3 connector. For more information on Backup to URL functionality, see:

Providers of S3-compatible object storage

There are many providers of S3-compatible object storage in the market today. Object storage is either provided as software-defined, as hardware appliances, or as a combination for hybrid cloud scenarios.

The following table provides a nonexhaustive summary of object storage providers offering an S3 endpoint as part of their solution.

Vendor (alphabetical) Offering
AWS AWS Simple Cloud Storage (S3)
Ceph Ceph
Cloudian HyperStore
Dell Technologies ECS Enterprise Object Storage
Hitachi Vantara Hitachi Content Platform for Cloud Scale
HPE HPE Ezmeral Data Fabric
MinIO Multicloud Object Storage
NetApp StorageGRID
ONTAP
Nutanix Nutanix Object Storage
Pure Storage Pure FlashBlade
Red Hat OpenShift Container Storage
Scality Scality Artesca
Weka Weka S3

Prerequisites for the S3 endpoint

The S3 endpoint must be configured as follows:

  • TLS must be configured. It's assumed that all connections will be securely transmitted over HTTPS not HTTP. SQL Server requires the certificate for this scenario.
  • A user (Access Key ID) must be configured and the secret (Secret Key ID) for that user is known to you. You need both to authenticate against the S3 endpoint.
  • At least one bucket must be configured. Buckets can't be created or configured inside SQL Server 2022 (16.x).

Performance best practices

Check with your S3-compatible object storage provider for guidance on performance best practices optimization, initial setup, and configuration. Due to a wide variety of solutions and setups, the recommended values for backup and restore parameters and throughput can change.

By using S3 parts in SQL Server 2022 (16.x), you can stripe your backup set to support files size up to 12.8 TB.

Known issues and limitations

Due to the current limitation of S3 Standard REST API, the temporary uncommitted data files aren't removed if there are failures. They can be created in the S3-compatible object store due to an ongoing multipart upload operation while the BACKUP T-SQL command is running. These uncommitted data blocks persist in the S3-compatible object storage in the case the BACKUP T-SQL command fails or is canceled. If the backup succeeds, the object store automatically removes these temporary files to form the final backup file. Some S3-providers handle temporary file cleanup through their garbage collector system.

Related content