Tutorial: Configuración de la replicación transaccional entre Instancia administrada de Azure SQL y SQL Server

Artículo
04/26/2023

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:

Configurar una instancia administrada como publicador de replicación.
Configurar una instancia administrada como distribuidor de replicación.
Configurar SQL Server como suscriptor.

Replicación entre un publicador de instancia administrada, un distribuidor de instancia administrada y un suscriptor de SQL Server

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.

Nota:

En este artículo se describe el uso de la replicación transaccional en la Instancia administrada de Azure SQL Database. Esto no está relacionado con los grupos de conmutación por error, una característica de Instancia administrada de Azure SQL que permite crear réplicas completas legibles de instancias individuales. Hay consideraciones adicionales al configurar la replicación transaccional con grupos de conmutación por error.

Requisitos previos

Para completar el tutorial, asegúrese de que cuenta con estos requisitos previos:

Una suscripción de Azure.
Experiencia con la implementación de dos instancias administradas dentro de la misma red virtual.
Un suscriptor de SQL Server, ya sea local o en una máquina virtual de Azure. En este tutorial se usa una máquina virtual de Azure.
SQL Server Management Studio (SSMS) 18.0 o versiones posteriores.
La versión más reciente de Azure PowerShell.
Los puertos 445 y 1433 permiten el tráfico SQL en Azure Firewall y en el Firewall de Windows.

Crear el grupo de recursos

Use el siguiente fragmento de código de PowerShell para crear un grupo de recursos:

# set variables
$ResourceGroupName = "SQLMI-Repl"
$Location = "East US 2"

# Create a new resource group
New-AzResourceGroup -Name  $ResourceGroupName -Location $Location

Creación de dos instancias administradas

Cree dos instancias administradas dentro de este nuevo grupo de recursos mediante Azure Portal.

El nombre de la instancia administrada del publicador debe ser sql-mi-publisher (junto con algunos caracteres para la selección aleatoria) y el nombre de la red virtual debe ser vnet-sql-mi-publisher.
El nombre de la instancia administrada del distribuidor debe ser sql-mi-distributor (junto con algunos caracteres para la selección aleatoria) y debe estar en la misma red virtual que la instancia administrada del publicador.

Para más información sobre cómo crear una instancia administrada, consulte Creación de una instancia administrada en el portal.

Nota:

Por motivos de simplicidad, y dado que es la configuración más común, en este tutorial se sugiere colocar la instancia administrada del distribuidor dentro de la misma red virtual que la del publicador. Sin embargo, es posible crear el distribuidor en una red virtual distinta. Para ello, tendrá que configurar el emparejamiento de red virtual entre las redes virtuales del publicador y del distribuidor y, luego, configurar el emparejamiento de red virtual entre las redes virtuales del distribuidor y del suscriptor.

Creación de una máquina virtual con SQL Server

Cree una máquina virtual con SQL Server en Azure Portal. La máquina virtual con SQL Server debe tener las siguientes características:

Nombre: sql-vm-sub
Imagen: SQL Server 2016 o superior.
Grupo de recursos: el mismo que el de la instancia administrada.
Red virtual: sql-vm-sub-vnet.

Para más información sobre la implementación de una máquina virtual con SQL Server en Azure, consulte Inicio rápido: Creación de una máquina virtual con SQL Server.

Configuración del emparejamiento de red virtual

Configure el emparejamiento de red virtual para habilitar la comunicación entre la red virtual de las dos instancias administradas y la red virtual de SQL Server. Para ello, use este fragmento de código de PowerShell:

# Set variables
$SubscriptionId = '<SubscriptionID>'
$resourceGroup = 'SQLMI-Repl'
$pubvNet = 'sql-mi-publisher-vnet'
$subvNet = 'sql-vm-sub-vnet'
$pubsubName = 'Pub-to-Sub-Peer'
$subpubName = 'Sub-to-Pub-Peer'

$virtualNetwork1 = Get-AzVirtualNetwork `
  -ResourceGroupName $resourceGroup `
  -Name $pubvNet

 $virtualNetwork2 = Get-AzVirtualNetwork `
  -ResourceGroupName $resourceGroup `
  -Name $subvNet  

# Configure VNet peering from publisher to subscriber
Add-AzVirtualNetworkPeering `
  -Name $pubsubName `
  -VirtualNetwork $virtualNetwork1 `
  -RemoteVirtualNetworkId $virtualNetwork2.Id

# Configure VNet peering from subscriber to publisher
Add-AzVirtualNetworkPeering `
  -Name $subpubName `
  -VirtualNetwork $virtualNetwork2 `
  -RemoteVirtualNetworkId $virtualNetwork1.Id

# Check status of peering on the publisher VNet; should say connected
Get-AzVirtualNetworkPeering `
 -ResourceGroupName $resourceGroup `
 -VirtualNetworkName $pubvNet `
 | Select PeeringState

# Check status of peering on the subscriber VNet; should say connected
Get-AzVirtualNetworkPeering `
 -ResourceGroupName $resourceGroup `
 -VirtualNetworkName $subvNet `
 | Select PeeringState

Una vez que se establece el emparejamiento de red virtual, pruebe la conectividad iniciando SQL Server Management Studio (SSMS) en SQL Server y conectándose a ambas instancias administradas. Para más información sobre cómo conectarse a una instancia administrada mediante SSMS, consulte Uso de SSMS para conectarse a Instancia administrada de SQL.

Prueba de la conectividad a las instancias administradas

Crear una zona DNS privada

Una zona DNS privada permite el enrutamiento DNS entre las instancias administradas y SQL Server.

Crear una zona DNS privada

Inicie sesión en el Portal de Azure.
Seleccione Crear un recurso para crear un recurso de Azure.
Busque private dns zone en Azure Marketplace.
Elija el recurso Zona DNS privada publicado por Microsoft y, luego, seleccione Crear para crear la zona DNS.
Elija la suscripción y el grupo de recursos en la lista desplegable.
Proporcione un nombre arbitrario para la zona DNS, como repldns.com.
Seleccione Revisar + crear. Revise los parámetros de la zona DNS privada y, luego, seleccione Crear para crear el recurso.

Creación de un registro A

Vaya a la nueva zona DNS privada y seleccione Información general.
Seleccione + Conjunto de registros para crear un nuevo registro D.
Proporcione el nombre de la VM con SQL Server, así como la dirección IP interna privada.
Seleccione Aceptar para crear el registro D.

Vincular la red virtual

Vaya a la nueva zona DNS privada y seleccione Vínculos de red virtual.
Seleccione +Agregar.
Proporcione un nombre para el vínculo, como Pub-link.
Seleccione la suscripción en la lista desplegable y, luego, seleccione la red virtual de la instancia administrada del publicador.
Active la casilla junto a Habilitar el registro automático.
Seleccione Aceptar para vincular la red virtual.
Repita estos pasos para agregar un vínculo para la red virtual del suscriptor, con un nombre como Sub-link.

Creación de una cuenta de Azure Storage

Cree una cuenta de Azure Storage para el directorio de trabajo y, a continuación, 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 la cadena de conexión de la clave 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 más información, consulte Administración de las claves de acceso de la cuenta de almacenamiento.

Crear una base de datos

Cree una base de datos en la instancia administrada del publicador. Para hacerlo, siga estos pasos:

Inicie SQL Server Management Studio en SQL Server.
Conéctese a la instancia administrada sql-mi-publisher.
Abra una ventana Nueva consulta y ejecute la siguiente consulta de T-SQL para crear la base de datos.

-- Create the databases
USE [master]
GO

-- Drop database if it exists
IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'ReplTutorial')
BEGIN
    DROP DATABASE ReplTutorial
END
GO

-- Create new database
CREATE DATABASE [ReplTutorial]
GO

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

-- Populate table with data
USE [ReplTutorial]
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

Configuración de distribución

Una vez que ha establecido la conectividad y tiene una base de datos de ejemplo, puede configurar la distribución en la instancia administrada sql-mi-distributor. Para hacerlo, siga estos pasos:

Inicie SQL Server Management Studio en SQL Server.
Conéctese a la instancia administrada sql-mi-distributor.

Abra una ventana Nueva consulta y ejecute el siguiente código de Transact-SQL para configurar la distribución en la instancia administrada del distribuidor:

EXEC sp_adddistributor @distributor = 'sql-mi-distributor.b6bf57.database.windows.net', @password = '<distributor_admin_password>'

EXEC sp_adddistributiondb @database = N'distribution'

EXEC sp_adddistpublisher @publisher = 'sql-mi-publisher.b6bf57.database.windows.net', -- primary publisher
     @distribution_db = N'distribution',
     @security_mode = 0,
     @login = N'azureuser',
     @password = N'<publisher_password>',
     @working_directory = N'\\replstorage.file.core.windows.net\replshare',
     @storage_connection_string = N'<storage_connection_string>'
     -- example: @storage_connection_string = N'DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;EndpointSuffix=core.windows.net'

Nota:

Asegúrese de usar solo barras diagonales inversas (\) para el parámetro @working_directory. El uso de una barra diagonal (/) puede producir un error al conectarse al recurso compartido de archivos.

Conéctese a la instancia administrada sql-mi-publisher.

Abra una ventana Nueva consulta y ejecute el siguiente código de Transact-SQL para registrar el distribuidor en el publicador:

Use MASTER
EXEC sys.sp_adddistributor @distributor = 'sql-mi-distributor.b6bf57.database.windows.net', @password = '<distributor_admin_password>'

Creación de la publicación

Una vez configurada la distribución, ahora puede crear la publicación. Para hacerlo, siga estos pasos:

Inicie SQL Server Management Studio en SQL Server.
Conéctese a la instancia administrada sql-mi-publisher.
En el Explorador de objetos, expanda el nodo Replicación y haga clic con el botón derecho en la carpeta Local Publication (Publicación local). Seleccione Nueva publicación... .
Seleccione Siguiente para dejar atrás la página principal.
En la página Base de datos de publicación, seleccione la base de datos ReplTutorial que creó anteriormente. Seleccione Next (Siguiente).
En la página Tipo de publicación, seleccione Publicación transaccional. Seleccione Next (Siguiente).
En la página Artículos, active la casilla situada junto a Tablas. Seleccione Next (Siguiente).
En la página Filtrar filas de tabla, seleccione Siguiente sin agregar ningún filtro.
En la página Agente de instantáneas, active la casilla junto a Crear una instantánea inmediatamente y mantenerla disponible para inicializar suscripciones. Seleccione Next (Siguiente).
En la página Seguridad del agente, seleccione Configuración de seguridad. Proporcione credenciales de inicio de sesión de SQL Server para usar con el Agente de instantáneas y para conectarse al publicador. Seleccione Aceptar para cerrar la página Seguridad del Agente de instantáneas. Seleccione Next (Siguiente).
En la página Acciones del asistente, elija Crear la publicación y (opcionalmente) elija Generar un archivo de script con los pasos para crear la publicación si quiere guardar este script para más tarde.
En la página Finalización del asistente, asigne a la publicación el nombre ReplTest y seleccione Siguiente para crear la publicación.
Una vez creada la publicación, actualice el nodo Replication en el Explorador de objetos y expanda Publicaciones locales para ver la nueva publicación.

Creación de la suscripción

Una vez creada la publicación, puede crear la suscripción. Para hacerlo, siga estos pasos:

Inicie SQL Server Management Studio en SQL Server.
Conéctese a la instancia administrada sql-mi-publisher.
Abra una ventana Nueva consulta y ejecute el siguiente código de Transact-SQL para agregar la suscripción y el agente de distribución. Use el DNS como parte del nombre del suscriptor.

use [ReplTutorial]
exec sp_addsubscription
@publication = N'ReplTest',
@subscriber = N'sql-vm-sub.repldns.com', -- include the DNS configured in the private DNS zone
@destination_db = N'ReplSub',
@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'ReplTest',
@subscriber = N'sql-vm-sub.repldns.com', -- include the DNS configured in the private DNS zone
@subscriber_db = N'ReplSub',
@job_login = N'azureuser',
@job_password = '<Complex Password>',
@subscriber_security_mode = 0,
@subscriber_login = N'azureuser',
@subscriber_password = '<Complex Password>',
@dts_package_location = N'Distributor'
GO

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:

Use ReplSub
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.

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

Limpieza de recursos

Vaya a su grupo de recursos en Azure Portal.
Seleccione las instancias administradas y después seleccione Eliminar. Escriba yes en el cuadro de texto para confirmar que quiere eliminar el recurso y después seleccione yes. Este proceso puede tardar algún tiempo en completarse en segundo plano y, hasta que finalice, no podrá eliminar el clúster virtual ni ningún otro recurso dependiente. Supervise la eliminación en la pestaña Actividad para confirmar que la instancia administrada se ha eliminado.
Una vez que se elimine la instancia administrada, puede eliminar el clúster virtual si lo selecciona en el grupo de recursos y, después, elige Eliminar. Escriba yes en el cuadro de texto para confirmar que quiere eliminar el recurso y después seleccione yes.
Elimine todos los recursos restantes. Escriba yes en el cuadro de texto para confirmar que quiere eliminar el recurso y después seleccione yes.
Para eliminar el grupo de recursos, seleccione Eliminar grupo de recursos, escriba el nombre del grupo de recursos (myResourceGroup) y, a continuación, seleccione Eliminar.

Errores conocidos

No se admiten inicios de sesión de Windows

Exception Message: Windows logins are not supported in this version of SQL Server.

El agente se configuró con un inicio de sesión de Windows y lo que necesita es usar un inicio de sesión de SQL Server. Use la página Seguridad del agente de Propiedades de la publicación para cambiar las credenciales de inicio de sesión a un inicio de sesión de SQL Server.

No se pudo conectar a Azure Storage

Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare' Failed to connect to Azure Storage '' with OS error: 53.

19-11-2019 02:21:05.07 Se ha obtenido la cadena de conexión de Azure Storage para replstorage 19-11-2019 02:21:05.07 Conectando a Azure Files Storage '\replstorage.file.core.windows.net\replshare' 19-11-2019 02:21:31.21 ''No se pudo conectar a Azure Storage'' con error del SO: 53.

Puede que el puerto 445 esté cerrado en Azure Firewall, en el Firewall de Windows o en ambos.

Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare' Failed to connect to Azure Storage '' with OS error: 55.

El uso de una barra diagonal en lugar de una barra diagonal inversa en la ruta de acceso del archivo del el recurso compartido de archivos puede producir este error.

Esto es correcto: \\replstorage.file.core.windows.net\replshare
Esto puede producir un error 55 del sistema operativo: '\\replstorage.file.core.windows.net/replshare'

No se pudo conectar al suscriptor

The process could not connect to Subscriber 'SQL-VM-SUB Could not open a connection to SQL Server [53]. A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

Posibles soluciones:

Asegúrese de que el puerto 1433 esté abierto.
Asegúrese de que TCP/IP esté habilitado en el suscriptor.
Confirme que se usó el nombre DNS al crear el suscriptor.
Compruebe que las redes virtuales estén vinculadas correctamente en la zona DNS privada.
Compruebe que el registro D esté configurado correctamente.
Compruebe que el emparejamiento de red virtual está configurado correctamente.

Al agregar una nueva suscripción mediante el Asistente para nueva suscripción, en la página Publicación, es posible que no se muestren las bases de datos y las publicaciones como opciones disponibles, y podría aparecer el mensaje de error siguiente:

There are no publications to which you can subscribe, either because this server has no publications or because you do not have sufficient privileges to access the publications.

Aunque puede que este mensaje de error sea verdad y no haya realmente publicaciones disponibles en el publicador al que se ha conectado, o que no tenga permisos suficientes, este error también podría deberse a una versión anterior de SQL Server Management Studio. Intente actualizar a SQL Server Management Studio 18.0 o superior para que esto se descarte como causa principal.

Pasos siguientes

Habilitar características de seguridad

Consulte en el artículo ¿Qué es Instancia administrada de Azure SQL? la lista completa de las formas de proteger la base de datos. Se abordan las características de seguridad siguientes:

Funcionalidades de Instancia administrada de SQL

Para obtener una información general completa de las funcionalidades de una instancia administrada, consulte:

Funcionalidades de Instancia administrada de SQL

Compartir a través de

Tutorial: Configuración de la replicación transaccional entre Instancia administrada de Azure SQL y SQL Server

Requisitos previos

Crear el grupo de recursos

Creación de dos instancias administradas

Creación de una máquina virtual con SQL Server

Configuración del emparejamiento de red virtual

Crear una zona DNS privada

Crear una zona DNS privada

Creación de un registro A

Vincular la red virtual

Creación de una cuenta de Azure Storage

Crear una base de datos

Configuración de distribución

Creación de la publicación

Creación de la suscripción

Prueba de la replicación

Limpieza de recursos

Errores conocidos

No se admiten inicios de sesión de Windows

No se pudo conectar a Azure Storage

No se pudo conectar al suscriptor

Pasos siguientes

Habilitar características de seguridad

Funcionalidades de Instancia administrada de SQL

Comentarios

Recursos adicionales

Compartir a través de

Tutorial: Configuración de la replicación transaccional entre Instancia administrada de Azure SQL y SQL Server

Requisitos previos

Crear el grupo de recursos

Creación de dos instancias administradas

Creación de una máquina virtual con SQL Server

Configuración del emparejamiento de red virtual

Crear una zona DNS privada

Crear una zona DNS privada

Creación de un registro A

Vincular la red virtual

Creación de una cuenta de Azure Storage

Crear una base de datos

Configuración de distribución

Creación de la publicación

Creación de la suscripción

Prueba de la replicación

Limpieza de recursos

Errores conocidos

No se admiten inicios de sesión de Windows

No se pudo conectar a Azure Storage

No se pudo conectar al suscriptor

No hay publicaciones a las que pueda suscribirse

Pasos siguientes

Habilitar características de seguridad

Funcionalidades de Instancia administrada de SQL

Comentarios

Recursos adicionales