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 Compute Engine SQL Server in esecuzione in un'istanza Compute Engine. Il tutorial mostra come gestire questi backup e archiviarli in Cloud Storage e come ripristinare un database in un momento specifico.

Questo tutorial è utile per amministratori di sistema, sviluppatori, ingegneri, database o sviluppatore 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, non tratta l'argomento Ripristino di emergenza per SQL Server.

Il tutorial presuppone che tu conosca i seguenti concetti:

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

Obiettivi

• Avvia un'istanza SQL Server e crea un database.
• Esegui backup completi, differenziali e dei log delle transazioni.
• Carica i backup su 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 with compute engine)

Per generare una stima dei costi basata sull'utilizzo previsto, utilizza il Calcolatore prezzi. I nuovi utenti di Google Cloud potrebbero essere idonei per una prova gratuita.

Prima di iniziare

1. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

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

3. Attiva l'API Compute Engine.

   Abilita l'API

4. Installa un client Remote Desktop Protocol (RDP) di tua scelta. Per maggiori informazioni consulta l'articolo sui 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 per configurare una chiave di crittografia.

avvia l'istanza SQL Server

La prima attività è avviare un'istanza SQL Server e creare il 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 la tua istanza. Utilizza questo file per collegarti utilizzando un client RDP. Per ulteriori informazioni, consulta l'articolo sui client di Microsoft Remote Desktop.

Installa SQL Server Management Studio

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

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

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

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

3. Accetta la richiesta per consentire l'applicazione delle modifiche.

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

5. Al termine dell'installazione, fai clic su Riavvia per riavviare il dalla macchina remota. La sessione RDP viene chiusa.

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

7. Inserisci il tuo nome utente e la password che hai salvato in precedenza (esci dalla sezione Dominio) campo vuoto), 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 uguale a Cloud Shell) sulla Desktop 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, nell'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. Crea 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 il 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 precedenti

In questa sezione creerai backup completi, differenziali e dei log delle transazioni e modificare il database tra uno e l'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 questa procedura documento. Per maggiori informazioni, consulta BACKUP CERTIFICATE (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 un backup del 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, differenziali e dei log delle transazioni e modificare il database tra uno e l'altro. Utilizzi il backup integrato e ripristinare i comandi con Cloud Storage.

SQL Server 2022 (16.x) supporta l'integrazione estesa dell'archiviazione di oggetti introducendo un nuovo connettore che utilizza un'API REST per connettersi a qualsiasi provider di servizi compatibili con S3 archiviazione di oggetti. Puoi usare 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 esterne. R credenziale è un record contenente informazioni di autenticazione. Per l'autenticazione e autorizzare il tuo accesso all'interfaccia di Cloud Storage S3, devi crea e utilizza una chiave di accesso e una chiave segreta. Successivamente, archivierai queste chiavi la tua credenziale SQL Server.

1. Crea una chiave di accesso e una chiave segreta per l'account utente al tuo Bucket Cloud Storage:

   1. Vai a Cloud Storage.
   2. Vai a Impostazioni.
   3. Vai a INTEROPERABILITY.
   4. Vai a Chiavi di accesso per l'account utente.
   5. Scorri verso il basso e fai clic su Create a key (Crea una chiave) per creare una nuova chiave di accesso, quindi una chiave segreta.

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

   Esegui questo esempio di codice per creare una credenziale. Scegli un nome per la tua qualifica. 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' è perché indica a SQL Server di utilizzare il connettore S3. 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 di database di backup utilizzando il tuo bucket Cloud Storage URI impostato come valore parametro URL e nome della credenziale definita in precedenza come valore dell'opzione WITH CREDENTIAL. Questo consentirà a SQL Server di creare il file di backup e caricarlo al bucket Cloud Storage, senza la necessità di spazio aggiuntivo 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 spiega i parametri dell'istruzione WITH:

• FORMATO: sovrascrive i backup esistenti e crea un nuovo set multimediale.
• STATS: indica a SQL Server di fornire informazioni sull'avanzamento delle il backup.
• COMPRESSION: comunica a SQL Server di comprimere il file di backup, in modo che più piccoli e rapidi da caricare su Cloud Storage.
• Le opzioni MAXTRANSFERSIZE = 10485760, BLOCKSIZE = 65536 consentono di evitare Errori del dispositivo I/O con file di backup più grandi.

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 un backup del 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 è simile al seguente:

   Initial
   
   After Full Backup
   
   After Diff Backup
   
   Bad Row
   

Gestione dei backup

In questa sezione archivierai i backup da remoto in Cloud Storage, configurare il bucket Cloud Storage per eliminare automaticamente i vecchi backup, e pianificare i backup. Se esegui più server di database, valuta creando più directory nel tuo bucket Cloud Storage per rappresentare server diversi.

Carica i tuoi backup su Cloud Storage

SQL Server 2019 e precedenti

Ora che disponi di alcuni file di backup, puoi caricare i tuoi backup su di archiviazione ideale in Cloud Storage.

1. In Cloud Shell, crea un bucket Cloud Storage. A cestello devono essere univoci a livello globale in Google Cloud. Per assicurarti che del bucket è univoco, considera l'aggiunta di un intervallo dei nomi con il nome del tuo progetto, mostrato qui:

   gcloud storage buckets create "gs://${DEVSHELL_PROJECT_ID}-sql-backups"
   

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

   gcloud storage cp c:\backup\testdb*.bak gs://BUCKET_NAME --no-clobber
   

   Nota: il flag --no-clobber impedisce a gcloud CLI di sovrascrivendo file identici.

   Puoi utilizzare lo gcloud storage cp per creare intere strutture di directory e caricare più file alla volta.

SQL Server 2022

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

Configura l'eliminazione automatica dei file in Cloud Storage

Alla fine, i backup meno recenti durano più del previsto, quindi dovrai rimuoverli. Per automatizzare questo processo, Cloud Storage gestisce il ciclo di vita per la gestione del ciclo di vita dei file di backup.

Avviso: il meccanismo del ciclo di vita elimina i file in modo definitivo, senza alcuna possibilità di recuperandole. Quando testi la configurazione del ciclo di vita, testala file di dati non critici.

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

1. In Cloud Shell, crea un file di configurazione JSON 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:

   gcloud storage buckets update gs://BUCKET_NAME --lifecycle-file=lifecycle.json
   

Pianifica i backup

In generale, è buona norma eseguire periodicamente un backup completo ed eseguire differenziali fino al successivo backup completo. Su Windows, un modo per programmare i backup avvengono tramite attività pianificate.

Se crei uno script di backup che esegue una serie di backup, assicurati di includi alcuni passaggi di convalida logica in ciascun punto per verificare completamento. Se la convalida ha esito negativo, assicurati che lo script generi un avviso di Windows. Nella inoltre, per evitare di riempire il disco locale, assicurati che lo script rimuova file di backup locale dopo il corretto caricamento su Cloud Storage.

Ripristino dal backup in corso

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

SQL Server 2019 e 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 SQL Server:

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

2. Apri la console di gestione di SQL Server.

3. Fai clic sul pulsante Start, quindi fai clic Strumenti di Microsoft SQL Server 18 > Microsoft SQL Server Management Studio 18.

4. Lascia invariati i campi Connection (Connessione), quindi fai clic su Connect (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 menu popup, 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 e seleziona tutti i file sotto C:\restore, quindi 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 sequenza temporale, seleziona ora.
   4. Scegli un'ora nell'intervallo Backup del log delle transazioni.

   5. Fai clic su Annulla per uscire dalla schermata della cronologia.

      Nota: per ripristinare il database a un momento specifico, premi OK invece di Annulla.

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

   

6. Avvia il processo 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 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"
   

   Vedrai tutte le righe che hai inserito nella tabella prima eseguendo 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 di SQL Server.
2. Fai clic sul pulsante Start, quindi fai clic Strumenti di Microsoft SQL Server 19 > Microsoft SQL Server Management Studio 19.

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

1. Lascia invariati i campi Connection (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 menu popup, 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 media di backup e seleziona URL S3

   4. Nella finestra di dialogo che si apre, fai clic su Aggiungi. Aggiungi le tre posizioni dei tuoi 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 per eseguire il backup dell'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 sequenza temporale, seleziona ora.
   4. Scegli un'ora nell'intervallo Backup del log delle transazioni.

   5. Fai clic su Annulla per uscire dalla schermata della cronologia.

      Nota: per ripristinare il database a un momento specifico, premi OK invece di Annulla.

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

   

6. Avvia il processo 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 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"
   

   Vengono visualizzate tutte le righe che hai inserito nella tabella prima eseguendo 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

• Informazioni Best practice per SQL Server su Google Cloud.
• Informazioni configurazione dei gruppi di disponibilità SQL Server.
• Leggi 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 Centro architetture cloud.