Backup van SQL Server naar een URL voor S3-compatibele objectopslag

van toepassing op: SQL Server 2022 (16.x)

In dit artikel worden de concepten, vereisten en onderdelen geïntroduceerd die nodig zijn voor het gebruik van S3-compatibele objectopslag als back-upbestemming. De functionaliteit voor back-up en herstel is conceptueel vergelijkbaar met het werken met SQL Server-back-up naar URL voor Azure Blob Storage als back-upapparaattype.

Zie providers van S3-compatibele objectopslagvoor informatie over ondersteunde platforms.

Overzicht

SQL Server 2022 (16.x) introduceert objectopslagintegratie in het gegevensplatform, zodat u SQL Server kunt integreren met S3-compatibele objectopslag naast Azure Storage. Om deze integratie te bieden, ondersteunt SQL Server een S3-connector, die gebruikmaakt van de S3 REST API om verbinding te maken met elke provider van S3-compatibele objectopslag. SQL Server 2022 (16.x) breidt de bestaande syntaxis van BACKUP/RESTORE TO/FROM URL uit door ondersteuning toe te voegen voor de nieuwe S3-connector met behulp van de REST API.

URL's die verwijzen naar S3-compatibele resources, worden voorafgegaan door s3:// aan te geven dat de S3-connector wordt gebruikt. URL's die beginnen met s3:// altijd aannemen dat het onderliggende protocol is https.

Beperkingen voor onderdeelnummers en bestandsgrootte

Voor het opslaan van gegevens moet de S3-compatibele objectopslagprovider bestanden splitsen in meerdere blokken die onderdelen worden genoemd, vergelijkbaar met blok-blobs in Azure Blob Storage.

Elk bestand kan worden gesplitst tot 10.000 onderdelen, elke onderdeelgrootte varieert van 5 MB tot 20 MB. Dit bereik wordt beheerd door de T-SQL BACKUP-opdracht via de parameter MAXTRANSFERSIZE. De standaardwaarde is MAXTRANSFERSIZE 10 MB, dus de standaardgrootte van elk onderdeel is 10 MB. Hoewel deze waarde de maximale overdrachtsgrootte aangeeft, garandeert dit niet dat elk verzonden deel 10 MB is. De grootte van het onderdeel wordt beïnvloed door de aangrenzing van gegevens. Als er bijvoorbeeld 4 MB aan gegevens grenzen aan 2 MB aan gegevens, wordt 6 MB verzonden nadat het minimale deel van 5 MB is bereikt. Alternatief, als er 12 MB aan aangrenzende data is, worden gegevens tot de maximale grootte (10 MB) verzonden en wordt de resterende 2 MB in het volgende deel verzonden. De S3-connector probeert altijd de maximale grootte van gegevens te verzenden, maar de waarde wordt MAXTRANSFERSIZE nooit overschreden.

De maximale ondersteunde grootte van één bestand is het resultaat van 10.000 onderdelen * MAXTRANSFERSIZE, als het nodig is om een back-up van een groter bestand te maken, moet het maximaal 64 URL's splitsen/stripen. De uiteindelijke maximale ondersteunde grootte van een bestand is 10.000 onderdelen * MAXTRANSFERSIZE * URL's.

Vereisten voor het S3-eindpunt

Het S3-eindpunt moet als volgt worden geconfigureerd:

• TLS moet worden geconfigureerd. Er wordt vanuit gegaan dat alle verbindingen veilig via HTTPS worden verzonden, niet via HTTP. Het eindpunt wordt gevalideerd door een certificaat dat is geïnstalleerd op de SQL Server OS Host.
• Inloggegevens aangemaakt voor de S3-compatibele objectopslag met werkende machtigingen om de bewerking uit te voeren. De gebruiker en het wachtwoord die op de opslaglaag zijn gemaakt, worden de Access Key ID en Secret Key ID genoemd. Je moet beide authentiseren bij het S3-eindpunt.
• Er is ten minste één bucket geconfigureerd. Buckets kunnen niet worden gemaakt of geconfigureerd vanuit SQL Server 2022 (16.x).

Veiligheid

Back-upmachtigingen

Als u SQL Server wilt verbinden met S3-compatibele objectopslag, moeten twee sets machtigingen tot stand worden gebracht, één op SQL Server en ook op de opslaglaag.

Op SQL Server moet het gebruikersaccount dat wordt gebruikt om back-up- of HERSTELopdrachten uit te geven, zich in de db_backupoperator databaserol bevinden met Machtigingen voor referenties wijzigen .

Op de opslaglaag:

• Maak in AWS S3 een aangepaste rol en geef specifiek aan welke S3-API toegang nodig heeft. Voor back-ups en herstel zijn deze machtigingen vereist: ListBucket (Bladeren), PutObject (Schrijven - voor back-up).
• In andere S3-compatibele opslag moet de gebruiker (Access Key ID) zowel de machtigingen ListBucket als WriteOnly hebben.

Machtigingen herstellen

Als de database die wordt hersteld niet bestaat, moet de gebruiker machtigingen hebben CREATE DATABASE om RESTORE uit te voeren. Als de database bestaat, worden de machtigingen VOOR HERSTELLEN standaard ingesteld op leden van de sysadmin en dbcreator vaste serverfuncties en de eigenaar (dbo) van de database.

HERSTELmachtigingen worden gegeven aan rollen waarin lidmaatschapsgegevens altijd direct beschikbaar zijn voor de server. Omdat het lidmaatschap van een vaste databaserol alleen kan worden gecontroleerd wanneer de database toegankelijk en onbeschadigd is, wat niet altijd het geval is wanneer RESTORE wordt uitgevoerd, hebben leden van de db_owner vaste databaserol geen MACHTIGINGEN VOOR HERSTELLEN.

Op de opslaglaag:

• Maak in AWS S3 een aangepaste rol en geef specifiek aan welke S3-API toegang nodig heeft. Voor back-ups en herstel zijn deze machtigingen vereist: ListBucket (Bladeren), GetObject (Lezen - voor herstel).
• In andere S3-compatibele opslag moet de gebruiker (Access Key ID) zowel de machtigingen ListBucket als ReadOnly hebben.

Ondersteunde functies

Overzicht op hoog niveau van de ondersteunde functies voor BACKUP en RESTORE:

1. Eén back-upbestand kan maximaal 200.000 MiB per URL zijn (met MAXTRANSFERSIZE ingesteld op 20 MB).
2. Back-ups kunnen worden verdeeld over maximaal 64 URLs.
3. Spiegeling wordt ondersteund, maar alleen tussen URL's. Spiegelen met zowel URL als DISK wordt niet ondersteund.
4. Compressie wordt ondersteund en aanbevolen.
5. Versleuteling wordt ondersteund.
6. Herstellen vanaf URL met S3-compatibele objectopslag heeft geen groottebeperking.
7. Wanneer u een database herstelt, wordt de waarde bepaald door de MAXTRANSFERSIZE waarde die tijdens de back-upfase is toegewezen.
8. URL's kunnen worden opgegeven in virtuele host-indeling of padstijl.
9. WITH CREDENTIAL wordt ondersteund.
10. REGION wordt ondersteund en de standaardwaarde is us-east-1.
11. MAXTRANSFERSIZE varieert van 5 MB tot 20 MB. 10 MB is de standaardwaarde voor de S3-connector.

Ondersteunde argumenten voor back-up

Ondersteunde argumenten voor herstellen

Regio

Uw S3-compatibele objectopslagprovider kan de mogelijkheid bieden om een specifieke regio voor de bucketlocatie te bepalen. Het gebruik van deze optionele parameter kan meer flexibiliteit bieden door op te geven tot welke regio die bepaalde bucket behoort. Deze parameter vereist het gebruik van WITH samen met een BACKUP_OPTIONS of RESTORE_OPTIONS. Voor deze opties moet de waarde worden gedeclareerd in JSON-indeling. Hierdoor kunnen scenario's worden gebruikt waarin een met S3 compatibele opslagprovider dezelfde universele URL kan hebben, maar kan worden gedistribueerd over verschillende regio's. In dit geval wijst de back-up- of herstelopdracht naar de opgegeven regio's zonder de URL te hoeven wijzigen.

Als er geen waarde wordt gedeclareerd, us-east-1 wordt deze standaard toegewezen.

Voorbeeld van back-up:

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

Voorbeeld van herstellen:

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

Linux-ondersteuning

SQL Server maakt gebruik van WinHttp om clients van de HTTP REST API's die het gebruikt te implementeren. Het is afhankelijk van het certificaatarchief van het besturingssysteem voor validaties van de TLS-certificaten die door het http(s) eindpunt worden gepresenteerd. SQL Server op Linux moeten de CA op een vooraf gedefinieerde locatie plaatsen bij /var/opt/mssql/security/ca-certificates; in deze map kunnen alleen de eerste 50 certificaten worden opgeslagen en ondersteund. De CA moet aanwezig zijn voordat het SQL Server-proces wordt gestart.

SQL Server leest de certificaten uit de map tijdens het opstarten en voegt ze toe aan het vertrouwensarchief.

Alleen de supergebruiker moet kunnen schrijven in de map, terwijl de mssql gebruiker moet kunnen lezen.

Niet-ondersteunde functies

• Back-ups maken van S3-compatibele objectopslag met een niet-beveiligde http URL wordt niet ondersteund. Klanten zijn verantwoordelijk voor het instellen van hun S3-host met een https URL en dit eindpunt wordt gevalideerd door een certificaat dat is geïnstalleerd op de SQL Server OS-host.
• Back-ups maken van S3-compatibele objectopslag wordt niet ondersteund in SQL Server Express en SQL Server Express met Advanced Services-edities.

Beperkingen

Hier volgen de huidige beperkingen van back-up en herstel met S3-compatibele objectopslag:

• Vanwege de huidige beperking van de S3 Standard REST API worden de tijdelijke, niet-verzonden gegevensbestanden die worden gecreëerd in het S3-compatibele objectarchief van de klant (vanwege een lopende uploadbewerking met meerdere onderdelen) niet verwijderd als er back-upfouten zijn terwijl de T-SQL-opdracht BACKUP wordt uitgevoerd. Deze niet-doorgevoerde gegevensblokken blijven behouden in S3-compatibele objectopslag als de T-SQL-opdracht BACKUP mislukt of wordt geannuleerd. Als de back-up slaagt, worden deze tijdelijke bestanden automatisch verwijderd door het objectarchief om het uiteindelijke back-upbestand te vormen. Sommige S3-compatibele opslagproviders verwerken tijdelijke bestanden via hun vuilnisopruimsysteem.
• De totale URL-lengte is beperkt tot 259 tekens. De volledige tekenreeks wordt geteld in deze limiet, inclusief de naam van de s3:// connector. De bruikbare limiet is dus 254 tekens. We raden u echter aan om een limiet van 200 tekens te houden om mogelijke introductie van queryparameters mogelijk te maken.
• De naam van de SQL-referentie wordt beperkt door 128 tekens in UTF-16-indeling.
• Geheime sleutel-id mag geen teken hebben : .

Padstijl en virtuele hoststijl

Back-up naar S3 maken ondersteunt het schrijven van URLs in zowel padstijl als virtuele hoststijl.

Voorbeeld van padstijl: s3://<endpoint>:<port>/<bucket>/<backup_file_name>

Voorbeeld van virtuele host: s3://<bucket>.<domain>/<backup_file_name>

Voorbeelden

Inloggegevens aanmaken

• De naam van de referentie moet het opslagpad opgeven en er zijn meerdere standaarden hiervoor, afhankelijk van het opslagplatform.
• De IDENTITEIT moet altijd 'S3 Access Key' zijn bij het gebruik van de S3-connector.
• De toegangssleutel-id en de geheime sleutel-id mogen geen dubbele punt bevatten. Toegangssleutel-id en geheime sleutel-id zijn de gebruiker en het wachtwoord die zijn gemaakt in de S3-compatibele objectopslag.
• Alleen alfanumerieke waarden zijn toegestaan.
• De toegangssleutel-id moet over de juiste machtigingen beschikken voor de S3-compatibele objectopslag.

Gebruik CREATE CREDENTIAL om een referentie op serverniveau te maken voor verificatie met het S3-compatibele eindpunt voor objectopslag.

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 ondersteunt echter twee verschillende standaarden voor URL's.

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

Er zijn meerdere manieren om een toegangsgegeven te creëren voor 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

Of

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

Back-up maken naar URL

In het volgende voorbeeld wordt een volledige databaseback-up uitgevoerd naar het eindpunt van de objectopslag, gestreept over meerdere bestanden:

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;

Herstellen vanuit URL

In het volgende voorbeeld wordt een databaseherstel uitgevoerd vanaf de locatie van het eindpunt van de objectopslag:

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;

Opties voor versleuteling en compressie

In het volgende voorbeeld ziet u hoe u een back-up van de AdventureWorks2022 database maakt en herstelt met versleuteling, MAXTRANSFERSIZE als 20 MB en compressie:

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

Regio gebruiken voor back-up en herstel

In het volgende voorbeeld ziet u hoe u een back-up van de AdventureWorks2022 database maakt en herstelt met behulp van REGION_OPTIONS:

U kunt de regio binnen elke BACKUP / RESTORE opdracht parameteriseren. Noteren de S3-specifieke regiotekenreeks in BACKUP_OPTIONS en RESTORE_OPTIONS, bijvoorbeeld '{"s3": {"region":"us-west-2"}}'. De standaardregio is us-east-1. Een eenvoudig voorbeeld:

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

Voorbeeld:

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

Verwante inhoud

• nl-NL: SQL Server back-up naar een URL voor S3-compatibele objectopslag: best practices en probleemoplossing
• SQL Server back-up maken naar URL voor best practices voor Microsoft Azure Blob Storage en het oplossen van problemen met
• CERTIFICAAT MAKEN (Transact-SQL)
• Backup van SQL Server naar een URL voor Microsoft Azure Blob Storage