Backup di SQL Server in un URL per l'archiviazione di oggetti compatibili con S3

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.

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.

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. Sebbene questo valore specifichi la dimensione massima del trasferimento, non garantisce che ogni parte inviata sia di 10 MB. La dimensione della parte è influenzata dall'adiacenza dei dati. Ad esempio, se sono presenti 4 MB di dati adiacenti a 2 MB di dati, vengono inviati 6 MB dopo aver raggiunto la parte minima delle dimensioni di 5 MB. In alternativa, se sono presenti 12 MB di dat adiacenti, i dati fino alle dimensioni massime (10 MB) vengono inviati e i rimanenti 2 MB vengono inviati nella parte successiva. Il connettore S3 tenta sempre di inviare le dimensioni massime dei dati possibili, ma non supera mai il MAXTRANSFERSIZE valore.

La dimensione massima di un file supportata è determinata dal risultato di 10.000 parti * MAXTRANSFERSIZE. Se è necessario eseguire il backup di un file più grande, deve essere suddiviso o distribuito in fino a 64 URL. La dimensione massima finale supportata di un file è di 10.000 parti * * MAXTRANSFERSIZE URL.

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

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.

Se il database da ripristinare non esiste, l'utente deve disporre CREATE DATABASE delle autorizzazioni per poter eseguire 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é l'appartenenza al ruolo predefinito del database può essere controllata solo quando il database è accessibile e non danneggiato, che non è sempre il caso in cui viene eseguito RESTORE, i membri del ruolo predefinito del db_owner database non dispongono delle autorizzazioni 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.

Panoramica generale delle funzionalità supportate per BACKUP e RESTORE:

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

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

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.

• Eseguire il backup su un'archiviazione oggetti compatibile con S3 con un URL non sicuro http 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 verso l'archiviazione oggetti compatibile con S3 non è supportato nelle edizioni SQL Server Express e SQL Server Express con Advanced Services.

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 di backup. 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 :.

Il backup su S3 supporta l'URL da scrivere, sia nella modalità PATH style, sia nella modalità Virtual Host style.

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

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

• 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

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;

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;

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

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

• Procedure consigliate e risoluzione dei problemi di archiviazione oggetti compatibile con S3 per il backup di SQL Server nell'URL
• Procedure consigliate e risoluzione dei problemi per il backup di SQL Server nell'URL per Archiviazione BLOB di Microsoft Azure
• CREA CERTIFICATO (Transact-SQL)
• Backup di SQL Server nell’URL per Archiviazione BLOB di Microsoft Azure