Backup di SQL Server nell'URL per l'archiviazione degli oggetti compatibile con S3

Articolo
04/18/2024

Si applica a: SQL Server 2022 (16.x)

Questo articolo introduce i concetti, i requisiti e i componenti necessari per usare l'archiviazione oggetti compatibile con S3 come destinazione di backup. La funzionalità di backup e ripristino è concettualmente simile all'uso del backup di SQL Server nell'URL di Archiviazione BLOB di Azure come tipo di dispositivo di backup.

Per informazioni sulle piattaforme supportate, vedere Provider di archiviazione oggetti compatibile con S3.

Panoramica

SQL Server 2022 (16.x) introduce l'integrazione dell'archiviazione di oggetti nella piattaforma dati, permettendoti di integrare SQL Server con l'archiviazione di oggetti compatibile con S3, oltre ad Archiviazione di Azure. Per offrire questa integrazione, SQL Server supporta un connettore S3, che usa l'API REST S3 per la connessione a qualsiasi provider di archiviazione di oggetti compatibile con S3. SQL Server 2022 (16.x) estende la sintassi esistente di BACKUP/RESTORE TO/FROM URL aggiungendo il supporto per il nuovo connettore S3 tramite l'API REST.

Gli URL che puntano a risorse compatibili con S3 sono preceduti da un prefisso s3:// che indica che viene usato il connettore S3. Gli URL che iniziano con s3:// presuppongono sempre che il protocollo sottostante sia https.

Limitazioni relative ai codici e alle dimensioni dei file

Per archiviare i dati, il provider di archiviazione oggetti compatibile con S3 deve suddividere i file in più blocchi denominati parti, in modo analogo ai BLOB in blocchi in Archiviazione BLOB di Azure.

Ogni file può essere suddiviso fino a 10.000 parti, le dimensioni delle quali vanno dai 5 MB ai 20 MB; questo intervallo è controllato dal comando T-SQL BACKUP tramite il parametro MAXTRANSFERSIZE. Il valore predefinito di MAXTRANSFERSIZE è 10 MB, pertanto la dimensione predefinita di ogni parte è 10 MB.

La dimensione massima supportata di un singolo file è il risultato di 10.000 parti * MAXTRANSFERSIZE, se è necessario eseguire il backup di un file di dimensioni maggiori, deve suddividere/eseguire lo striping fino a 64 URL. La dimensione massima finale supportata di un file è di 10.000 parti * * MAXTRANSFERSIZE URL.

Nota

L'uso di COMPRESSION è necessario per modificare i valori MAXTRANSFERSIZE.

Prerequisiti per l'endpoint S3

L'endpoint S3 deve essere configurato come segue:

È necessario configurare TLS. Si presuppone che tutte le connessioni vengano trasmesse in modo sicuro tramite HTTPS non HTTP. L'endpoint viene convalidato da un certificato installato nell'host del sistema operativo di SQL Server.
Credenziali create nell'archivio oggetti compatibile con S3 con autorizzazioni appropriate per eseguire l'operazione. L'utente e la password creati nel livello di archiviazione sono denominati Access Key ID e Secret Key ID. È necessario eseguire l'autenticazione a fronte dell'endpoint S3.
È stato configurato almeno un bucket. Non è possibile creare o configurare bucket da SQL Server 2022 (16.x).

Sicurezza

Autorizzazioni di backup

Per connettere SQL Server all'archiviazione oggetti compatibile con S3, è necessario stabilire due set di autorizzazioni, uno in SQL Server e anche nel livello di archiviazione.

Su SQL Server l'account utente usato per eseguire i comandi BACKUP o RESTORE deve essere associato il ruolo del database db_backupoperator con autorizzazioni Modifica qualsiasi credenziale .

Nel livello di archiviazione:

In AWS S3, creare un ruolo personalizzato e dichiarare in modo specifico per quale API S3 richieda l'accesso. Il backup e il ripristino richiedono queste autorizzazioni: ListBucket (Esplora), PutObject (Scrivi - per backup).
In un'altra risorsa di archiviazione compatibile con S3, l'utente (Access Key ID) deve avere entrambe le autorizzazioni ListBucket e WriteOnly.

Ripristinare le autorizzazioni

Se il database da ripristinare non esiste, l'utente deve avere le autorizzazioni CREATE DATABASE per eseguire un'istruzione RESTORE. Se il database esiste, le autorizzazioni per l'istruzione RESTORE vengono assegnate per impostazione predefinita ai membri dei ruoli predefiniti del server sysadmin e dbcreator e al proprietario (dbo) del database.

Le autorizzazioni per l'istruzione RESTORE vengono assegnate ai ruoli in cui le informazioni sull'appartenenza sono sempre disponibili per il server. Poiché è possibile controllare l'appartenenza ai ruoli predefiniti del database solo quando il database è accessibile e non è danneggiato, condizioni che non risultano sempre vere quando si esegue un'operazione RESTORE, i membri del ruolo predefinito del database db_owner non dispongono delle autorizzazioni per l'istruzione RESTORE.

Nel livello di archiviazione:

In AWS S3, creare un ruolo personalizzato e dichiarare in modo specifico per quale API S3 richieda l'accesso. Il backup e il ripristino richiedono queste autorizzazioni: ListBucket (Esplora), GetObject (Leggi - per il ripristino).
In un'altra risorsa di archiviazione compatibile con S3, l'utente (Access Key ID) deve avere entrambe le autorizzazioni ListBucket e ReadOnly.

Funzionalità supportate

Panoramica generale delle funzionalità supportate per BACKUP e RESTORE:

Un singolo file di backup può arrivare fino a 200.000 MiB per URL (con MAXTRANSFERSIZE impostato su 20 MB).
È possibile eseguire lo striping dei backup in un massimo di 64 URL.
Il mirroring è supportato, ma solo negli URL. Il mirroring con URL e DISK non è supportato.
La compressione è supportata e consigliata.
La crittografia è supportata.
Il ripristino dall'URL con l'archiviazione oggetti compatibile con S3 non presenta limitazioni di dimensioni.
Quando si ripristina un database, l'oggetto MAXTRANSFERSIZE viene determinato dal valore assegnato durante la fase di backup.
Gli URL possono essere specificati nel formato stile del percorso o dell’host virtuale.
WITH CREDENTIAL è supportato.
REGION è supportato e il valore predefinito è us-east-1.
MAXTRANSFERSIZE è compreso tra 5 MB e 20 MB. 10 MB è il valore predefinito per il connettore S3.

Argomenti supportati per il backup

Opzioni `WITH`	Endpoint S3	Note
`BLOCKSIZE`	Y	`MAXTRANSFERSIZE` determina le dimensioni della parte.
`BUFFERCOUNT`	S
`COMPRESSION`	Y
`COPY_ONLY`	Y
`CREDENTIAL`	Y
`DESCRIPTION`	Y
`DIFFERENTIAL`	Y
`ENCRYPTION`	Y
`FILE_SNAPSHOT`	N
`MAXTRANSFERSIZE`	Y	Da 5 MB (5.242.880 byte) a 20 MB (20.971.520 byte), il valore predefinito è 10 MB (10.485.760 byte).
`MEDIADESCRIPTION`	S
`MEDIANAME`	Y
`MIRROR TO`	S	Funziona solo con un altro URL, `MIRROR` con `URL` e `DISK` non è supportato.
`NAME`	S
`NOFORMAT` / `FORMAT`	Y
`NOINIT` / `INIT`	N	L’append non è supportato. Per sovrascrivere un backup, usare `WITH FORMAT`.
`NO_CHECKSUM` / `CHECKSUM`	S
`NO_TRUNCATE`	Y
`REGION`	S	Il valore predefinito è `us-east-1`. Deve essere usato con `BACKUP_OPTIONS`.
`STATS`	Y

Argomenti supportati per il ripristino

Opzioni `WITH`	Endpoint S3	Note
`BLOCKSIZE`	Y	`MAXTRANSFERSIZE` determina le dimensioni della parte.
`BUFFERCOUNT`	N
`CHECKSUM` / `NO_CHECKSUM`	S
`CREDENTIAL`	Y
`ENABLE_BROKER` / `ERROR_BROKER_CONVERSATIONS` / `NEW_BROKER`	Y
`FILE`	N	Nomi logici non supportati con `RESTORE FROM URL`.
`FILESTREAM`	S
`KEEP_CDC`	Y
`KEEP_REPLICATION`	Y
`LOADHISTORY`	Y
`MAXTRANSFERSIZE`	Y
`MEDIANAME`	Y
`MEDIAPASSWORD`	N	Obbligatorio per alcuni backup eseguiti nelle versioni precedenti a SQL Server 2012.
`MOVE`	S
`PARTIAL`	Y
`PASSWORD`	N	Obbligatorio per alcuni backup eseguiti nelle versioni precedenti a SQL Server 2012.
`RECOVERY` / `NORECOVERY` / `STANDBY`	S
`REGION`	S	Il valore predefinito è `us-east-1`. Deve essere usato con `RESTORE_OPTIONS`.
`REPLACE`	S
`RESTART`	Y
`RESTRICTED_USER`	Y
`REWIND` / `NOREWIND`	N
`STATS`	S
`STOP_ON_ERROR` / `CONTINUE_AFTER_ERROR`	Y
`STOPAT` / `STOPATMARK` / `STOPBEFOREMARK`	Y
`UNLOAD` / `NOUNLOAD`	N

Paese

Il provider di archiviazione oggetti compatibile con S3 può offrire la possibilità di determinare un'area specifica per la posizione del bucket. L'uso di questo parametro opzionale può offrire maggiore flessibilità poiché può essere specificata l'area a cui appartiene un determinato bucket. Questo parametro richiede l'uso di WITH insieme a BACKUP_OPTIONS o RESTORE_OPTIONS. Queste opzioni richiedono che il valore venga dichiarato in formato JSON. Ciò consente scenari in cui un provider di archiviazione compatibile con S3 può avere lo stesso URL universale, ma può essere distribuito in diverse aree. In questo caso, il comando di backup o ripristino punta alle aree specificate senza la necessità di modificare l'URL.

Se non viene dichiarato alcun valore, us-east-1 viene assegnato come predefinito.

Esempi di backup:

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

Esempi di ripristino:

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

Supporto di Linux

SQL Server usa WinHttp per implementare il client delle API REST HTTP usate. Si basa sull'archivio certificati del sistema operativo per le convalide dei certificati TLS presentati dall'endpoint http(s). Tuttavia, su SQL Server in Linux la CA deve essere inserita in un percorso predefinito da creare in /var/opt/mssql/security/ca-certificates, solo i primi 50 certificati possono essere archiviati e supportati in questa cartella. La CA deve essere eseguita, prima dell'avvio del processo di SQL Server.

SQL Server legge i certificati dalla cartella durante l'avvio e li aggiunge all'archivio attendibilità.

Solo l'utente con privilegi avanzati deve essere in grado di scrivere nella cartella, mentre l'utente mssql deve essere in grado di leggere.

Funzionalità non supportate

Il backup nell'archivio oggetti compatibile con S3 con un URL http non sicuro non è supportato. I clienti sono responsabili della configurazione dell'host S3 con un URL https e questo endpoint viene convalidato da un certificato installato nell'host del sistema operativo di SQL Server.
Il backup nell'archiviazione oggetti compatibile con S3 non è supportato nelle edizioni SQL Server Express e SQL Server Express con Advanced Services.

Limiti

Di seguito sono riportate le limitazioni correnti di backup e ripristino con l'archiviazione di oggetti compatibile con S3:

A causa della limitazione corrente dell'API REST S3 Standard, i file di dati temporanei di cui non è stato eseguito il commit creati nell'archivio oggetti compatibile con S3 del cliente (a causa di un'operazione di caricamento multiparte in corso) mentre il comando BACKUP T-SQL è in esecuzione, non vengono rimossi in caso di errori. Questi blocchi di dati non inviati rimangono nell'archiviazione oggetti compatibile con S3 nel caso in cui il comando BACKUP T-SQL abbia esito negativo o venga annullato. Se il backup ha esito positivo, questi file temporanei vengono rimossi automaticamente dall'archivio oggetti per formare il file di backup finale. Alcuni provider di archiviazione compatibili con S3 gestiscono file temporanei tramite il sistema di Garbage Collector.
La lunghezza totale dell'URL è limitata a 259 caratteri. La stringa completa viene conteggiata in questa limitazione, incluso il nome del connettore s3://. Perciò, il limite di utilizzo è di 254 caratteri. È tuttavia consigliabile attenersi a un limite di 200 caratteri per consentire la possibile introduzione dei parametri di query.
Il nome delle credenziali SQL è limitato a 128 caratteri in formato UTF-16.
L'ID chiave privata non deve avere un carattere :.

Stile percorso e stile host virtuale

Il backup in S3 supporta la scrittura dell’URL sia nello stile del percorso che nello stile dell’host virtuale.

Esempio di stile percorso: s3://<endpoint>:<port>/<bucket>/<backup_file_name>

Esempio di host virtuale: s3://<bucket>.<domain>/<backup_file_name>

Esempi

Creare le credenziali

Il nome delle credenziali deve fornire il percorso di archiviazione e sono disponibili più standard a seconda della piattaforma di archiviazione.
IDENTITY deve essere sempre 'S3 Access Key' quando si usa il connettore S3.
L'ID chiave di accesso e l'ID chiave privata non devono contenere i due punti. L'ID chiave di accesso e l'ID chiave privata sono l'utente e la password creati nell'archivio oggetti compatibile con S3.
Sono consentiti solo valori alfanumerici.
L'ID chiave di accesso deve disporre delle autorizzazioni appropriate per l'archiviazione di oggetti compatibile con S3.

Usare CREATE CREDENTIAL per creare credenziali a livello di server per l'autenticazione con l'endpoint di archiviazione oggetti compatibile con 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;

AWS S3 supporta tuttavia due diversi standard di URL.

S3://<BUCKET_NAME>.S3.<REGION>.AMAZONAWS.COM/<FOLDER> (predefinito)
S3://S3.<REGION>.AMAZONAWS.COM/<BUCKET_NAME>/<FOLDER>

Esistono più approcci per creare correttamente delle credenziali per 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

Oppure

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

Backup su URL

Nell'esempio seguente viene eseguito un backup completo del database nell'endpoint di archiviazione oggetti, con striping su più file:

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;

Ripristino da un URL

Nell'esempio seguente viene eseguito un ripristino del database dal percorso dell'endpoint di archiviazione oggetti:

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;

Opzioni per la crittografia e la compressione

Nell'esempio seguente viene illustrato come eseguire il backup e il ripristino del database AdventureWorks2022 con crittografia, MAXTRANSFERSIZE come 20 MB e compressione:

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

Usare l’area per il backup e il ripristino

Nell'esempio seguente viene illustrato come eseguire il backup e il ripristino del database AdventureWorks2022 usando REGION_OPTIONS:

È possibile parametrizzare l'area all'interno di ogni comando BACKUP / RESTORE. Si noti la stringa di area specifica di S3 in BACKUP_OPTIONS e RESTORE_OPTIONS, ad esempio '{"s3": {"region":"us-west-2"}}'. L'area predefinita è us-east-1. Un semplice esempio:

-- 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"}}'

Ad esempio:

-- 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

Condividi tramite

Backup di SQL Server nell'URL per l'archiviazione degli oggetti compatibile con S3

Panoramica

Limitazioni relative ai codici e alle dimensioni dei file

Prerequisiti per l'endpoint S3

Sicurezza

Autorizzazioni di backup

Ripristinare le autorizzazioni

Funzionalità supportate

Argomenti supportati per il backup

Argomenti supportati per il ripristino

Paese

Supporto di Linux

Funzionalità non supportate

Limiti

Stile percorso e stile host virtuale

Esempi

Creare le credenziali

Backup su URL

Ripristino da un URL

Opzioni per la crittografia e la compressione

Usare l’area per il backup e il ripristino

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

Backup di SQL Server nell'URL per l'archiviazione degli oggetti compatibile con S3

Panoramica

Limitazioni relative ai codici e alle dimensioni dei file

Prerequisiti per l'endpoint S3

Sicurezza

Autorizzazioni di backup

Ripristinare le autorizzazioni

Funzionalità supportate

Argomenti supportati per il backup

Argomenti supportati per il ripristino

Paese

Supporto di Linux

Funzionalità non supportate

Limiti

Stile percorso e stile host virtuale

Esempi

Creare le credenziali

Backup su URL

Ripristino da un URL

Opzioni per la crittografia e la compressione

Usare l’area per il backup e il ripristino

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive