Como migrar dados do SQL Server 2017 para o Cloud SQL para SQL Server usando replicação de snapshot

Como criar uma VM do SQL Server

A primeira etapa é criar uma instância do SQL Server 2017 em uma VM do Windows 2016 no Compute Engine e se conectar a ela usando RDP.

1. No Cloud Shell, crie uma instância do SQL Server 2017 Standard no Windows Server 2016. O banco de dados de origem precisa ser um banco de dados do SQL Server Standard ou Enterprise, já que os níveis inferiores não têm a capacidade de editor de banco de dados.

   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
   

   Para este tutorial, crie a instância na zona us-central1-f com um tamanho de disco de inicialização de 100 GB. Para mais informações, consulte Locais do Cloud.

2. Gere uma senha do Windows:

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

3. No console do Google Cloud, acesse a página Instâncias de VMs.

   Acessar instâncias de VM

4. Na seção "Compute Engine" do console do Google Cloud, clique no menu suspenso RDP e selecione a opção Fazer o download do arquivo RDP para fazer o download do arquivo RDP da instância.

   Use esse arquivo para se conectar à instância usando um cliente RDP. Para mais informações, consulte Clientes de Área de Trabalho Remota da Microsoft.

5. Nos campos username e password, digite o nome de usuário e a senha que você criou para a instância de VM do SQL Server. Deixe o campo Domínio em branco e clique em OK para se conectar à VM do SQL Server.

   

   Quando solicitado, aceite o certificado.

6. Se você aceita os termos, clique em Continuar.

7. Na instância, minimize todas as janelas, clique em Iniciar na barra de tarefas do Windows, digite PowerShell, clique com o botão direito do mouse no aplicativo Windows PowerShell e selecione Executar como administrador.

8. No prompt do PowerShell, crie uma estrutura de diretórios para o armazenamento do banco de dados:

   mkdir c:\sql-server-data\adventureworks
   

Como criar e preencher um banco de dados de amostra

Agora faça o download do arquivo de backup do banco de dados AdventureWorks da Microsoft e o restaure na sua instância do SQL Server. Esse banco de dados simula o banco de dados de produção que você quer migrar.

1. Na linha de comando do PowerShell, faça o download do arquivo de backup AdventureWorksLT2017.bak para acionar 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. Restaure o banco de dados:

   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. Valide o banco de dados recém-restaurado consultando o número de linhas na tabela Customer. A saída é 847 linhas.

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

Como preparar a instância do Cloud SQL

1. No Cloud Shell, crie a instância do Server 2017 Enterprise do Cloud SQL para SQL:

   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
   

   O usuário raiz é sqlserver com uma senha de sqlserver12@. Anote também o endereço IP do SQL Server.

2. Armazene o endereço IP da VM de origem do SQL Server em uma variável de ambiente:

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

3. Adicione o endereço IP da VM do SQL Server com a instância do Cloud SQL à lista de permissões (lista de permissões):

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

4. Crie um banco de dados de destino:

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

5. Na linha de comando do PowerShell que você iniciou como administrador, verifique a conectividade do Cloud SQL para 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'"
   

   O comando imprime 'test'.

Como iniciar a replicação da VM para o Cloud SQL

Usando a replicação de snapshot do SQL Server, você cria um job para replicar snapshots do banco de dados no Cloud SQL.

Configurar o Distributor

Agora você configura a VM do SQL Server para ser um Distributor do SQL Server. Na prática, um Distributor pode ser executado em uma máquina separada, mas neste tutorial, você o executa na mesma VM.

1. Na linha de comando do PowerShell que você iniciou como administrador, atualize o nome da instância local do SQL Server para corresponder ao nome do host da 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 você receber o erro Cannot open MSSQLServer service on computer '.'., provavelmente não executou a linha de comando do PowerShell como administrador.

2. Verifique se o nome da instância agora é "sqlserver":

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

3. Especifique o banco de dados de distribuição:

   $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. Configure o banco de dados de distribuição:

   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. Registre o nome da VM do SQL Server com o Distributor como editor:

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

Configurar a publicação

Agora que a distribuição está configurada, você configura quais tabelas são publicadas.

1. Na linha de comando do PowerShell, ative e inicie o SQL Server Agent:

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

2. Crie uma publicação para o banco de dados 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. Configure a publicação para enviar um snapshot da origem ao Cloud SQL uma vez a cada hora:

   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. Adicionar artigos (tabelas) à publicação. Embora o banco de dados AdventureWorksLT2017 contenha muitas tabelas, para simplificar neste tutorial, você replica três tabelas: 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. Crie manualmente um snapshot dos artigos. Se você não fizer isso, poderá levar até uma hora para que o programador crie o snapshot inicial.

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

Configurar uma assinatura

Agora você cria uma assinatura que envia os dados publicados para o Cloud SQL.

1. Na linha de comando do PowerShell, crie uma assinatura para enviar o snapshot da publicação para o 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. Valide se os dados estão disponíveis na instância do 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]"
   

   O comando imprime 417, 450, 847