Migrar datos de SQL Server 2017 a Cloud SQL para SQL Server mediante la replicación de capturas

---

En este tutorial se explica cómo migrar datos de Microsoft SQL Server 2017 Enterprise, que se ejecuta en Compute Engine, a Cloud SQL para SQL Server 2017 Enterprise. Este tutorial es útil si eres administrador de sistemas, desarrollador, ingeniero, administrador de bases de datos o ingeniero de DevOps. En el tutorial se explica cómo configurar SQL Server Distributor, cómo usar SQL Server Agent para la replicación de capturas en Cloud SQL para SQL Server 2017 y cómo validar que los datos se han importado correctamente.

En este tutorial, usarás la replicación de la instantánea para mantener sincronizados el origen y el destino. La replicación de instantáneas envía a los suscriptores una copia completa de cada artículo, es decir, de cada objeto de base de datos publicado. Cloud SQL también admite la replicación transaccional, que solo envía datos incrementales. Una limitación de la replicación transaccional es que las tablas deben tener claves principales. Para obtener más información sobre los tipos de replicación de SQL Server, consulta la documentación de SQL Server.

Para simplificar el tutorial, se usa el SQL Server de origen para alojar un distribuidor. En un entorno de producción, si los datos se replican desde fuera de Google Cloud, Google Cloud puede que prefieras alojar la distribución en el lado de Google Clouden una instancia de Compute Engine.

Nota: Cloud SQL para SQL Server no admite el envío de registros, la creación de imágenes especulares de bases de datos ni estas funciones. Microsoft SQL Server no admite la replicación desde las ediciones Developer, Web o Express.

En este tutorial se da por hecho que tienes conocimientos sobre lo siguiente:

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

Objetivos

• Crea una instancia de máquina virtual de SQL Server en Compute Engine para alojar la base de datos de ejemplo.
• Rellena una base de datos de ejemplo.
• Crea una instancia de Cloud SQL para SQL Server.
• Crea un distribuidor.
• Configura la publicación y la suscripción.
• Inicia la replicación de SQL Server a Cloud SQL.
• Valida los datos importados.

Costes

En este documento, se utilizan los siguientes componentes facturables de Google Cloud:

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

Para generar una estimación de costes basada en el uso previsto, utiliza la calculadora de precios.

Los usuarios nuevos Google Cloud pueden disfrutar de una prueba gratuita.

Antes de empezar

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. Instala el cliente de protocolo de escritorio remoto (RDP) que quieras. Para obtener más información, consulta Clientes de Escritorio Remoto de Microsoft. Si ya tienes instalado un cliente RDP, puedes saltarte esta tarea.

Cuando termines las tareas que se describen en este documento, puedes evitar que se te siga facturando eliminando los recursos que has creado. Para obtener más información, consulta la sección Limpiar.

Crear una VM de SQL Server

El primer paso es crear una instancia de SQL Server 2017 en una máquina virtual de Windows 2016 en Compute Engine y conectarse a ella mediante RDP.

1. En Cloud Shell, crea una instancia de SQL Server 2017 Standard en Windows Server 2016. La base de datos de origen debe ser una base de datos SQL Server Standard o Enterprise, ya que los niveles inferiores no tienen la función de editor de bases de datos.

   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
   

   En este tutorial, crearás la instancia en la zona us-central1-f con un tamaño de disco de arranque de 100 GB. Para obtener más información, consulta Ubicaciones en la nube.

2. Generar una contraseña de Windows:

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

3. En la consola de Google Cloud , ve a la página Instancias de VM.

   Ve a Instancias de VM .

4. En la sección Compute Engine de la Google Cloud consola, haga clic en el menú desplegable RDP y seleccione la opción Descargar el archivo RDP para descargar el archivo RDP de su instancia.

   Usa este archivo para conectarte a la instancia mediante un cliente RDP. Para obtener más información, consulta Clientes de Escritorio Remoto de Microsoft.

5. En los campos nombre de usuario y contraseña, introduce el nombre de usuario y la contraseña que has creado para la instancia de VM de SQL Server. Deja en blanco el campo Dominio y haz clic en Aceptar para conectarte a la VM de SQL Server.

   

   Cuando se te solicite, acepta el certificado.

6. Si aceptas los términos, haz clic en Continuar.

7. En la instancia, minimiza todas las ventanas, haz clic en Inicio en la barra de tareas de Windows, escribe PowerShell y, a continuación, haz clic con el botón derecho en la aplicación Windows PowerShell y selecciona Ejecutar como administrador.

8. En el símbolo del sistema de PowerShell, crea una estructura de directorios para el almacenamiento de la base de datos:

   mkdir c:\sql-server-data\adventureworks
   

Crear y rellenar una base de datos de ejemplo

Ahora, descarga el archivo de copia de seguridad de la base de datos AdventureWorks de Microsoft y restáuralo en tu instancia de SQL Server. Esta base de datos simula la base de datos de producción que quieres migrar.

1. En la línea de comandos de PowerShell, descarga el archivo de copia de seguridad AdventureWorksLT2017.bak en la unidad 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. Restaura la base de datos:

   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. Valida la base de datos recién restaurada consultando el número de filas de la tabla Customer. El resultado es de 847 filas.

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

Preparar la instancia de Cloud SQL

1. En Cloud Shell, crea la instancia de 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
   

   El usuario raíz es sqlserver y tiene la contraseña sqlserver12@. Anota también la dirección IP de SQL Server.

2. Almacena la dirección IP de la VM de SQL Server de origen en una variable de entorno:

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

3. Añade la dirección IP de la VM de SQL Server a la lista de permitidas de la instancia de Cloud SQL:

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

4. Crea una base de datos de destino:

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

5. En la línea de comandos de PowerShell que has iniciado como administrador, comprueba la conectividad con 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'"
   

   El comando imprime 'test'.

Iniciar la replicación de la VM a Cloud SQL

Con la replicación de capturas de SQL Server, puedes crear un trabajo para replicar capturas de bases de datos en Cloud SQL.

Configurar el distribuidor

Ahora, configura la VM de SQL Server para que sea un distribuidor de SQL Server. En la práctica, un distribuidor puede ejecutarse en una máquina independiente, pero en este tutorial lo ejecutarás en la misma máquina virtual.

1. En la línea de comandos de PowerShell que has iniciado como administrador, actualiza el nombre de la instancia local de SQL Server para que coincida con el nombre de host de la 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
   

   Si recibes el error Cannot open MSSQLServer service on computer '.'., es probable que no hayas ejecutado la línea de comandos de PowerShell como administrador.

2. Comprueba que el nombre de la instancia sea "sqlserver":

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

3. Especifica la base de datos de distribución:

   $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 la base de datos de distribución:

   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 el nombre de la máquina virtual de SQL Server con el distribuidor 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 la publicación

Ahora que ha configurado la distribución, puede elegir qué tablas se publican.

1. En la línea de comandos de PowerShell, habilita e inicia el agente de SQL Server:

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

2. Crea una publicación para la base de datos 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 la publicación para que envíe una captura de la fuente a Cloud SQL una vez 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. Añade artículos (tablas) a la publicación. Aunque la base de datos AdventureWorksLT2017 contiene muchas tablas, en este tutorial, para simplificar las cosas, replicarás tres tablas: Address, Customer y 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 una captura de los artículos. Si no lo haces, el programador puede tardar hasta una hora en crear la primera instantánea.

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

Configurar una suscripción

Ahora, crea una suscripción que envíe los datos publicados a Cloud SQL.

1. En la línea de comandos de PowerShell, crea una suscripción para enviar la captura de la publicación 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. Valida que los datos estén disponibles en la instancia de 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]"
   

   El comando imprime 417, 450 y 847.

Limpieza

Cuando hayas terminado el tutorial, puedes eliminar los recursos que has creado para que dejen de usar cuota y generar cargos. En las siguientes secciones se explica cómo eliminar o desactivar dichos recursos.

Eliminar el proyecto

Precaución: Eliminar un proyecto tiene los siguientes efectos:
• Se elimina todo el contenido del proyecto. Si has usado un proyecto que ya existía para las tareas de este documento, cuando lo elimines, también se eliminará cualquier otro trabajo que hayas realizado en él.
• Se pierden los IDs de proyecto personalizados. Cuando creaste este proyecto, es posible que hayas creado un ID de proyecto personalizado que quieras usar en el futuro. Para conservar las URLs que usan el ID del proyecto, como una URL appspot.com, elimina los recursos seleccionados dentro del proyecto en lugar de eliminar todo el proyecto.
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.

Siguientes pasos

• Consulta cómo migrar datos de SQL Server 2017 a Cloud SQL para SQL Server mediante archivos de copia de seguridad.
• Consulta cómo migrar datos de SQL Server 2008 a Cloud SQL para SQL Server mediante archivos de copia de seguridad.
• Consulta arquitecturas de referencia, diagramas y prácticas recomendadas sobre Google Cloud. Consulta nuestro Centro de arquitectura de Cloud.