Backup di SQL Server nell'URL per l'archiviazione degli oggetti compatibile 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.
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 IDeSecret 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
MAXTRANSFERSIZEimpostato 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
MAXTRANSFERSIZEviene 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 | Y | |
| COMPRESSION | Y | |
| COPY_ONLY | Y | |
| CREDENTIAL | Y | |
| DESCRIZIONE | 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 | Y | |
| MEDIANAME | Y | |
| MIRROR TO | Y | Funziona solo con un altro URL, MIRROR con URL e DISK non è supportato. |
| NOME | Y | |
| NOFORMAT/FORMAT | Y | |
| NOINIT/INIT | N | L’append non è supportato. Per sovrascrivere un backup, usare WITH FORMAT. |
| NO_CHECKSUM/CHECKSUM | Y | |
| NO_TRUNCATE | Y | |
| REGION | Y | Il valore predefinito è us-east-1. Deve essere usato con BACKUP_OPTIONS. |
| STATS (Statistiche) | Y |
Argomenti supportati per il ripristino
| Opzioni WITH | Endpoint S3 | Note |
|---|---|---|
| BLOCKSIZE | Y | MAXTRANSFERSIZE determina le dimensioni della parte. |
| BUFFERCOUNT | N | |
| CHECKSUM | NO_CHECKSUM | Y | |
| CREDENTIAL | Y | |
| ENABLE_BROKER | ERROR_BROKER_CONVERSATIONS | NEW_BROKER | Y | |
| FILE | N | Nomi logici non supportati con RESTORE FROM URL. |
| FILESTREAM | Y | |
| KEEP_CDC | Y | |
| KEEP_REPLICATION | Y | |
| LOADHISTORY | Y | |
| MAXTRANSFERSIZE | Y | |
| MEDIANAME | Y | |
| MEDIAPASSWORD | N | Obbligatorio per alcuni backup eseguiti prima di SQL Server 2012. |
| MOVE | Y | |
| PARTIAL | Y | |
| PASSWORD | N | Obbligatorio per alcuni backup eseguiti prima di SQL Server 2012. |
| RECOVERY | NORECOVERY | STANDBY | Y | |
| REGION | Y | Il valore predefinito è us-east-1. Deve essere usato con RESTORE_OPTIONS. |
| REPLACE | Y | |
| RESTART | Y | |
| RESTRICTED_USER | Y | |
| REWIND | NOREWIND | N | |
| STATS (Statistiche) | Y | |
| STOP_ON_ERROR | CONTINUE_AFTER_ERROR | Y | |
| STOPAT | STOPATMARK | STOPBEFOREMARK | Y | |
| UNLOAD | NOUNLOAD | N |
Area
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.
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
httpnon sicuro non è supportato. I clienti sono responsabili della configurazione dell'host S3 con un URLhttpse 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 questa operazione 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
Contenuto correlato
- 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
- CREATE CERTIFICATE (Transact-SQL)
- Backup di SQL Server nell’URL per Archiviazione BLOB di Microsoft Azure
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per