Tutorial: Configuración de la replicación entre dos instancias administradas

Se aplica a: Azure SQL Managed Instance

La replicación transaccional permite replicar datos de una base de datos a otra hospedada tanto en SQL Server como en Azure SQL Managed Instance. Instancia administrada de SQL puede ser un publicador, un distribuidor o un suscriptor en la topología de replicación. Consulte las configuraciones de la replicación transaccional para ver las opciones disponibles.

En este tutorial, aprenderá a:

Este tutorial va dirigido a un público experimentado, así que se da por hecho que el usuario está familiarizado con la implementación y la conexión a ambas instancias administradas, y con máquinas virtuales con SQL Server en Azure.

Requisitos

Para configurar Instancia administrada de SQL de forma que funcione como un publicador o distribuidor, deben darse las siguientes condiciones:

• Que la instancia administrada del publicador se encuentre en la misma red virtual que el distribuidor y el suscriptor o se hayan configurado emparejamiento de VNET o puertas de enlace de VPN entre las redes virtuales de las tres entidades.
• La conectividad usa la autenticación de SQL entre los participantes de la replicación.
• Un recurso compartido de cuenta de Azure Storage para el directorio de trabajo de replicación.
• El puerto 445 (salida TCP) está abierto en las reglas de seguridad del grupo de seguridad de red de la instancia administrada para tener acceso al recurso compartido de archivos de Azure. Si aparece el error failed to connect to azure storage <storage account name> with os error 53, deberá agregar una regla de salida al grupo de seguridad de red de la subred de Instancia administrada de SQL adecuada.

1 - Creación de un grupo de recursos

Use Azure Portal para crear un grupo de recursos denominado SQLMI-Repl.

2 - Creación de instancias administradas

Use Azure Portal para crear dos Instancias administradas de SQL en las mismas red virtual y subred. Por ejemplo, asigne un nombre a las dos instancias administradas:

• sql-mi-pub (junto con algunos caracteres para la selección aleatoria)
• sql-mi-sub (junto con algunos caracteres para la selección aleatoria)

También deberá configurar una máquina virtual de Azure para la conexión a las instancias administradas.

3\. Creación de una cuenta de Azure Storage

Cree una cuenta de Azure Storage para el directorio de trabajo y, después, cree un recurso compartido de archivos dentro de la cuenta de almacenamiento.

Copie la ruta de acceso del recurso compartido de archivos en el formato \\storage-account-name.file.core.windows.net\file-share-name.

Ejemplo: \\replstorage.file.core.windows.net\replshare

Copie las claves de acceso de almacenamiento en el formato DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.windows.net.

Ejemplo: DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;EndpointSuffix=core.windows.net

Para obtener más información, consulte Administración de las claves de acceso de la cuenta de almacenamiento.

4 - Creación de una base de datos del publicador

Conéctese a la instancia administrada de sql-mi-pub mediante SQL Server Management Studio y ejecute el siguiente código de Transact-SQL (T-SQL) para crear la base de datos del publicador:

USE [master]
GO

CREATE DATABASE [ReplTran_PUB]
GO

USE [ReplTran_PUB]
GO
CREATE TABLE ReplTest (
  ID INT NOT NULL PRIMARY KEY,
  c1 VARCHAR(100) NOT NULL,
  dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO


USE [ReplTran_PUB]
GO

INSERT INTO ReplTest (ID, c1) VALUES (6, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (2, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (3, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (4, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (5, 'pub')
GO
SELECT * FROM ReplTest
GO

5 - Creación de una base de datos del suscriptor

Conéctese a la instancia administrada de sql-mi-sub mediante SQL Server Management Studio y ejecute el siguiente código de T-SQL para crear la base de datos del suscriptor vacía:

USE [master]
GO

CREATE DATABASE [ReplTran_SUB]
GO

USE [ReplTran_SUB]
GO
CREATE TABLE ReplTest (
  ID INT NOT NULL PRIMARY KEY,
  c1 VARCHAR(100) NOT NULL,
  dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO

6 - Configuración de la distribución

Conéctese a la instancia administrada de sql-mi-pub mediante SQL Server Management Studio y ejecute el siguiente código de T-SQL para configurar la base de datos de distribución.

USE [master]
GO

EXEC sp_adddistributor @distributor = @@ServerName;
EXEC sp_adddistributiondb @database = N'distribution';
GO

7 - Configuración del publicador para usar el distribuidor

En la Instancia administrada de SQL sql-mi-pub del publicador, cambie la ejecución de consultas al modo SQLCMD y ejecute el código siguiente para registrar el distribuidor nuevo con el publicador.

:setvar username loginUsedToAccessSourceManagedInstance
:setvar password passwordUsedToAccessSourceManagedInstance
:setvar file_storage "\\storage-account-name.file.core.windows.net\file-share-name"
-- example: file_storage "\\replstorage.file.core.windows.net\replshare"
:setvar file_storage_key "DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.windows.net"
-- example: file_storage_key "DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;EndpointSuffix=core.windows.net"

USE [master]
EXEC sp_adddistpublisher
  @publisher = @@ServerName,
  @distribution_db = N'distribution',
  @security_mode = 0,
  @login = N'$(username)',
  @password = N'$(password)',
  @working_directory = N'$(file_storage)',
  @storage_connection_string = N'$(file_storage_key)'; -- Remove this parameter for on-premises publishers

Este script configura un publicador local en la instancia administrada, agrega un servidor vinculado y crea un conjunto de trabajos del Agente SQL Server.

8 - Creación de la publicación y el suscriptor

A través del modo SQLCMD, ejecute el siguiente script de T-SQL para habilitar la replicación de la base de datos y configure la replicación entre el publicador, el distribuidor y el suscriptor.

-- Set variables
:setvar username sourceLogin
:setvar password sourcePassword
:setvar source_db ReplTran_PUB
:setvar publication_name PublishData
:setvar object ReplTest
:setvar schema dbo
:setvar target_server "sql-mi-sub.wdec33262scj9dr27.database.windows.net"
:setvar target_username targetLogin
:setvar target_password targetPassword
:setvar target_db ReplTran_SUB

-- Enable replication for your source database
USE [$(source_db)]
EXEC sp_replicationdboption
  @dbname = N'$(source_db)',
  @optname = N'publish',
  @value = N'true';

-- Create your publication
EXEC sp_addpublication
  @publication = N'$(publication_name)',
  @status = N'active';


-- Configure your log reader agent
EXEC sp_changelogreader_agent
  @publisher_security_mode = 0,
  @publisher_login = N'$(username)',
  @publisher_password = N'$(password)',
  @job_login = N'$(username)',
  @job_password = N'$(password)';

-- Add the publication snapshot
EXEC sp_addpublication_snapshot
  @publication = N'$(publication_name)',
  @frequency_type = 1,
  @publisher_security_mode = 0,
  @publisher_login = N'$(username)',
  @publisher_password = N'$(password)',
  @job_login = N'$(username)',
  @job_password = N'$(password)';

-- Add the ReplTest table to the publication
EXEC sp_addarticle
  @publication = N'$(publication_name)',
  @type = N'logbased',
  @article = N'$(object)',
  @source_object = N'$(object)',
  @source_owner = N'$(schema)';

-- Add the subscriber
EXEC sp_addsubscription
  @publication = N'$(publication_name)',
  @subscriber = N'$(target_server)',
  @destination_db = N'$(target_db)',
  @subscription_type = N'Push';

-- Create the push subscription agent
EXEC sp_addpushsubscription_agent
  @publication = N'$(publication_name)',
  @subscriber = N'$(target_server)',
  @subscriber_db = N'$(target_db)',
  @subscriber_security_mode = 0,
  @subscriber_login = N'$(target_username)',
  @subscriber_password = N'$(target_password)',
  @job_login = N'$(username)',
  @job_password = N'$(password)';

-- Initialize the snapshot
EXEC sp_startpublication_snapshot
  @publication = N'$(publication_name)';

9 - Modificación de los parámetros del agente

Instancia administrada de Azure SQL está experimentando algunos problemas de back-end con la conectividad con los agentes de replicación. Mientras se aborda este problema, la solución consiste en aumentar el valor de tiempo de espera de inicio de sesión para los agentes de replicación.

Ejecute el siguiente comando de T-SQL en el publicador para aumentar el tiempo de espera de inicio de sesión:

-- Increase login timeout to 150s
update msdb..sysjobsteps set command = command + N' -LoginTimeout 150'
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %'

Ejecute de nuevo el siguiente comando de T-SQL para volver a establecer el tiempo de espera de inicio de sesión en el valor predeterminado, en caso de que lo necesite:

-- Increase login timeout to 30
update msdb..sysjobsteps set command = command + N' -LoginTimeout 30'
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %'

Reinicie los tres agentes para aplicar estos cambios.

10 - Prueba de la replicación

Una vez que se ha configurado la replicación, puede probarla mediante la inserción de nuevos elementos en el publicador y la observación de los cambios que se propagan al suscriptor.

Ejecute el siguiente fragmento de código de T-SQL para ver las filas en el suscriptor:

select * from dbo.ReplTest

Ejecute el siguiente fragmento de código de T-SQL para insertar filas adicionales en el publicador y, a continuación, compruebe las filas de nuevo en el suscriptor.

INSERT INTO ReplTest (ID, c1) VALUES (15, 'pub')

Limpieza de recursos

Para anular la publicación, ejecute el siguiente comando de T-SQL:

-- Drops the publication
USE [ReplTran_PUB]
EXEC sp_droppublication @publication = N'PublishData'
GO

Para quitar la opción de replicación de la base de datos, ejecute el siguiente comando de T-SQL:

-- Disables publishing of the database
USE [ReplTran_PUB]
EXEC sp_removedbreplication
GO

Para deshabilitar la publicación y la distribución, ejecute el siguiente comando de T-SQL:

-- Drops the distributor
USE [master]
EXEC sp_dropdistributor @no_checks = 1
GO

Para limpiar los recursos de Azure, elimine los recursos de Instancia administrada de SQL del grupo de recursos y, después, elimine el grupo de recursos SQLMI-Repl.

Pasos siguientes

También puede obtener más información sobre la replicación transaccional con Instancia administrada de Azure SQL o aprender a configurar la replicación entre un publicador/distribuidor de Instancia administrada de SQL y un suscriptor de SQL Server en una máquina virtual de Azure.