Migrazione dei dati da SQL Server 2017 a Cloud SQL per SQL Server utilizzando la replica degli snapshot

---

Questo tutorial mostra come eseguire la migrazione dei dati da Microsoft SQL Server 2017 Enterprise in esecuzione su Compute Engine a Cloud SQL per SQL Server 2017 Enterprise. Questo tutorial è utile se sei un amministratore di sistema, uno sviluppatore, un ingegnere, un amministratore del database o un ingegnere DevOps. Il tutorial mostra come configurare il distributore SQL Server, utilizzare l'agente SQL Server per la replica degli snapshot in Cloud SQL per SQL Server 2017 e convalidare la corretta importazione dei dati.

In questo tutorial utilizzerai la replica degli snapshot per mantenere sincronizzate l'origine e la destinazione. La replica degli snapshot invia ai sottoscrittori una copia completa di ogni articolo, ossia l'oggetto del database pubblicato. Cloud SQL supporta anche la replica transazionale, che invia solo dati incrementali. Una limitazione della replica transazionale è che le tabelle devono avere chiavi primarie. Per ulteriori informazioni sui tipi di replica di SQL Server, consulta la documentazione di SQL Server.

Per semplicità, il tutorial utilizza l'SQL Server di origine per ospitare un distributore. In uno scenario di produzione, se i dati vengono replicati dall'esterno di Google Cloud, potresti preferire l'hosting della distribuzione sul lato di Google Cloud su un'istanza di Compute Engine.

Nota: Cloud SQL per SQL Server non supporta la spedizione dei log, il mirroring del database o queste funzionalità. Microsoft SQL Server non supporta la replica dalle versioni Developer, Web o Express.

Il tutorial presuppone che tu conosca le seguenti nozioni:

• SQL Server
• Microsoft PowerShell
• Compute Engine
• Cloud SQL per SQL Server

Obiettivi

• Crea un'istanza di macchina virtuale (VM) SQL Server su Compute Engine per ospitare il database di esempio.
• Completa un database di esempio.
• Creare un'istanza di Cloud SQL per SQL Server.
• Creare un distributore.
• Configura la pubblicazione e l'abbonamento.
• Avvia la replica da SQL Server a Cloud SQL.
• Convalida i dati importati.

Costi

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

• Compute Engine
• Cloud SQL
• 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. Abilita le API Cloud SQL Admin and Compute Engine API.

   Abilita le 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. Abilita le API Cloud SQL Admin and Compute Engine API.

   Abilita le API

8. Nella console Google Cloud, attiva Cloud Shell.

   Attiva Cloud Shell

   Nella parte inferiore della console Google Cloud viene avviata una sessione di Cloud Shell che mostra un prompt della riga di comando. Cloud Shell è un ambiente shell con Google Cloud CLI già installato e con valori già impostati per il progetto attuale. L'inizializzazione della sessione può richiedere alcuni secondi.

9. Installa qualsiasi client RDP (Remote Desktop Protocol) di tua scelta. Per ulteriori informazioni, vedi Client 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.

Creazione di una VM SQL Server

Il primo passaggio consiste nel creare un'istanza SQL Server 2017 su una VM Windows 2016 in Compute Engine e connetterti all'istanza utilizzando RDP.

1. In Cloud Shell, crea un'istanza SQL Server 2017 Standard su Windows Server 2016. Il database di origine deve essere un database SQL Server Standard o Enterprise perché i livelli inferiori non hanno le funzionalità del publisher del database.

   gcloud compute instances create sqlserver --machine-type=n1-standard-4 \
       --boot-disk-size=100GB \
       --image-project=windows-sql-cloud \
       --image-family=sql-std-2017-win-2016 \
       --zone=us-central1-f  \
       --scopes=https://www.googleapis.com/auth/cloud-platform
   

   Per questo tutorial, creerai l'istanza nella zona us-central1-f con una dimensione del disco di avvio di 100 GB. Per ulteriori informazioni, consulta Località cloud.

2. Genera una password di Windows:

   gcloud compute reset-windows-password sqlserver --zone=us-central1-f
   

3. Nella console Google Cloud, vai alla pagina Istanze VM.

   Vai a Istanze VM

4. 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 connetterti all'istanza mediante un client RDP. Per maggiori informazioni, consulta Client Microsoft Remote Desktop.

5. Nei campi username e password, inserisci il nome utente e la password che hai creato per l'istanza VM SQL Server. Lascia vuoto il campo Domain (Dominio), quindi fai clic su OK per connetterti alla VM SQL Server.

   

   Quando richiesto, accetta il certificato.

6. Se accetti i termini, fai clic su Continua.

7. Nell'istanza, riduci a icona tutte le finestre, fai clic su Start nella barra delle applicazioni di Windows, digita PowerShell, quindi fai clic con il tasto destro del mouse sull'app Windows PowerShell e seleziona Esegui come amministratore.

8. Al prompt di PowerShell, crea una struttura di directory per l'archiviazione del database:

   mkdir c:\sql-server-data\adventureworks
   

Creazione e compilazione di un database di esempio

Ora puoi scaricare il file di backup del database AdventureWorks Microsoft e ripristinarlo nella tua istanza SQL Server. Questo database simula il database di produzione di cui vuoi eseguire la migrazione.

1. Dalla riga di comando di PowerShell, scarica il file di backup AdventureWorksLT2017.bak sull'unità C:

   bitsadmin /transfer sampledb /dynamic /download /priority FOREGROUND "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksLT2017.bak " C:\sql-server-data\AdventureWorksLT2017.bak
   

2. Ripristina il database:

   osql -E -Q "USE [master]
   RESTORE DATABASE [AdventureWorksLT2017]
   FROM  DISK = N'C:\sql-server-data\AdventureWorksLT2017.bak' WITH  FILE = 1,
   MOVE N'AdventureWorksLT2012_Data' TO N'C:\sql-server-data\adventureworks\AdventureWorksLT2012.mdf',
   MOVE N'AdventureWorksLT2012_Log' TO N'C:\sql-server-data\adventureworks\AdventureWorksLT2012_log.ldf',
   NOUNLOAD,  STATS = 5"
   

3. Convalida il database appena ripristinato eseguendo una query sul numero di righe nella tabella Customer. L'output è di 847 righe.

   osql -E -Q "select count(*) from AdventureWorksLT2017.SalesLT.Customer"
   

Preparazione dell'istanza Cloud SQL

1. In Cloud Shell, crea l'istanza Cloud SQL per SQL Server 2017 Enterprise:

   gcloud sql instances create target-sqlserver  \
       --database-version=SQLSERVER_2017_STANDARD \
       --cpu=4 \
       --memory=15 \
       --storage-size=100 \
       --root-password=sqlserver12@ \
       --zone=us-central1-f
   

   L'utente root è sqlserver con password di sqlserver12@. Prendi nota anche dell'indirizzo IP di SQL Server.

2. Archivia l'indirizzo IP della VM SQL Server di origine in una variabile di ambiente:

   sql_source_ip=$(gcloud compute instances describe sqlserver \
       --zone=us-central1-f | grep natIP | awk '{print $2}')
   echo $sql_source_ip
   

3. Aggiungi l'indirizzo IP della VM SQL Server con l'istanza Cloud SQL alla lista consentita (lista consentita):

   gcloud sql instances patch target-sqlserver --authorized-networks=$sql_source_ip
   

4. Crea un database di destinazione:

   gcloud sql databases create AdventureWorksTarget --instance=target-sqlserver
   

5. Nella riga di comando di PowerShell che hai avviato come amministratore, controlla la connettività a Cloud SQL per SQL Server:

   $cloud_sql_server_ip=gcloud sql instances describe target-sqlserver --format='value(ipAddresses.ipAddress)'
   osql -S $cloud_sql_server_ip -U sqlserver -P sqlserver12@ -Q "select 'test'"
   

   Il comando stampa 'test'.

Avvio della replica dalla VM a Cloud SQL

Con la replica degli snapshot di SQL Server puoi creare un job per replicare gli snapshot del database in Cloud SQL.

Configurare il distributore

Ora configuri la VM SQL Server come distributore SQL Server. In pratica, un distributore può essere eseguito su una macchina separata, ma in questo tutorial lo eseguirai sulla stessa VM.

1. Dalla riga di comando di PowerShell che hai avviato come amministratore, aggiorna il nome dell'istanza locale di SQL Server in modo che corrisponda al nome host della VM:

   $servername=hostname
   osql -E -Q "sp_dropserver 'INST-INSTALL-SQ';"
   osql -E -Q "sp_addserver '$servername', local;"
   Restart-Service -F MSSQLServer
   mkdir c:\sql-server-data\repldata
   

   Se ricevi l'errore Cannot open MSSQLServer service on computer '.'., è probabile che tu non abbia eseguito la riga di comando di PowerShell come amministratore.

2. Verifica che il nome dell'istanza sia ora "sqlserver":

   osql -E -Q "select @@servername;"
   

3. Specifica il database di distribuzione:

   $servername=hostname
   osql -E -Q "use master; exec sp_adddistributor @distributor = N'$servername', @password = N'';"
   osql -E -Q "exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1"
   

4. Configura il database di distribuzione:

   osql -E -Q "
   use [distribution]
   if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
       create table UIProperties(id int)
   if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
       exec sp_updateextendedproperty N'SnapshotFolder', N'c:\sql-server-data\repldata', 'user', dbo, 'table', 'UIProperties'
   else
       exec sp_addextendedproperty N'SnapshotFolder', N'c:\sql-server-data\repldata', 'user', dbo, 'table', 'UIProperties'
   "
   

5. Registra il nome della VM SQL Server con il distributore come publisher:

   osql -E -Q "exec sp_adddistpublisher @publisher = N'$servername',
   @distribution_db = N'distribution', @security_mode = 1, @working_directory
   = N'c:\sql-server-data\repldata', @trusted = N'false', @thirdparty_flag =
   0, @publisher_type = N'MSSQLSERVER'"
   

Configura la pubblicazione

Ora che la distribuzione è configurata, puoi configurare le tabelle da pubblicare.

1. Dalla riga di comando di PowerShell, abilita e avvia l'agente SQL Server:

   Set-Service -Name SQLServerAgent -StartupType Automatic
   Start-Service -Name SQLServerAgent
   

2. Crea una pubblicazione per il database AdventureWorksLT2017:

   $servername=hostname
   osql -E -Q "
   use [AdventureWorksLT2017]
   exec sp_replicationdboption @dbname = N'AdventureWorksLT2017', @optname = N'publish', @value = N'true'"
   
   osql -E -Q "use [AdventureWorksLT2017]
   exec sp_addpublication @publication = N'advn-pub3', @description = N'Snapshot publication of database ''AdventureWorksLT2017'' from Publisher ''$servername''.', @sync_method = N'native', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'snapshot', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1"
   

3. Configura la pubblicazione in modo che invii uno snapshot dell'origine a Cloud SQL una volta ogni ora:

   osql -E -Q "
   use [AdventureWorksLT2017]
   exec sp_addpublication_snapshot @publication = N'advn-pub3', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1"
   

4. Aggiungi articoli (tabelle) alla pubblicazione. Sebbene il database AdventureWorksLT2017 contenga molte tabelle, per semplicità in questo tutorial replichi tre tabelle: Address, Customer e CustomerAddress.

   osql -E -Q "
   use [AdventureWorksLT2017]
   exec sp_addarticle @publication = N'advn-pub3', @article = N'Address', @source_owner = N'SalesLT', @source_object = N'Address', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'Address', @destination_owner = N'SalesLT', @vertical_partition = N'false'
   "
   
   osql -E -Q "
   use [AdventureWorksLT2017]
   exec sp_addarticle @publication = N'advn-pub3', @article = N'Customer', @source_owner = N'SalesLT', @source_object = N'Customer', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'Customer', @destination_owner = N'SalesLT', @vertical_partition = N'false'
   "
   
   osql -E -Q "
   use [AdventureWorksLT2017]
   exec sp_addarticle @publication = N'advn-pub3', @article = N'CustomerAddress', @source_owner = N'SalesLT', @source_object = N'CustomerAddress', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'CustomerAddress', @destination_owner = N'SalesLT', @vertical_partition = N'false'
   "
   

5. Crea manualmente un'istantanea degli articoli. In caso contrario, potrebbe essere necessaria fino a un'ora prima che lo scheduler crei lo snapshot iniziale.

   osql -E -Q "use [AdventureWorksLT2017]
   exec sp_startpublication_snapshot @publication = N'advn-pub3'"
   

Configurare un abbonamento

Ora creerai una sottoscrizione che invia i dati pubblicati a Cloud SQL.

1. Dalla riga di comando di PowerShell, crea una sottoscrizione per inviare lo snapshot della pubblicazione a Cloud SQL:

   $cloud_sql_server_ip=gcloud sql instances describe target-sqlserver --format='value(ipAddresses.ipAddress)'
   $cloud_sql_user="sqlserver"
   $cloud_sql_password="sqlserver12@"
   $target_db_name="AdventureWorksTarget"
   osql -E -Q " use [AdventureWorksLT2017] exec sp_addsubscription @publication = N'advn-pub3', @subscriber = N'$cloud_sql_server_ip', @destination_db = N'$target_db_name', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
   
   exec sp_addpushsubscription_agent @publication = N'advn-pub3', @subscriber = N'$cloud_sql_server_ip', @subscriber_db = N'$target_db_name', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'$cloud_sql_user', @subscriber_password = N'$cloud_sql_password', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20200408, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor' "
   

1. Verifica che i dati siano disponibili nell'istanza Cloud SQL:

   osql -S $cloud_sql_server_ip -U $cloud_sql_user -P $cloud_sql_password -Q "select count(*) from [AdventureWorksTarget].[SalesLT].[CustomerAddress] UNION
   select count(*) from [AdventureWorksTarget].[SalesLT].[Customer]
   UNION
   Select count(*) from [AdventureWorksTarget].[SalesLT].[Address]"
   

   Il comando stampa 417, 450 e 847

Esegui la pulizia

Al termine del tutorial, puoi eseguire la pulizia delle risorse che hai creato in modo che smettano di utilizzare la quota e non incorrano in addebiti. Le sezioni seguenti descrivono come eliminare o disattivare queste risorse.

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 come eseguire la migrazione dei dati da SQL Server 2017 a Cloud SQL per SQL Server utilizzando file di backup.
• Scopri come eseguire la migrazione dei dati da SQL Server 2008 a Cloud SQL per SQL Server utilizzando file di backup.
• Esplora le architetture di riferimento, i diagrammi e le best practice su Google Cloud. Dai un'occhiata al nostro Cloud Architecture Center.