SQL Server backup and restore with S3-compatible object storage
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:
- SQL Server backup to URL for S3-compatible object storage.
- BACKUP (Transact-SQL)
- SQL Server back up to URL best practices and troubleshooting
- Blog: SQL Server Backup to URL – a cheat sheet
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
- SQL Server backup to URL for S3-compatible object storage
- SQL Server back up to URL for S3-compatible object storage best practices and troubleshooting
- SQL Server Backup to URL Best Practices and Troubleshooting
- SQL Server Backup to URL for Microsoft Azure Blob Storage
- Back Up and Restore of System Databases (SQL Server)
- Tutorial: Use Azure Blob Storage with SQL Server databases