Backup and restore to URL for S3-compatible object storage

September 29, 2022

Product
SQL

Part of the SQL Server 2022 blog series.

Backup and restore to simple storage service (S3)–compatible object storage is a new feature introduced in SQL Server 2022 that grants the user the capability to back up or restore their databases using S3-compatible object storage, whether that be on-premises, or in the cloud.

What is object storage?

Object storage, also known as object-based storage, is a relatively new storage architecture that manipulates data storage as distinct units, called objects. Object storage was built from the ground up to provide flexibility and scalability.

Unlike the traditional file system that uses a hierarchical file distribution system, object storage is kept in a single storehouse and embeds each data with its required metadata and custom identifier. This metadata customization and classification allows for benefits in data analytics and big data scenarios but is not restricted to it.

Object storage is provided either as software-defined, as hardware appliances, or as a combination for hybrid cloud scenarios. SQL Server 2022 is compatible with any object storage solution that is S3 Rest API compatible. That means that your object storage solution can run locally in your network, in the cloud, or hybrid.

How does it work?

Despite storage architectural differences for SQL Server, the backup and restore to S3 operation behaves in a very similar fashion to backup/restore to URL. the user needs to set up a credential and then execute the backup or restore operation. To differentiate from a traditional backup to URL to object storage, SQL Server 2022 uses “S3://” as an URL prefix.

For security reasons, an HTTPS endpoint must be established between SQL Server 2022 and the object storage provider, for which a certificate must be used and where self-signed certificates are supported. Access permissions must also be provided by the storage administrator.

A database backup file storage in object storage is just as compatible as any other database backup file.

Parts and file size limitations

Unlike traditional file systems, object storage splits and stores its data in blocks called parts, similar to the Azure block blobs when using Azure Blob Storage. Each part can vary its size from 5 MB to 20 MB, where the default value is 10 MB. This behavior is controlled by SQL Server through the parameter MaxTransferSize in combination with Compression.

Each backup URL can allocate up to 10,000 parts, so the file size limit for each URL is 10,000 parts * MaxTransferSize. SQL Server can split a single database backup up into 64 URLs with the final maximum supported being 10,000 parts * MaxTransferSize * 64 URLs.

It’s encouraged to test different MaxTransferSize values since it can offer different benefits based on the database and the network.

Benefits and common scenarios

There are two major benefits and use cases for backup and restore to S3-compatible object storage:

Resource optimization: Allows the users more flexibility for planning their backup strategy, mixing different storage offerings with different costs, and guarantees the best value.
Migration: The ability to seamlessly execute both back-ups and restore using S3 storage allows for easier database migration.

Next steps

Download the latest release of SQL Server 2022 if you haven’t already done so and check out the SQL Server 2022 Overview and What’s New references. There are many new features and improved functionalities being added to this release.