Sauvegarde SQL Server sur URL pour le stockage d’objets compatible S3

Article
05/02/2024

S’applique à : SQL Server 2022 (16.x)

Cet article présente les concepts, les exigences et les composants nécessaires pour utiliser le stockage d’objets compatible S3 comme destination de sauvegarde. La fonctionnalité de sauvegarde et de restauration est similaire d’un point de vue conceptuel à la sauvegarde SQL Server sur URL pour le Stockage Blob Azure comme type d’unité de sauvegarde.

Pour plus d’informations sur les plateformes prises en charge, consultez Fournisseurs de stockage d’objets compatible S3.

Vue d’ensemble

SQL Server 2022 (16.x) introduit l’intégration du stockage d’objets à la plateforme de données, vous permettant d’intégrer SQL Server au stockage d’objets compatible S3 en plus du Stockage Azure. Pour fournir cette intégration, SQL Server prend en charge un connecteur S3. Ce connecteur utilise l’API REST S3 pour se connecter à n’importe quel fournisseur de stockage d’objets compatible S3. SQL Server 2022 (16.x) étend la syntaxe BACKUP/RESTORE TO/FROM URL existante en ajoutant la prise en charge du nouveau connecteur S3 avec l’API REST.

Les URL pointant vers des ressources compatibles S3 sont précédées d’un préfixe s3:// pour indiquer que le connecteur S3 est utilisé. Les URL commençant par s3:// supposent toujours que le protocole sous-jacent est https.

Limitation du nombre de composants et de la taille des fichiers

Pour stocker des données, le fournisseur de stockage d’objets compatible S3 doit fractionner des fichiers dans plusieurs blocs appelés parties, comme les objets blob de blocs dans le Stockage Blob Azure.

Chaque fichier peut être divisé en 10 000 parties maximum. La taille d’un composant est comprise entre 5 Mo et 20 Mo, cette plage étant contrôlée par la commande Transact-SQL BACKUP avec le paramètre MAXTRANSFERSIZE. La valeur par défaut de MAXTRANSFERSIZE est 10 Mo, ce qui signifie que la taille par défaut de chaque partie est de 10 Mo.

La taille maximale prise en charge d’un seul fichier est le résultat de 10 000 parties × MAXTRANSFERSIZE. Il est nécessaire, pour sauvegarder un fichier plus volumineux, de le fractionner ou de le répartir sur plusieurs URL (64 maximum). La taille maximale finale prise en charge d’un fichier est de 10 000 parties × MAXTRANSFERSIZE × URL.

Remarque

Il est nécessaire d’utiliser COMPRESSION pour modifier les valeurs MAXTRANSFERSIZE.

Prérequis pour le point de terminaison S3

Le point de terminaison S3 doit être configuré comme suit :

Le protocole TLS doit être configuré. Il est supposé que toutes les connexions sont transmises de manière sécurisée par le protocole HTTPS et non HTTP. Le point de terminaison est validé par un certificat installé sur l’hôte de système d’exploitation SQL Server.
Identifiants créés sur le stockage d’objets compatible S3 avec les autorisations appropriées pour effectuer l’opération. L’utilisateur et le mot de passe créés sur la couche de stockage sont nommés Access Key ID et Secret Key ID. Les deux sont nécessaires pour l’authentification auprès du point de terminaison S3.
Au moins un compartiment a été configuré. Les compartiments ne peuvent pas être créés ni configurés à partir de SQL Server 2022 (16.x).

Sécurité

Autorisations de sauvegarde

Pour connecter SQL Server au stockage d’objets compatible S3, deux ensembles d’autorisations doivent être établis, l’un sur SQL Server et également sur la couche de stockage.

Sur SQL Server, le compte d’utilisateur utilisé pour émettre les commandes BACKUP et RESTORE doit figurer dans le rôle de base de données db_backupoperator avec les autorisations Modifier des informations d’identification.

Sur la couche de stockage :

Dans AWS S3, créez un rôle personnalisé et indiquez spécifiquement l’API S3 qui nécessite un accès. La sauvegarde et la restauration nécessitent ces autorisations : ListBucket (Parcourir), PutObject (Écriture : pour la sauvegarde).
Dans les autres stockages compatibles S3, l’utilisateur (Access Key ID) doit disposer des autorisations ListBucket et WriteOnly.

Autorisations de restauration

Si la base de données restaurée n'existe pas, l'utilisateur doit posséder les autorisations CREATE DATABASE afin de pouvoir exécuter RESTORE. Si la base de données existe, les autorisations RESTORE reviennent par défaut aux membres des rôles serveur fixes sysadmin et dbcreator, et au propriétaire (dbo) de la base de données.

Les autorisations RESTORE sont attribuées aux rôles dont les informations d'appartenance sont toujours immédiatement accessibles à partir du serveur. Étant donné que l’appartenance au rôle de base de données fixe ne peut être contrôlée que quand la base de données est accessible et non endommagée, ce qui n’est pas toujours le cas lorsque RESTORE est exécuté, les membres du rôle de base de données fixe db_owner ne détiennent pas d’autorisations RESTORE.

Sur la couche de stockage :

Dans AWS S3, créez un rôle personnalisé et indiquez spécifiquement l’API S3 qui nécessite un accès. La sauvegarde et la restauration nécessitent ces autorisations : ListBucket (Parcourir), GetObject (Lecture : pour la restauration).
Dans les autres stockages compatibles S3, l’utilisateur (Access Key ID) doit disposer des autorisations ListBucket et ReadOnly.

Fonctionnalités prises en charge

Vue d’ensemble générale des fonctionnalités prises en charge pour BACKUP et RESTORE :

Un seul fichier de sauvegarde peut atteindre 200 000 Mio par URL (avec MAXTRANSFERSIZE défini sur 20 Mo).
Les sauvegardes peuvent être réparties sur un maximum de 64 URL.
La mise en miroir est prise en charge, mais uniquement entre les URL. La mise en miroir à l’aide de l’URL et de DISK n’est pas prise en charge.
La compression est prise en charge et recommandée.
Le chiffrement est pris en charge.
La restauration à partir d’une URL avec stockage d’objets compatible S3 ne présente aucune limitation de taille.
Lorsque vous restaurez une base de données, la valeur MAXTRANSFERSIZE est déterminée par la valeur attribuée au cours de la phase de sauvegarde.
Les URL peuvent être spécifiées au format d’hôte virtuel ou de style de chemin.
WITH CREDENTIAL est pris en charge.
REGION est pris en charge. La valeur par défaut est us-east-1.
MAXTRANSFERSIZE varie de 5 Mo à 20 Mo. 10 Mo est la valeur par défaut du connecteur S3.

Arguments pris en charge pour la sauvegarde

`WITH` options	Point de terminaison S3	Notes
`BLOCKSIZE`	Y	`MAXTRANSFERSIZE` détermine la taille du composant.
`BUFFERCOUNT`	O
`COMPRESSION`	O
`COPY_ONLY`	O
`CREDENTIAL`	O
`DESCRIPTION`	O
`DIFFERENTIAL`	O
`ENCRYPTION`	O
`FILE_SNAPSHOT`	N
`MAXTRANSFERSIZE`	O	De 5 Mo (5 242 880 octets) à 20 Mo (20 971 520 octets), la valeur par défaut est 10 Mo (10 485 760 octets).
`MEDIADESCRIPTION`	A
`MEDIANAME`	O
`MIRROR TO`	A	Fonctionne uniquement avec une autre URL, `MIRROR` avec `URL` et `DISK` n’est pas pris en charge.
`NAME`	A
`NOFORMAT` / `FORMAT`	O
`NOINIT` / `INIT`	N	L’ajout n’est pas pris en charge. Pour remplacer une sauvegarde, utilisez `WITH FORMAT`.
`NO_CHECKSUM` / `CHECKSUM`	A
`NO_TRUNCATE`	O
`REGION`	A	La valeur par défaut est `us-east-1`. Doit être utilisé avec `BACKUP_OPTIONS`.
`STATS`	Y

Arguments pris en charge pour la restauration

`WITH` options	Point de terminaison S3	Notes
`BLOCKSIZE`	Y	`MAXTRANSFERSIZE` détermine la taille du composant.
`BUFFERCOUNT`	N
`CHECKSUM` / `NO_CHECKSUM`	O
`CREDENTIAL`	O
`ENABLE_BROKER` / `ERROR_BROKER_CONVERSATIONS` / `NEW_BROKER`	O
`FILE`	N	Noms logiques non pris en charge avec `RESTORE FROM URL`.
`FILESTREAM`	A
`KEEP_CDC`	O
`KEEP_REPLICATION`	O
`LOADHISTORY`	O
`MAXTRANSFERSIZE`	O
`MEDIANAME`	O
`MEDIAPASSWORD`	N	Obligatoire pour certaines sauvegardes effectuées dans les versions antérieures à SQL Server 2012.
`MOVE`	A
`PARTIAL`	O
`PASSWORD`	N	Obligatoire pour certaines sauvegardes effectuées dans les versions antérieures à SQL Server 2012.
`RECOVERY` / `NORECOVERY` / `STANDBY`	A
`REGION`	A	La valeur par défaut est `us-east-1`. Doit être utilisé avec `RESTORE_OPTIONS`.
`REPLACE`	A
`RESTART`	O
`RESTRICTED_USER`	O
`REWIND` / `NOREWIND`	N
`STATS`	O
`STOP_ON_ERROR` / `CONTINUE_AFTER_ERROR`	O
`STOPAT` / `STOPATMARK` / `STOPBEFOREMARK`	O
`UNLOAD` / `NOUNLOAD`	N

Région

Votre fournisseur de stockage d’objets compatible S3 peut offrir la possibilité de déterminer une région spécifique pour l’emplacement du compartiment. L’utilisation de ce paramètre facultatif peut offrir une plus grande flexibilité en spécifiant la région à laquelle appartient un compartiment particulier. Ce paramètre nécessite l’utilisation de WITH avec soit BACKUP_OPTIONS ou RESTORE_OPTIONS. Ces options nécessitent que la valeur soit déclarée au format JSON. Cela permet des scénarios dans lesquels un fournisseur de stockage compatible S3 peut avoir la même URL universelle, mais être réparti dans plusieurs régions. Dans ce cas, la commande de sauvegarde ou de restauration pointe vers les régions spécifiées sans avoir à modifier l’URL.

Si aucune valeur n’est déclarée, us-east-1 est affectée comme valeur par défaut.

Exemple de sauvegarde :

WITH BACKUP_OPTIONS = '{"s3": {"region":"us-west-1"}}'

Exemple de restauration :

WITH RESTORE_OPTIONS = '{"s3": {"region":"us-west-1"}}'

Prise en charge Linux

SQL Server utilise WinHttp pour implémenter le client des API REST HTTP qu’il utilise. Il s’appuie sur le magasin de certificats du système d’exploitation pour la validation des certificats TLS présentés par le point de terminaison http(s). Avec SQL Server sur Linux toutefois, l’autorité de certification doit être placée sur un emplacement prédéfini pour être créée à l’emplacement /var/opt/mssql/security/ca-certificates. Seuls les 50 premiers certificats peuvent être stockés et pris en charge dans ce dossier. L’autorité de confiance doit être en place avant le démarrage du processus SQL Server.

SQL Server lit les certificats à partir du dossier au démarrage et les ajoute au magasin de confiance.

Seuls les superutilisateurs doivent pouvoir écrire dans le dossier, tandis que l’utilisateur mssql doit avoir l’accès en lecture.

Fonctionnalités non prises en charge

La sauvegarde dans le stockage d’objets compatible S3 n’est pas prise en charge avec une URL http non sécurisée. Les clients doivent configurer leur hôte S3 avec une URL https. Ce point de terminaison est validé par un certificat installé sur l’hôte du système d’exploitation SQL Server.
La sauvegarde dans le stockage d’objets compatible S3 n’est pas prise en charge dans les éditions SQL Server Express et SQL Server Express avec Advanced Services.

Limites

Voici les limitations actuelles de la sauvegarde et de la restauration avec le stockage d’objets compatible S3 :

En raison de la limitation actuelle de l’API REST Standard S3, les fichiers de données temporaires non validés créés dans le magasin d’objets compatible S3 du client (en raison d’une opération de chargement en plusieurs parties en cours) pendant l’exécution de la commande Transact-SQL BACKUP ne sont pas supprimés en cas de défaillances. Ces blocs de données non validés restent conservés dans le stockage d’objets compatible S3 dans le cas où la commande BACKUP T-SQL échoue ou est annulée. Si la sauvegarde réussit, ces fichiers temporaires sont supprimés automatiquement par le magasin d’objets pour former le fichier de sauvegarde final. Certains fournisseurs de stockage compatible S3 gèrent les fichiers temporaires par le biais de leur système de récupérateur de mémoire.
La longueur totale de l’URL est limitée à 259 caractères. La chaîne complète est comptabilisée dans cette limitation, en incluant le nom du connecteur s3://. Par conséquent, la limite utilisable s’élève à 254 caractères. Nous vous recommandons toutefois de vous en tenir à une limite de 200 caractères pour permettre l’introduction possible des paramètres de requête.
Le nom des informations d’identification SQL est limité à 128 caractères au format UTF-16.
L’ID de clé secrète ne doit pas comporter le caractère :.

Style de chemin d’accès et style d’hôte virtuel

La sauvegarde vers S3 prend en charge l’URL à écrire dans le style de chemin d’accès ou le style d’hôte virtuel.

Exemple de style de chemin d’accès : s3://<endpoint>:<port>/<bucket>/<backup_file_name>

Exemple d’hôte virtuel : s3://<bucket>.<domain>/<backup_file_name>

Exemples

Créer des informations d’identification

Le nom des informations d’identification doit indiquer le chemin de stockage et il existe plusieurs normes pour cela en fonction de la plateforme de stockage.
IDENTITY doit toujours être 'S3 Access Key' en cas d’utilisation du connecteur S3.
L’ID de clé d’accès et l’ID de clé secrète ne doivent pas contenir de signe deux-points. L’ID de clé d’accès et l’ID de clé secrète sont l’utilisateur et le mot de passe créés sur le stockage d’objets compatible S3.
Seules les valeurs alphanumériques sont autorisées.
L’ID de clé d’accès doit disposer d’autorisations appropriées sur le stockage d’objets compatible S3.

Utilisez CREATE CREDENTIAL pour créer des informations d’identification au niveau du serveur pour l’authentification avec le point de terminaison de stockage d’objets compatible S3.

USE [master];
CREATE CREDENTIAL [s3://<endpoint>:<port>/<bucket>]
WITH
        IDENTITY    = 'S3 Access Key',
        SECRET      = '<AccessKeyID>:<SecretKeyID>';
GO

BACKUP DATABASE [SQLTestDB]
TO      URL = 's3://<endpoint>:<port>/<bucket>/SQLTestDB.bak'
WITH    FORMAT /* overwrite any existing backup sets */
,       STATS = 10
,       COMPRESSION;

Toutefois, AWS S3 prend en charge deux normes d’URL différentes.

S3://<BUCKET_NAME>.S3.<REGION>.AMAZONAWS.COM/<FOLDER> (valeur par défaut)
S3://S3.<REGION>.AMAZONAWS.COM/<BUCKET_NAME>/<FOLDER>

Il existe plusieurs approches pour créer des informations d’identification pour AWS S3.

-- S3 bucket name: datavirtualizationsample
-- S3 bucket region: us-west-2
-- S3 bucket folder: backup

CREATE CREDENTIAL [s3://datavirtualizationsample.s3.us-west-2.amazonaws.com/backup]
WITH    
        IDENTITY    = 'S3 Access Key'
,       SECRET      = 'accesskey:secretkey';
GO

BACKUP DATABASE [AdventureWorks2022]
TO URL  = 's3://datavirtualizationsample.s3.us-west-2.amazonaws.com/backup/AdventureWorks2022.bak'
WITH COMPRESSION, FORMAT, MAXTRANSFERSIZE = 20971520;
GO

Ou,

CREATE CREDENTIAL [s3://s3.us-west-2.amazonaws.com/datavirtualizationsample/backup]
WITH    
        IDENTITY    = 'S3 Access Key'
,       SECRET      = 'accesskey:secretkey';
GO

BACKUP DATABASE [AdventureWorks2022]
TO URL  = 's3://s3.us-west-2.amazonaws.com/datavirtualizationsample/backup/AdventureWorks2022.bak'
WITH COMPRESSION, FORMAT, MAXTRANSFERSIZE = 20971520;
GO

Sauvegarde vers une URL

L’exemple suivant illustre la sauvegarde complète de base de données, répartie sur plusieurs fichiers, sur le point de terminaison de stockage d’objets :

BACKUP DATABASE <db_name>
TO      URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak'
--
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak'
WITH    FORMAT -- overwrite
,       STATS               = 10
,       COMPRESSION;

Restaurer à partir de l'URL

L’exemple suivant illustre la restauration de base de données à partir de l’emplacement du point de terminaison de stockage d’objets :

RESTORE DATABASE <db_name>
FROM    URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak'
--
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak'
WITH    REPLACE -- overwrite
,       STATS  = 10;

Options de chiffrement et de compression

L’exemple suivant illustre la sauvegarde et la restauration de la base de données AdventureWorks2022 avec chiffrement, MAXTRANSFERSIZE égal à 20 Mo et compression :

CREATE MASTER KEY ENCRYPTION BY PASSWORD = <password>;
GO

CREATE CERTIFICATE AdventureWorks2022Cert
    WITH SUBJECT = 'AdventureWorks2022 Backup Certificate';
GO
-- Backup database
BACKUP DATABASE AdventureWorks2022
TO URL = 's3://<endpoint>:<port>/<bucket>/AdventureWorks2022_Encrypt.bak'
WITH FORMAT, MAXTRANSFERSIZE = 20971520, COMPRESSION,
ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = AdventureWorks2022Cert)
GO

-- Restore database
RESTORE DATABASE AdventureWorks2022
FROM URL = 's3://<endpoint>:<port>/<bucket>/AdventureWorks2022_Encrypt.bak'
WITH REPLACE

Sauvegarde et restauration à l’aide de la région

L’exemple suivant illustre la sauvegarde et la restauration de la base de données AdventureWorks2022 à l’aide de REGION_OPTIONS :

Vous pouvez paramétriser la région dans chaque commande BACKUP / RESTORE. Notez la chaîne de région spécifique à S3 dans les BACKUP_OPTIONS et RESTORE_OPTIONS, par exemple, '{"s3": {"region":"us-west-2"}}'. La région par défaut est us-east-1. Voici un exemple simple :

-- Backup Database
BACKUP DATABASE AdventureWorks2022
TO URL = 's3://<endpoint>:<port>/<bucket>/AdventureWorks2022.bak'
WITH BACKUP_OPTIONS = '{"s3": {"region":"us-west-2"}}'

-- Restore Database
RESTORE DATABASE AdventureWorks2022
FROM URL = 's3://<endpoint>:<port>/<bucket>/AdventureWorks2022.bak'
WITH 
  MOVE 'AdventureWorks2022' 
  TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2022.mdf'
, MOVE 'AdventureWorks2022_log' 
  TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2022.ldf'
, RESTORE_OPTIONS = '{"s3": {"region":"us-west-2"}}'

Par exemple :

-- S3 bucket name: datavirtualizationsample
-- S3 bucket region: us-west-2
-- S3 bucket folder: backup

CREATE CREDENTIAL   [s3://datavirtualizationsample.s3.amazonaws.com/backup]
WITH    
        IDENTITY    = 'S3 Access Key'
,       SECRET      = 'accesskey:secretkey';
GO

BACKUP DATABASE [AdventureWorks2022]
TO URL  = 's3://datavirtualizationsample.s3.amazonaws.com/backup/AdventureWorks2022.bak'
WITH
    BACKUP_OPTIONS = '{"s3": {"region":"us-west-2"}}' -- REGION AS PARAMETER)
, COMPRESSION, FORMAT, MAXTRANSFERSIZE = 20971520;
GO

RESTORE DATABASE AdventureWorks2022_1 
FROM URL = 's3://datavirtualizationsample.s3.amazonaws.com/backup/AdventureWorks2022.bak'
WITH 
  MOVE 'AdventureWorks2022' 
  TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2022_1.mdf'
, MOVE 'AdventureWorks2022_log' 
  TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2022_1.ldf'
, STATS = 10, RECOVERY
, REPLACE, RESTORE_OPTIONS = '{"s3": {"region":"us-west-2"}}'; -- REGION AS PARAMETER)
GO

Partager via

Sauvegarde SQL Server sur URL pour le stockage d’objets compatible S3

Vue d’ensemble

Limitation du nombre de composants et de la taille des fichiers

Prérequis pour le point de terminaison S3

Sécurité

Autorisations de sauvegarde

Autorisations de restauration

Fonctionnalités prises en charge

Arguments pris en charge pour la sauvegarde

Arguments pris en charge pour la restauration

Région

Prise en charge Linux

Fonctionnalités non prises en charge

Limites

Style de chemin d’accès et style d’hôte virtuel

Exemples

Créer des informations d’identification

Sauvegarde vers une URL

Restaurer à partir de l'URL

Options de chiffrement et de compression

Sauvegarde et restauration à l’aide de la région

Commentaires

Ressources supplémentaires

Partager via

Sauvegarde SQL Server sur URL pour le stockage d’objets compatible S3

Vue d’ensemble

Limitation du nombre de composants et de la taille des fichiers

Prérequis pour le point de terminaison S3

Sécurité

Autorisations de sauvegarde

Autorisations de restauration

Fonctionnalités prises en charge

Arguments pris en charge pour la sauvegarde

Arguments pris en charge pour la restauration

Région

Prise en charge Linux

Fonctionnalités non prises en charge

Limites

Style de chemin d’accès et style d’hôte virtuel

Exemples

Créer des informations d’identification

Sauvegarde vers une URL

Restaurer à partir de l'URL

Options de chiffrement et de compression

Sauvegarde et restauration à l’aide de la région

Contenu connexe

Commentaires

Ressources supplémentaires