Configuración de ambos nodos del mismo nivel en grupos de disponibilidad

A partir de SQL Server 2019 (15.x) CU 13, una base de datos que pertenezca a un grupo de disponibilidad Always On de SQL Server puede participar como par en una topología de replicación transaccional entre iguales. Este artículo describe cómo configurar este escenario con dos pares, cada uno en su propio grupo de disponibilidad.

Los scripts de este ejemplo utilizan procedimientos almacenados de T-SQL.

Roles y nombres

Esta sección describe los roles y nombres de los distintos elementos que participan en la topología de replicación para este artículo.

Peer1

  • Nodo1: Réplica primaria para el primer grupo de disponibilidad
  • Nodo2: Réplica secundaria para el primer grupo de disponibilidad
  • MyAG: Nombre del primer grupo de disponibilidad
  • MyDBName: Base de datos Peer1 . Base de datos a publicar
  • Dist1: Distribuidor remoto
  • P2P_MyDBName: Nombre de la publicación
  • MyAGListenerName: oyente del grupo de disponibilidad

Peer2

  • Nodo3: Réplica primaria para el segundo grupo de disponibilidad
  • Nodo4: Réplica secundaria para el segundo grupo de disponibilidad
  • MyAG2: nombre del segundo grupo de disponibilidad
  • MyDBName: Base de datos a publicar
  • Dist2: Distribuidor remoto
  • P2P_MyDBName: Nombre de la publicación
  • MyAG2ListenerName: oyente del grupo de disponibilidad

Prerequisites

  • Cuatro instancias de SQL Server en servidores físicos o virtuales separados para alojar los grupos de disponibilidad. Dos grupos de disponibilidad contienen cada uno una base de datos de pares.

  • Dos instancias de SQL Server para alojar las bases de datos distribuidoras.

  • Todas las instancias de servidor requieren una edición soportada: edición Enterprise o edición Developer.

  • Todas las instancias de servidor requieren una versión compatible: SQL Server 2019 (15.x) CU13 o posterior.

  • Conectividad de red y ancho de banda suficientes entre todas las instancias.

  • Instala la replicación de SQL Server en todas las instancias de SQL Server.

    Para ver si la replicación está instalada en alguna instancia, ejecuta la siguiente consulta:

    USE master;   
GO   
DECLARE @installed int;   
EXEC @installed = sys.sp_MS_replication_installed;   
SELECT @installed;

    Nota:

    Para evitar un único punto de fallo en la base de datos de distribución, utilice un distribuidor remoto para cada nodo del mismo nivel.

    Para entornos de demostración o prueba, puedes configurar las bases de datos de distribución en una sola instancia.

Configurar el distribuidor y el editor remoto (Peer1)

Esta sección describe cómo configurar el primer nodo (Peer1) en un grupo de disponibilidad.

  1. Ejecuta sp_adddistributor para configurar la distribución en Dist1. Úsalo @password = para especificar una contraseña que el editor remoto utiliza para conectarse al distribuidor. Usa esta contraseña en cada editor remoto cuando configures el distribuidor remoto.

    USE master;  
GO  
EXEC sys.sp_adddistributor  
 @distributor = 'Dist1',  
 @password = '<Strong password for distributor>';

  2. Cree la base de datos de distribución en el distribuidor.

    USE master;
GO  
EXEC sys.sp_adddistributiondb  
 @database = 'distribution',  
 @security_mode = 1;

  3. Configura el publicador remoto de Node1 y Node2.

    @security_mode determina cómo los agentes de replicación se conectan al primario actual.

    • 1 = autenticación de Windows.
    • 0= Autenticación de SQL Server. Requiere @login y @password. El inicio de sesión y la contraseña especificados deben ser válidos en cada réplica secundaria.

    Nota:

    Si algún agente de replicación modificado se ejecuta en un ordenador distinto del distribuidor, el uso de la autenticación de Windows para la conexión a la principal requiere autenticación Kerberos para la comunicación entre los equipos host de réplica. El uso de un inicio de sesión de SQL Server para conectarse al servidor principal actual no requiere la autenticación Kerberos.

    USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node1',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir',  
 @security_mode = 1
USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node2',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir',  
 @security_mode = 1

Configura el editor en el editor original (Nodo1)

  1. Configurar el editor original de distribución remota (Nodo1). Especifica el mismo valor para @password que se usó cuando sp_adddistributor se ejecutó en el distribuidor para configurar la distribución.

    EXEC sys.sp_adddistributor  
@distributor = 'Dist1',  
@password = '<Password used when running sp_adddistributor on distributor server>'

  2. Habilite la base de datos para replicación.

    USE master;  
GO  
EXEC sys.sp_replicationdboption  
 @dbname = 'MyDBName',  
 @optname = 'publish',  
 @value = 'true';

Configurar el host réplica secundario como editor de replicación (Nodo2)

En cada host de réplica secundaria (Node2) del primer grupo de disponibilidad, configure la distribución. Especifica el mismo valor para @password que se usó cuando sp_adddistributor se ejecutó en el distribuidor para configurar la distribución.

EXEC sys.sp_adddistributor  
   @distributor = 'Dist1',  
   @password = '<Password used when running sp_adddistributor on distributor server>'

Haz que la base de datos forme parte del grupo de disponibilidad y crea el oyente (Peer1)

  1. En la réplica principal deseada, cree el grupo de disponibilidad con la base de datos como base de datos miembro.

  2. Crea un escuchador DNS para el grupo de disponibilidad. El agente de replicación se conecta a la réplica primaria actual usando el oyente. El siguiente ejemplo crea un oyente llamado MyAGListenerName.

    ALTER AVAILABILITY GROUP 'MyAG'
ADD LISTENER 'MyAGListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));

    Nota:

    En el guion anterior, la información entre corchetes ([ ... ]) es opcional. Úsalo para especificar un valor no predeterminado para el puerto TCP. No incluyas los corchetes.

Redirección del publicador original al nombre del agente de escucha de AG (Peer1)

En el distribuidor de Peer1, redirija el publicador original al nombre del agente de escucha de AG.

USE distribution;   
GO   
EXEC sys.sp_redirect_publisher    
@original_publisher = 'Node1',   
@publisher_db = 'MyDBName',   
@redirected_publisher = 'MyAGListenerName,<port>';

Nota:

En el guion anterior ,<port> es opcional. Solo es obligatorio si usas puertos no predeterminados. No incluya corchetes angulares <>.

Crear publicación entre pares (Peer1) en el publicador original - Node1

El siguiente script crea la publicación para Peer1.

EXEC master..sp_replicationdboption  @dbname=  'MyDBName'   
        ,@optname=  'publish'   
        ,@value=  'true'  
GO
DECLARE @publisher_security_mode smallint = 1
EXEC [MyDBName].dbo.sp_addlogreader_agent @publisher_security_mode = @publisher_security_mode
GO
DECLARE @allow_dts nvarchar(5) = N'false'
DECLARE @allow_pull nvarchar(5) = N'true'
DECLARE @allow_push nvarchar(5) = N'true'
DECLARE @description nvarchar(255) = N'Peer-to-Peer publication of database MyDBName from Node1'
DECLARE @enabled_for_p2p nvarchar(5) = N'true'
DECLARE @independent_agent nvarchar(5) = N'true'
DECLARE @p2p_conflictdetection nvarchar(5) = N'true'
DECLARE @p2p_originator_id int = 100
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @repl_freq nvarchar(10) = N'continuous'
DECLARE @restricted nvarchar(10) = N'false'
DECLARE @status nvarchar(8) = N'active'
DECLARE @sync_method nvarchar(40) = N'NATIVE'
EXEC [MyDBName].dbo.sp_addpublication @allow_dts = @allow_dts, @allow_pull = @allow_pull, @allow_push = @allow_push, @description = @description, @enabled_for_p2p = @enabled_for_p2p, @independent_agent = @independent_agent, @p2p_conflictdetection = @p2p_conflictdetection, @p2p_originator_id = @p2p_originator_id, @publication = @publication, @repl_freq = @repl_freq, @restricted = @restricted, @status = @status, @sync_method = @sync_method
GO
DECLARE @article nvarchar(256) = N'tbl0'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl0'
DECLARE @destination_table nvarchar(256) = N'tbl0'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl0'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, @source_owner = @source_owner, @type = @type
GO
DECLARE @article nvarchar(256) = N'tbl1'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl1'
DECLARE @destination_table nvarchar(256) = N'tbl1'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl1'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, 
@source_owner = @source_owner, @type = @type
GO

Hacer compatible la publicación peer-to-peer con el grupo de disponibilidad (Peer1)

En el editor original (Node1), ejecuta el siguiente script para hacer que la publicación sea compatible con el grupo de disponibilidad:

USE MyDBName
GO
DECLARE @publication sysname = N'P2P_MyDBName' 
DECLARE @property sysname = N'redirected_publisher' 
DECLARE @value sysname = N'MyAGListenerName,<port>' 
EXEC MyDBName..sp_changepublication @publication = @publication, @property = @property, @value = @value 
GO

Nota:

En el guion anterior ,<port> es opcional. Solo es obligatorio si usas puertos no predeterminados.

Una vez que haya completado los pasos anteriores, el grupo de disponibilidad estará preparado para participar en la topología del mismo nivel. Los siguientes pasos configuran un grupo de disponibilidad separado como segundo par (Peer2) en la topología de replicación peer-to-peer.

Configurar el distribuidor y el editor remoto (Peer2)

Esta sección describe cómo configurar el segundo par (Peer2) en un grupo de disponibilidad diferente.

  1. Ejecuta sp_adddistributor para configurar la distribución en Dist2. Úsalo @password = para especificar una contraseña que el editor remoto utiliza para conectarse al distribuidor. Usa esta contraseña en cada editor remoto cuando configures el distribuidor remoto.

    USE master;  
GO  
EXEC sys.sp_adddistributor  
 @distributor = 'Dist2',  
 @password = '<Strong password for distributor>';

  2. Cree la base de datos de distribución en el distribuidor.

    USE master;
GO  
EXEC sys.sp_adddistributiondb  
 @database = 'distribution',  
 @security_mode = 1;

  3. Configura el publicador remoto de Node3 y Node4.

    @security_mode determina cómo los agentes de replicación se conectan al primario actual.

    • 1 = autenticación de Windows.
    • 0= Autenticación de SQL Server. Requiere @login y @password. El inicio de sesión y la contraseña especificados deben ser válidos en cada réplica secundaria.

    Nota:

    Si algún agente de replicación modificado se ejecuta en un ordenador distinto del distribuidor, el uso de la autenticación de Windows para la conexión a la principal requiere autenticación Kerberos para la comunicación entre los equipos host de réplica. El uso de un inicio de sesión de SQL Server para conectarse al servidor principal actual no requiere la autenticación Kerberos.

    USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node3',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir2',  
 @security_mode = 1
USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node4',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir2',  
 @security_mode = 1

Configurar el editor (Peer2)

  1. Configura la distribución remota en (Nodo3). Especifica el mismo valor para @password que se usó cuando sp_adddistributor se ejecutó en el distribuidor para configurar la distribución.

    EXEC sys.sp_adddistributor  
@distributor = 'Dist2',  
@password = '<Password used when running sp_adddistributor on distributor server>'

  2. Habilite la base de datos para replicación.

    USE master;  
GO  
EXEC sys.sp_replicationdboption  
 @dbname = 'MyDBName',  
 @optname = 'publish',  
 @value = 'true';

Configurar el host réplica secundario como editor de replicación (Node4)

En cada host de réplica secundaria (Node4) del segundo grupo de disponibilidad, configure la distribución. Especifica el mismo valor para @password que se usó cuando sp_adddistributor se ejecutó en el distribuidor para configurar la distribución.

EXEC sys.sp_adddistributor  
   @distributor = 'Dist2',  
   @password = '<Password used when running sp_adddistributor on distributor server>'

Haz que la base de datos forme parte del grupo de disponibilidad y crea el oyente (Peer2)

  1. En la réplica principal deseada, cree el grupo de disponibilidad con la base de datos como base de datos miembro.

  2. Crea un escuchador DNS para el grupo de disponibilidad. El agente de replicación se conecta a la réplica primaria actual usando el oyente. El siguiente ejemplo crea un oyente llamado MyAG2ListenerName.

    ALTER AVAILABILITY GROUP 'MyAG2'
ADD LISTENER 'MyAG2ListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));

    Nota:

    En el guion anterior, la información entre corchetes ([ ... ]) es opcional. Úsalo para especificar un valor no predeterminado para el puerto TCP. No incluyas los corchetes.

Redirección del publicador original al nombre del agente de escucha de AG (Peer2)

En el distribuidor de Peer2, redirija el publicador original al nombre del agente de escucha de AG.

USE distribution;   
GO   
EXEC sys.sp_redirect_publisher    
@original_publisher = 'Node3',   
@publisher_db = 'MyDBName',   
@redirected_publisher = 'MyAG2ListenerName,<port>';

Nota:

En el guion anterior ,<port> es opcional. Solo es obligatorio si usas puertos no predeterminados. No incluya corchetes angulares <>.

Creación de una publicación del mismo nivel (Peer2)

El siguiente script crea la publicación para Peer2.

En Nodo3 ejecuta el siguiente comando para crear la publicación peer-to-peer.

EXEC master..sp_replicationdboption  @dbname=  'MyDBName'   
        ,@optname=  'publish'   
        ,@value=  'true'  
GO
DECLARE @publisher_security_mode smallint = 1
EXEC [MyDBName].dbo.sp_addlogreader_agent @publisher_security_mode = @publisher_security_mode
GO

-- Make sure that the value for @p2p_originator_id is different from Peer1.
DECLARE @allow_dts nvarchar(5) = N'false'
DECLARE @allow_pull nvarchar(5) = N'true'
DECLARE @allow_push nvarchar(5) = N'true'
DECLARE @description nvarchar(255) = N'Peer-to-Peer publication of database MyDBName from Node3'
DECLARE @enabled_for_p2p nvarchar(5) = N'true'
DECLARE @independent_agent nvarchar(5) = N'true'
DECLARE @p2p_conflictdetection nvarchar(5) = N'true'
DECLARE @p2p_originator_id int = 1
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @repl_freq nvarchar(10) = N'continuous'
DECLARE @restricted nvarchar(10) = N'false'
DECLARE @status nvarchar(8) = N'active'
DECLARE @sync_method nvarchar(40) = N'NATIVE'
EXEC [MyDBName].dbo.sp_addpublication @allow_dts = @allow_dts, @allow_pull = @allow_pull, @allow_push = @allow_push, @description = @description, @enabled_for_p2p = @enabled_for_p2p, @independent_agent = @independent_agent, @p2p_conflictdetection = @p2p_conflictdetection, @p2p_originator_id = @p2p_originator_id, @publication = @publication, @repl_freq = @repl_freq, @restricted = @restricted, @status = @status, @sync_method = @sync_method
GO
DECLARE @article nvarchar(256) = N'tbl0'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl0'
DECLARE @destination_table nvarchar(256) = N'tbl0'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl0'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, @source_owner = @source_owner, @type = @type
GO

DECLARE @article nvarchar(256) = N'tbl1'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl1'
DECLARE @destination_table nvarchar(256) = N'tbl1'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl1'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, 
@source_owner = @source_owner, @type = @type
GO

Hacer compatible la publicación de igual a igual con el grupo de disponibilidad (Peer2)

En el editor original (Node3), ejecuta el siguiente script para hacer que la publicación sea compatible con el grupo de disponibilidad:

USE MyDBName
GO
DECLARE @publication sysname = N'P2P_MyDBName' 
DECLARE @property sysname = N'redirected_publisher' 
DECLARE @value sysname = N'MyAG2ListenerName,<port>' 
EXEC MyDBName..sp_changepublication @publication = @publication, @property = @property, @value = @value 
GO

Nota:

En el guion anterior ,<port> es opcional. Solo es obligatorio si usas puertos no predeterminados.

Creación de una suscripción de inserción de Peer1 en el agente de escucha del grupo de disponibilidad para Peer2

Para crear una suscripción push desde Peer1 al oyente del grupo de disponibilidad Peer2, ejecuta el siguiente comando en Nodo1.

Ejecuta el siguiente script en Nodo1. Esto asume que Nodo1 está ejecutando la réplica primaria.

Importante

El script que hay a continuación especifica el nombre del agente de escucha del grupo de disponibilidad para el subscriptor.

@subscriber = N'MyAGListenerName,<port>'

Nota:

En el guion anterior ,<port> es opcional. Solo es obligatorio si usas puertos no predeterminados. No incluyas los paréntesis angulares <>.

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAG2Listener,<port>' 
 , @destination_db = N'MyDBName'
 , @subscription_type = N'push'
 , @sync_type = N'replication support only'
GO

EXEC [MyDBName].dbo.sp_addpushsubscription_agent 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAG2Listener,<port>' 
 , @subscriber_db = N'MyDBName'
 , @job_login = null 
 , @job_password = null
 , @subscriber_security_mode = 1
 , @frequency_type = 64
 , @frequency_interval = 1
 , @frequency_relative_interval = 1
 , @frequency_recurrence_factor = 0
 , @frequency_subday = 4
 , @frequency_subday_interval = 5
 , @active_start_time_of_day = 0
 , @active_end_time_of_day = 235959
 , @active_start_date = 0
 , @active_end_date = 0
 , @dts_package_location = N'Distributor'
GO

Crea una suscripción push desde Peer2 al oyente del grupo de disponibilidad (Peer1)

Para crear una suscripción push desde Peer2 al oyente del grupo de disponibilidad (Peer1), ejecuta el siguiente comando en Nodo3.

Importante

El script que hay a continuación especifica el nombre del agente de escucha del grupo de disponibilidad para el subscriptor.

@subscriber = N'MyAGListenerName,<port>'

Nota:

En el guion anterior ,<port> es opcional. Solo es obligatorio si usas puertos no predeterminados. No incluya corchetes angulares <>.

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAGListenerName,<port>'
 , @destination_db = N'MyDBName'
 , @subscription_type = N'push'
 , @sync_type = N'replication support only'
GO

EXEC [MyDBName].dbo.sp_addpushsubscription_agent 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAGListenerName,<port>'
 , @subscriber_db = N'MyDBName'
 , @job_login = null
 , @job_password = null
 , @subscriber_security_mode = 1
 , @frequency_type = 64
 , @frequency_interval = 1
 , @frequency_relative_interval = 1
 , @frequency_recurrence_factor = 0
 , @frequency_subday = 4
 , @frequency_subday_interval = 5
 , @active_start_time_of_day = 0
 , @active_end_time_of_day = 235959
 , @active_start_date = 0
 , @active_end_date = 0
 , @dts_package_location = N'Distributor'
GO

Configurar servidores enlazados

En cada host réplica secundario, asegúrate de que los suscriptores push de las publicaciones de la base de datos aparezcan como servidores enlazados.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';
  • Last updated on