Migrar dados do SQL Server 2017 para o Cloud SQL para SQL Server através da replicação de instantâneos

Este tutorial mostra como migrar dados do Microsoft SQL Server 2017 Enterprise em execução no Compute Engine para o Cloud SQL para SQL Server 2017 Enterprise. Este tutorial é útil se for um sysadmin, um programador, um engenheiro, um administrador de base de dados ou um engenheiro de DevOps. O tutorial mostra como configurar o SQL Server Distributor, usar o SQL Server Agent para a replicação de instantâneos no Cloud SQL para SQL Server 2017 e validar se os dados foram importados com êxito.

Neste tutorial, vai usar a replicação de instantâneos para manter a origem e o destino sincronizados. A replicação de instantâneos envia aos subscritores uma cópia completa de cada artigo, ou seja, um objeto de base de dados publicado. O Cloud SQL também suporta a replicação transacional, que envia apenas dados incrementais. Uma limitação da replicação transacional é que as tabelas têm de ter chaves primárias. Para mais informações sobre os tipos de replicação do SQL Server, consulte a documentação do SQL Server.

Para simplificar, o tutorial usa o SQL Server de origem para alojar um distribuidor. Num cenário de produção, se os dados estiverem a ser replicados a partir do exterior Google Cloud, pode preferir alojar a distribuição no lado Google Cloud numa instância do Compute Engine.

Nota: o Cloud SQL para SQL Server não suporta o envio de registos, a replicação de bases de dados nem estas funcionalidades. O Microsoft SQL Server não suporta a replicação das edições Developer, Web ou Express.

O tutorial pressupõe que conhece o seguinte:

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

Objetivos

• Crie uma instância de máquina virtual (VM) do SQL Server no Compute Engine para alojar a base de dados de amostra.
• Preencha uma base de dados de amostra.
• Crie uma instância do Cloud SQL para SQL Server.
• Crie um distribuidor.
• Configure a publicação e a subscrição.
• Inicie a replicação do SQL Server para o Cloud SQL.
• Valide os dados importados.

Custos

Neste documento, usa os seguintes componentes faturáveis do Google Cloud:

• Compute Engine
• Cloud SQL
• Cloud Storage
• SQL Server (premium with Compute Engine)

Para gerar uma estimativa de custos com base na sua utilização projetada, use a calculadora de preços.

Os novos Google Cloud utilizadores podem ser elegíveis para uma avaliação gratuita.

Antes de começar

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

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

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

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

Verify that billing is enabled for your Google Cloud project.

Enable the Cloud SQL Admin and Compute Engine API APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

Enable the APIs

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

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

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

Verify that billing is enabled for your Google Cloud project.

Enable the Cloud SQL Admin and Compute Engine API APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

Enable the APIs

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

2. Instale qualquer cliente do protocolo de ambiente de trabalho remoto (RDP) à sua escolha. Para mais informações, consulte o artigo Clientes do Ambiente de Trabalho Remoto da Microsoft. Se já tiver um cliente RDP instalado, pode ignorar esta tarefa.

Quando terminar as tarefas descritas neste documento, pode evitar a faturação contínua eliminando os recursos que criou. Para mais informações, consulte o artigo Limpe.

Criar uma VM do SQL Server

O primeiro passo é criar uma instância do SQL Server 2017 numa VM do Windows 2016 no Compute Engine e estabelecer ligação à mesma através do RDP.

1. No Cloud Shell, crie uma instância do SQL Server 2017 Standard no Windows Server 2016. A base de dados de origem tem de ser uma base de dados SQL Server Standard ou Enterprise, uma vez que os níveis inferiores não têm a capacidade de editor de bases 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, cria a instância na zona us-central1-f com um tamanho do disco de arranque de 100 GB. Para mais informações, consulte o artigo Localizações na nuvem.

2. Gere uma palavra-passe do Windows:

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

3. Na Google Cloud consola, aceda à página Instâncias de VM.

   Aceder a Instâncias de VM

4. Na secção Compute Engine da Google Cloud consola, clique no menu pendente RDP e selecione a opção Transferir o ficheiro RDP para transferir o ficheiro RDP da sua instância.

   Use este ficheiro para estabelecer ligação à instância através de um cliente RDP. Para mais informações, consulte o artigo Clientes do Ambiente de Trabalho Remoto da Microsoft.

5. Nos campos nome de utilizador e palavra-passe, introduza o nome de utilizador e a palavra-passe que criou para a instância de VM do SQL Server. Deixe o campo Domínio em branco e, de seguida, clique em OK para estabelecer ligação à VM do SQL Server.

   

   Quando lhe for pedido, aceite o certificado.

6. Se aceitar os termos, clique em Continuar.

7. Na instância, minimize todas as janelas, clique em Iniciar na barra de tarefas do Windows, escreva PowerShell e, de seguida, clique com o botão direito do rato na app Windows PowerShell e selecione Executar como administrador.

8. Na linha de comandos do PowerShell, crie uma estrutura de diretórios para o armazenamento da base de dados:

   mkdir c:\sql-server-data\adventureworks
   

Criar e preencher uma base de dados de amostra

Agora, transfere o ficheiro de cópia de segurança da base de dados AdventureWorks da Microsoft e restaura-o na sua instância do SQL Server. Esta base de dados simula a base de dados de produção que quer migrar.

1. A partir da linha de comandos do PowerShell, transfira o AdventureWorksLT2017.bak ficheiro de cópia de segurança para a unidade 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 a base 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 a base de dados recentemente restaurada consultando o número de linhas na tabela Customer. O resultado são 847 linhas.

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

Preparar a instância do Cloud SQL

1. No Cloud Shell, crie a instância do Cloud SQL para 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
   

   O utilizador root é sqlserver com uma palavra-passe de sqlserver12@. Tome também nota do endereço IP do SQL Server.

2. Armazene o endereço IP da VM do SQL Server de origem numa 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 autorizações:

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

4. Crie uma base de dados de destino:

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

5. Na linha de comandos do PowerShell que iniciou como administrador, verifique a conetividade com o 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'.

Iniciar a replicação da VM para o Cloud SQL

Através da replicação de instantâneos do SQL Server, cria uma tarefa para replicar instantâneos da base de dados no Cloud SQL.

Configure o distribuidor

Agora, configure a VM do SQL Server para ser um distribuidor do SQL Server. Na prática, um distribuidor pode ser executado numa máquina separada, mas neste tutorial, vai executá-lo na mesma MV.

1. Na linha de comandos do PowerShell que iniciou como administrador, atualize o nome da instância local do SQL Server para corresponder ao nome do anfitrião 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 receber o erro Cannot open MSSQLServer service on computer '.'., é provável que não tenha executado a linha de comandos do PowerShell como administrador.

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

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

3. Especifique a base 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 a base 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. Registe o nome da VM do SQL Server no distribuidor como publicador:

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

Configure a publicação

Agora que a distribuição está configurada, configure as tabelas que são publicadas.

1. Na linha de comandos 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 a base 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 resumo da origem para o Cloud SQL uma vez por 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. Adicione artigos (tabelas) à publicação. Embora a base de dados AdventureWorksLT2017 contenha muitas tabelas, para simplificar neste tutorial, vai replicar 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 instantâneo dos artigos. Se não o fizer, o agendador pode demorar até uma hora a criar a captura de ecrã inicial.

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

Configure uma subscrição

Agora, cria uma subscrição que envia os dados publicados para o Cloud SQL.

1. Na linha de comandos do PowerShell, crie uma subscrição para enviar o instantâneo de 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

Limpar

Depois de concluir o tutorial, pode limpar os recursos que criou para que deixem de usar a quota e incorrer em custos. As secções seguintes descrevem como eliminar ou desativar estes recursos.

Elimine o projeto

Atenção: a eliminação de um projeto tem os seguintes efeitos:
• Tudo no projeto é eliminado. Se usou um projeto existente para as tarefas neste documento, quando o elimina, também elimina qualquer outro trabalho que tenha feito no projeto.
• Os IDs dos projetos personalizados são perdidos. Quando criou este projeto, pode ter criado um ID do projeto personalizado que quer usar no futuro. Para preservar os URLs que usam o ID do projeto, como um URL appspot.com, elimine os recursos selecionados no projeto em vez de eliminar o projeto inteiro.
1. In the Google Cloud console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.

O que se segue?

• Saiba como migrar dados do SQL Server 2017 para o Cloud SQL para SQL Server através de ficheiros de cópia de segurança.
• Saiba como migrar dados do SQL Server 2008 para o Cloud SQL para SQL Server através de ficheiros de cópia de segurança.
• Explore arquiteturas de referência, diagramas e práticas recomendadas sobre o Google Cloud. Consulte o nosso Centro de arquitetura na nuvem.