Utilizzo dei backup di Microsoft SQL Server per il recupero point-in-time su Compute Engine

Last reviewed 2023-06-27 UTC

---

Nota: questo documento o questa sezione include riferimenti a uno o più termini che Google considera irrispettosi o offensivi. I termini vengono utilizzati perché sono parole chiave nel software descritto nel documento.

I termini: master

In questo tutorial eseguirai backup su un'istanza SQL Server di Compute Engine. Il tutorial mostra come gestire questi backup e archiviarli in Cloud Storage e come ripristinare un database in un determinato momento.

Questo tutorial è utile se sei un amministratore di sistema, uno sviluppatore, un ingegnere, un amministratore di database o un ingegnere DevOps che vuole eseguire il backup dei dati di SQL Server.

Nota: sebbene la gestione dei backup sia fondamentale per qualsiasi piano di ripristino di emergenza, questo tutorial non tratta l'argomento del ripristino di emergenza per SQL Server.

Il tutorial presuppone che tu conosca le seguenti nozioni:

• Microsoft Windows
• Microsoft SQL Server
• Backup di log completi, differenziali e transazioni SQL Server
• Compute Engine
• Cloud Storage

Obiettivi

• Avvia un'istanza SQL Server e crea un database.
• Esegui backup dei log completi, differenziali e delle transazioni.
• Caricare i backup in Cloud Storage.
• Ripristina il database da un backup di Cloud Storage.

Costi

In questo documento vengono utilizzati i seguenti componenti fatturabili di Google Cloud:

• Compute Engine
• Networking
• Cloud Storage
• SQL Server (premium con Compute Engine)

Per generare una stima dei costi in base all'utilizzo previsto, utilizza il Calcolatore prezzi. I nuovi utenti di Google Cloud possono essere idonei a una prova senza costi aggiuntivi.

Prima di iniziare

1. Accedi al tuo account Google Cloud. Se non conosci Google Cloud, crea un account per valutare le prestazioni dei nostri prodotti in scenari reali. I nuovi clienti ricevono anche 300 $di crediti gratuiti per l'esecuzione, il test e il deployment dei carichi di lavoro.

2. Nella pagina del selettore di progetti della console Google Cloud, seleziona o crea un progetto Google Cloud.

   Nota: se non prevedi di conservare le risorse create in questa procedura, crea un progetto anziché selezionarne uno esistente. Dopo aver completato questi passaggi, puoi eliminare il progetto, rimuovendo tutte le risorse associate.

   Vai al selettore progetti

3. Assicurati che la fatturazione sia attivata per il tuo progetto Google Cloud.

4. Attiva l'API Compute Engine.

   Abilita l'API

5. Nella pagina del selettore di progetti della console Google Cloud, seleziona o crea un progetto Google Cloud.

   Nota: se non prevedi di conservare le risorse create in questa procedura, crea un progetto anziché selezionarne uno esistente. Dopo aver completato questi passaggi, puoi eliminare il progetto, rimuovendo tutte le risorse associate.

   Vai al selettore progetti

6. Assicurati che la fatturazione sia attivata per il tuo progetto Google Cloud.

7. Attiva l'API Compute Engine.

   Abilita l'API

8. Installa un client RDP (Remote Desktop Protocol) a tua scelta. Per ulteriori informazioni, consulta la pagina relativa ai client di Microsoft Remote Desktop. Se hai già installato un client RDP, puoi saltare questa attività.

Una volta completate le attività descritte in questo documento, puoi evitare la fatturazione continua eliminando le risorse che hai creato. Per ulteriori informazioni, consulta la pagina Pulizia.

Preparazione dell'istanza SQL Server

In questa sezione avvierai l'istanza SQL Server, preparerai il database e configurerai una chiave di crittografia.

Avvia l'istanza SQL Server

La prima attività prevede l'avvio di un'istanza SQL Server e la creazione della cartella di backup.

1. Apri Cloud Shell:

   VAI A Cloud Shell

2. Avvia un'istanza SQL Server:

   gcloud compute instances create sqlserver \
       --zone=us-central1-c \
       --machine-type=n1-standard-1 \
       --image-family=sql-std-2019-win-2019 \
       --image-project=windows-sql-cloud \
       --boot-disk-size=50GB \
       --boot-disk-type=pd-standard \
       --tags=sqlserver \
       --scopes=https://www.googleapis.com/auth/cloud-platform
   

3. Vai alla pagina Istanze VM nella console Google Cloud e trova l'istanza Windows a cui vuoi connetterti:

   VAI ALLA PAGINA ISTANZE VM

4. Imposta la password iniziale per l'istanza. Conserva la password in un luogo sicuro.

5. Nella sezione Compute Engine della console Google Cloud, fai clic sul menu a discesa RDP e seleziona l'opzione Scarica il file RDP per scaricare il file RDP per l'istanza. Utilizza questo file per connetterti all'istanza mediante un client RDP. Per ulteriori informazioni, vedi Client Microsoft Remote Desktop.

Installa SQL Server Management Studio

Installa Microsoft SQL Server Management Studio (SSMS) procedendo nel seguente modo:

1. Nella sessione RDP, riduci a icona tutte le finestre e avvia l'app Windows PowerShell ISE.

2. Al prompt di PowerShell, scarica ed esegui il programma di installazione di SSMS:

   Start-BitsTransfer `
       -Source "https://aka.ms/ssmsfullsetup" `
       -Destination "$env:Temp\ssms-setup.exe"
   & $env:Temp\ssms-setup.exe
   

3. Accetta la richiesta di consentire l'applicazione di modifiche.

4. Nel programma di installazione SSMS, fai clic su Installa.

5. Al termine dell'installazione, fai clic su Riavvia per riavviare la macchina remota. La sessione RDP si chiude.

6. Per riconnetterti, fai clic su Connetti nella finestra RDP. Se la macchina remota non ha terminato il riavvio, attendi qualche istante e riprova a connetterti.

7. Inserisci il nome utente e la password salvati in precedenza (lascia vuoto il campo Dominio), quindi fai clic su OK per riconnetterti.

Prepara le cartelle di backup e ripristino

1. Nella sessione RDP, riduci a icona tutte le finestre, quindi apri Google Cloud SDK Shell (non la stessa di Cloud Shell) sul desktop di Windows.

2. Crea una cartella di backup:

   mkdir c:\backup
   

3. Crea una cartella di ripristino:

   mkdir c:\restore
   

Prepara il database

1. In Cloud Shell, sull'istanza, crea un database di test:

   osql -E -Q "create database testdb"
   

   Nota: se viene visualizzato un messaggio di accesso non riuscito, esegui Google Cloud SDK Shell come amministratore.

2. Creare una tabella di test:

   osql -E -Q "create table testdb.dbo.testtable(status varchar(255))"
   

Configura la chiave di crittografia

1. In Cloud Shell, crea una chiave di database primaria:

   osql -E -Q "USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword!';"
   

   Nota: in questo tutorial utilizzerai la password MyPassword! per la chiave di crittografia, ma puoi scegliere la password che preferisci.

2. Crea un certificato di backup:

   osql -E -Q "USE master; CREATE CERTIFICATE testdbcert WITH SUBJECT = 'testdb certificate';"
   

Esecuzione dei backup

SQL Server 2019 e versioni precedenti

In questa sezione creerai backup completi dei log, differenziali e delle transazioni mentre modifichi il database da uno all'altro.

1. Aggiungi una riga alla tabella di test:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('Initial')"
   

2. In Cloud Shell, esegui un backup completo:

   osql  -E -Q "BACKUP DATABASE testdb TO DISK='c:\backup\testdb.bak' WITH INIT,
       COMPRESSION,
       ENCRYPTION
       (
           ALGORITHM = AES_256,
           SERVER CERTIFICATE = testdbcert
       )  "
   

   Nota: riceverai un avviso che ti informa che non hai eseguito il backup del certificato. Il backup del certificato non rientra nell'ambito di questo documento. Per saperne di più, consulta CERTIFICATO DI BACKUP (Transact-SQL).

3. Aggiungi una riga alla tabella di test:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('After Full Backup')"
   

4. Esegui un backup differenziale:

   osql  -E -Q "BACKUP DATABASE testdb TO DISK='c:\backup\testdb-diff.bak' WITH DIFFERENTIAL,
   COMPRESSION,
   ENCRYPTION
       (
       ALGORITHM = AES_256,
       SERVER CERTIFICATE = testdbcert
       ) "
   

5. Aggiungi una riga alla tabella di test:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('After Diff Backup')"
   

6. Esegui il backup dei log delle transazioni:

   osql  -E -Q "BACKUP LOG testdb TO DISK='c:\backup\testdb-log.bak' WITH COMPRESSION,
   ENCRYPTION
       (
       ALGORITHM = AES_256,
       SERVER CERTIFICATE = testdbcert
       ) "
   

7. Aggiungi una riga alla tabella di test:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('Bad Row')"
   

8. Verifica le righe della tabella:

   osql -E -Q "select * from testdb.dbo.testtable"
   

   L'output è simile al seguente:

   Initial
   
   After Full Backup
   
   After Diff Backup
   
   Bad Row
   

SQL Server 2022

In questa sezione creerai backup completi dei log, differenziali e delle transazioni mentre modifichi il database da uno all'altro. I comandi di backup e ripristino integrati si usano in Cloud Storage.

SQL Server 2022 (16.x) supporta l'integrazione dell'archiviazione estesa di oggetti introducendo un nuovo connettore che utilizza un'API REST per connettersi a qualsiasi provider di archiviazione di oggetti compatibile con S3. Puoi utilizzare i comandi BACKUP TO URL e RESTORE FROM URL in SQL Server 2022 con qualsiasi destinazione di archiviazione compatibile con S3 come URL.

SQL Server utilizza le credenziali per connettersi alle risorse al di fuori di se stesso. Una credenziale è un record contenente informazioni di autenticazione. Per autenticare e autorizzare l'accesso all'interfaccia di Cloud Storage S3, devi creare e utilizzare una chiave di accesso e una chiave segreta. Successivamente, archivierai queste chiavi nelle tue credenziali SQL Server.

1. Crea una chiave di accesso e una chiave segreta per il tuo account utente nel bucket Cloud Storage:

   1. Vai a Cloud Storage.
   2. Vai a Impostazioni.
   3. Vai a INTEROPERABILITÀ.
   4. Vai a Chiavi di accesso per il tuo account utente.
   5. Scorri verso il basso e fai clic su Crea una chiave per creare una nuova chiave di accesso e una chiave segreta.

2. Crea una credenziale nella tua istanza SQL Server 2022:

   Esegui il seguente esempio di codice per creare una credenziale. Scegli un nome per la credenziale. Sostituisci i campi ACCESS_KEY e SECRET con i valori generati nel passaggio precedente.

   osql -E -Q "
       CREATE CREDENTIAL [CREDENTIAL_NAME]
       WITH
           IDENTITY = 'S3 Access Key',
           SECRET = 'ACCESS_KEY:SECRET'
       "
   

   Nota: la definizione dell'identità IDENTITY = 'S3 Access Key' è importante perché indica a SQL Server di utilizzare il connettore S3.Non modificare questo campo.

3. Aggiungi una riga alla tabella di test:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('Initial')"
   

4. Esegui l'operazione BACKUP sul bucket Cloud Storage:

   Esegui il comando del database di backup impostando l'URI del bucket Cloud Storage come valore parametro URL e il nome della credenziale che hai definito in precedenza come valore dell'opzione WITH CREDENTIAL. Questo comando consente a SQL Server di creare il file di backup e di caricarlo contemporaneamente nel bucket Cloud Storage, senza bisogno di ulteriore spazio su disco locale.

   osql -E -Q "
       BACKUP DATABASE testdb
       TO URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/testdb.bak'
       WITH
           CREDENTIAL = 'CREDENTIAL_NAME',
           FORMAT,
           STATS = 10,
           MAXTRANSFERSIZE = 10485760,
           BLOCKSIZE = 65536,
           COMPRESSION;
       "
   

   Nota: assicurati di utilizzare il prefisso 's3://storage.googleapis.com' perché indica a SQL Server di utilizzare il connettore compatibile con S3. Ciò implica anche l'utilizzo di HTTPS.

Il seguente elenco illustra i parametri dell'istruzione WITH:

• FORMAT: sovrascrive eventuali backup esistenti e crea un nuovo set di contenuti multimediali.
• STATISTICHE: indica a SQL Server di fornire informazioni sull'avanzamento del backup.
• COMPRESSIONE: indica a SQL Server di comprimere il file di backup, rendendolo più piccolo e veloce da caricare in Cloud Storage.
• Le opzioni MAXTRANSFERSIZE = 10485760 e BLOCKSIZE = 65536 consentono di evitare errori del dispositivo I/O con file di backup di dimensioni maggiori.

1. Aggiungi una riga alla tabella di test:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('After Full Backup')"
   

2. Esegui un backup differenziale:

   osql -E -Q "
       BACKUP DATABASE testdb
       TO URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/testdb-diff.bak'
       WITH
           DIFFERENTIAL,
           CREDENTIAL = 'CREDENTIAL_NAME',
           STATS = 10,
           MAXTRANSFERSIZE = 10485760,
           BLOCKSIZE = 65536,
           COMPRESSION;
       "
   

3. Aggiungi una riga alla tabella di test:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('After Diff Backup')"
   

4. Esegui il backup dei log delle transazioni:

   osql -E -Q "
       BACKUP LOG testdb
       TO URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/testdb-log.bak'
       WITH
           CREDENTIAL = 'CREDENTIAL_NAME',
           STATS = 10,
           MAXTRANSFERSIZE = 10485760,
           BLOCKSIZE = 65536,
           COMPRESSION;
       "
   

5. Aggiungi una riga alla tabella di test:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('Bad Row')"
   

6. Verifica le righe della tabella:

   osql -E -Q "select * from testdb.dbo.testtable"
   

   L'output sarà simile al seguente:

   Initial
   
   After Full Backup
   
   After Diff Backup
   
   Bad Row
   

Gestione dei backup

In questa sezione archivierai i backup in remoto in Cloud Storage, configurerai il bucket Cloud Storage per eliminare automaticamente i vecchi backup e pianificarai i backup. Se esegui più server di database, valuta la possibilità di creare più directory nel bucket Cloud Storage per rappresentare server diversi.

Carica i tuoi backup su Cloud Storage

SQL Server 2019 e versioni precedenti

Ora che disponi di alcuni file di backup, puoi caricare i backup in Cloud Storage.

1. In Cloud Shell, crea un bucket Cloud Storage. I nomi dei bucket devono essere univoci a livello globale in Google Cloud. Per assicurarti che il nome del bucket sia univoco, valuta la possibilità di assegnare al bucket il nome del progetto, come mostrato qui:

   gsutil mb "gs://${DEVSHELL_PROJECT_ID}-sql-backups"
   

2. Nella finestra shell della sessione RDP, copia i file nel bucket Cloud Storage. Nel comando seguente, sostituisci BUCKET_NAME con il nome del bucket appena creato.

   gsutil cp -n c:\backup\testdb*.bak gs://BUCKET_NAME
   

   Nota: il flag -n impedisce allo strumento gsutil di sovrascrivere file identici.

   Puoi utilizzare il comando gsutil cp per creare intere strutture di directory e caricare più file contemporaneamente.

SQL Server 2022

I file di backup si trovano già nel tuo bucket poiché SQL Server 2022 supporta il backup direttamente in Cloud Storage.

Configura l'eliminazione automatica dei file in Cloud Storage

I backup meno recenti alla fine perdono la loro utilità, quindi devi rimuoverli. Per automatizzare questo processo, Cloud Storage dispone di un meccanismo di gestione del ciclo di vita che puoi utilizzare per gestire il ciclo di vita dei file di backup.

Avviso: il meccanismo del ciclo di vita elimina i file definitivamente, senza la possibilità di recuperarli. Quando testi la configurazione del ciclo di vita, effettua test su file di dati non critici.

Per configurare la gestione del ciclo di vita per gli oggetti nel bucket:

1. In Cloud Shell, crea un file JSON di configurazione del ciclo di vita. Questo file indica a Cloud Storage di eliminare i file dopo 30 giorni:

   bash -c 'cat <<EOF >  lifecycle.json
   {
       "lifecycle": {
           "rule": [{
               "action": { "type": "Delete" },
               "condition": { "age": 30 }
           }]
       }
   }
   EOF'
   

2. Imposta la configurazione del ciclo di vita per il bucket Cloud Storage. Sostituisci BUCKET_NAME con il nome del tuo bucket:

   gsutil lifecycle set lifecycle.json gs://BUCKET_NAME
   

Pianifica i backup

In generale, è consigliabile eseguire periodicamente un backup completo ed eseguire backup differenziali fino al backup completo successivo. Su Windows, un modo per pianificare i backup è usare attività pianificate.

Se crei uno script di backup che esegue una serie di backup, assicurati di includere alcuni passaggi di convalida logica in ogni punto per verificare il completamento corretto. Se la convalida non va a buon fine, assicurati che lo script generi un avviso di Windows. Inoltre, per evitare di riempire il disco locale, assicurati che lo script rimuova il file di backup locale dopo aver caricato correttamente in Cloud Storage.

Ripristino dal backup in corso

In questa sezione ripristinerai il database SQL Server dai file di backup archiviati in Cloud Storage.

SQL Server 2019 e versioni precedenti

1. In Cloud Shell, nella sessione RDP, scarica i file di backup da Cloud Storage. Sostituisci BUCKET_NAME con il nome del tuo bucket di archiviazione di backup di SQL Server:

   gsutil cp gs://BUCKET_NAME/testdb*.bak c:\restore
   

2. Apri la console di gestione SQL Server.

3. Fai clic sul pulsante Start e poi su Microsoft SQL Server Tools 18 > Microsoft SQL Server Management Studio 18.

4. Lascia invariati i campi Connessione, quindi fai clic su Connetti.

Nota: se viene visualizzato un messaggio di accesso non riuscito, esegui Management Studio come amministratore.

1. Nel riquadro a sinistra, espandi Database.
2. Fai clic con il tasto destro del mouse su testdb e, nel popup del menu, fai clic su Attività > Ripristina > Database.
3. Importa i file di backup nella console:
   1. In Origine, seleziona Dispositivo.
   2. Fai clic sul pulsante .
   3. Nella finestra di dialogo che si apre, fai clic su Aggiungi, seleziona tutti i file in C:\restore e fai clic su OK.
   4. Fai clic su Ok.

4. Per visualizzare le opzioni di ripristino point-in-time:

   1. Fai clic su Cronologia.
   2. In Ripristina in, fai clic su Data e ora specifiche.
   3. Per Intervallo cronologia, seleziona ora.
   4. Scegli un orario nell'intervallo di Backup dei log delle transazioni.

   5. Fai clic su Annulla per uscire dalla schermata della sequenza temporale.

      Nota: per ripristinare il database in un momento specifico, premi OK anziché Annulla.

5. In questo tutorial, ripristinerai il database allo stato precedente al backup dei log delle transazioni. A tale scopo, cancella la riga del log delle transazioni nell'elenco dei backup:

   

6. Avvia la procedura di ripristino:

   1. Fai clic su Ok.
   2. Attendi che il database completi il processo di ripristino e, quando vedi il messaggio Database 'testdb' restored successfully, fai clic su OK.

7. Elenca le righe nella tabella di test:

   osql -E -Q "select * from testdb.dbo.testtable;"
   

   L'output mostra due righe:

   "Initial
   "After Full Backup"
   

   Vedi tutte le righe che hai inserito nella tabella prima di eseguire il backup del log delle transazioni.

SQL Server 2022

Puoi eseguire l'operazione RESTORE. Imposta il percorso file di Cloud Storage come valore parametro URL. Ad esempio, questo script T-SQL ripristina il backup completo direttamente da Cloud Storage:

```
osql -E -Q "
RESTORE DATABASE testdb
FROM
    URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/testdb.bak'
WITH
    CREDENTIAL = 'CREDENTIAL_NAME';
    "
```

1. Apri la console di gestione SQL Server.
2. Fai clic sul pulsante Start e poi su Microsoft SQL Server Tools 19 > Microsoft SQL Server Management Studio 19.

Nota: devi installare Microsoft SQL Server Management Studio versione 19.x.

1. Lascia invariati i campi Connessione, quindi fai clic su Connetti.

Nota: se viene visualizzato un messaggio di accesso non riuscito, esegui Management Studio come amministratore.

1. Nel riquadro a sinistra, espandi Database.
2. Fai clic con il tasto destro del mouse su testdb e, nel popup del menu, fai clic su Attività > Ripristina > Database.

3. Importa i file di backup nella console:

   1. In Origine, seleziona Dispositivo.
   2. Fai clic sul pulsante .
   3. Nella finestra di dialogo che si apre, fai clic su Tipo di supporto multimediale di backup e seleziona URL S3.

   4. Nella finestra di dialogo che si apre, fai clic su Aggiungi. Aggiungi le tre posizioni dei file di backup nel bucket Cloud Storage e la chiave segreta e la chiave di accesso che hai fornito al momento della creazione della credenziale.

      Nota: utilizza lo stesso formato di quello per eseguire il backup per l'URL S3.

   5. Fai clic su Ok.

4. Per visualizzare le opzioni di ripristino point-in-time:

   1. Fai clic su Cronologia.
   2. In Ripristina in, fai clic su Data e ora specifiche.
   3. Per Intervallo cronologia, seleziona ora.
   4. Scegli un orario nell'intervallo di Backup dei log delle transazioni.

   5. Fai clic su Annulla per uscire dalla schermata della sequenza temporale.

      Nota: per ripristinare il database in un momento specifico, premi OK anziché Annulla.

5. In questo tutorial, ripristinerai il database allo stato precedente al backup dei log delle transazioni. A tale scopo, cancella la riga del log delle transazioni nell'elenco dei backup:

   

6. Avvia la procedura di ripristino:

   1. Fai clic su Ok.
   2. Attendi che il database completi il processo di ripristino e, quando vedi il messaggio Database 'testdb' restored successfully, fai clic su OK.

7. Elenca le righe nella tabella di test:

   osql -E -Q "select * from testdb.dbo.testtable;"
   

   L'output mostra due righe:

   "Initial
   "After Full Backup"
   

   Vedi tutte le righe che hai inserito nella tabella prima di eseguire il backup del log delle transazioni.

Esegui la pulizia

Elimina il progetto

Attenzione: l'eliminazione di un progetto ha i seguenti effetti:
• L'intero contenuto del progetto viene eliminato. Se hai utilizzato un progetto esistente per le attività in questo documento, quando lo elimini elimini anche qualsiasi altro lavoro svolto nel progetto.
• Gli ID progetto personalizzati non sono più disponibili. Quando hai creato questo progetto, potresti aver creato un ID progetto personalizzato che vuoi utilizzare in futuro. Per conservare gli URL che utilizzano l'ID progetto, ad esempio un URL appspot.com, elimina le risorse selezionate all'interno del progetto anziché eliminare l'intero progetto.
1. Nella console Google Cloud, vai alla pagina Gestisci risorse.

   Vai a Gestisci risorse

2. Nell'elenco dei progetti, seleziona il progetto che vuoi eliminare, quindi fai clic su Elimina.
3. Nella finestra di dialogo, digita l'ID del progetto e fai clic su Chiudi per eliminare il progetto.

Passaggi successivi

• Scopri di più sulle best practice per SQL Server su Google Cloud.
• Scopri di più sulla configurazione dei gruppi di disponibilità di SQL Server.
• Leggi la guida alla pianificazione del ripristino di emergenza per Google Cloud.
• Esplora le architetture di riferimento, i diagrammi e le best practice su Google Cloud. Dai un'occhiata al nostro Cloud Architecture Center.