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

  • Artículo

A partir de SQL Server 2019 (15.x) CU 13, una base de datos que pertenece a un grupo de disponibilidad Always On puede participar como un nodo del mismo nivel en una topología de replicación transaccional punto a punto. En este artículo se describe cómo configurar este escenario con dos nodos del mismo nivel, cada uno en el grupo de disponibilidad correspondiente.

En los scripts que hay en este ejemplo se usan procedimientos almacenados de T-SQL.

Roles y nombres

En esta sección se describen los roles y los nombres de los distintos elementos que participan en la topología de replicación de este artículo.

Peer1

  • Node1: réplica principal del primer grupo de disponibilidad
  • Node2: réplica secundaria del primer grupo de disponibilidad
  • MyAG: nombre del primer grupo de disponibilidad
  • MyDBName: base de datos de Peer1 Base de datos que se va a publicar
  • Dist1: distribuidor remoto
  • P2P_MyDBName: nombre de publicación
  • MyAGListenerName: agente de escucha del grupo de disponibilidad

Peer2

  • Node3: réplica principal del segundo grupo de disponibilidad
  • Node4: réplica secundaria del segundo grupo de disponibilidad
  • MyAG2: nombre del segundo grupo de disponibilidad
  • MyDBName: base de datos que se va a publicar
  • Dist2: distribuidor remoto
  • P2P_MyDBName: nombre de publicación
  • MyAG2ListenerName: agente de escucha del grupo de disponibilidad

Requisitos previos

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

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

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

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

  • Suficiente conectividad de red y ancho de banda entre todas las instancias.

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

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

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

    Nota:

    A fin de evitar un único punto de error para la base de datos de distribución, use un distribuidor remoto para cada nodo del mismo nivel.

    Para el entorno de demostración o prueba, puede configurar las bases de datos de distribución en una sola instancia.

Configuración del distribuidor y el publicador remoto (Peer1)

En esta sección se describe cómo configurar el primer nodo del mismo nivel (Peer1) en un grupo de disponibilidad.

  1. Ejecute sp_adddistributor para configurar la distribución en Dist1. Utilice @password = para especificar una contraseña que el publicador remoto utilice para conectarse al distribuidor. Utilice esta contraseña en cada publicador remoto cuando configure 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. Configure el publicador remoto de Node1 y Node2.

    @security_mode determina cómo los agentes de replicación se conectan a la réplica principal 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 algunos agentes de replicación modificados se ejecutan en un equipo distinto del distribuidor, el uso de la autenticación de Windows para la conexión a la principal requiere la 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 la conexión a la réplica principal actual no necesita 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

Configuración del publicador en el publicador original (Node1)

  1. Configure el publicador original de la distribución remota (Node1). Especifique el mismo valor para @password que el 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';

Configuración del host de la réplica secundaria como el publicador de replicación (Node2)

En cada host de réplica secundaria (Node2) del primer grupo de disponibilidad, configure la distribución. Especifique el mismo valor para @password que el 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>'

Configuración de la base de datos para que forme parte del grupo de disponibilidad y creación del agente de escucha (Peer1)

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

  2. Cree un agente de escucha de DNS para el grupo de disponibilidad. El agente de replicación se conecta a la réplica principal actual usando el agente de escucha. En el ejemplo siguiente se crea un agente de escucha llamado MyAGListenerName.

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

    Nota:

    En el script anterior, la información que sale entre corchetes ([ ... ]) es opcional. Úsela para especificar un valor no predeterminado para el puerto TCP. No incluya 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 script anterior, ,<port> es opcional. Solo se requiere si se usan puertos no predeterminados. No incluya corchetes angulares <>.

Creación de la publicación del mismo nivel (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

Configuración de una publicación del mismo nivel para que sea compatible con el grupo de disponibilidad (Peer1)

En el publicador original (Node1), ejecute el script siguiente para 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 script anterior, ,<port> es opcional. Solo se requiere si se usan 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. En los pasos siguientes se configura un grupo de disponibilidad separado como segundo nodo del mismo nivel (Peer2) en la topología de replicación punto a punto.

Configuración del distribuidor y el publicador remoto (Peer2)

En esta sección se describe cómo configurar el segundo nodo del mismo nivel (Peer2) en otro grupo de disponibilidad.

  1. Ejecute sp_adddistributor para configurar la distribución en Dist2. Utilice @password = para especificar una contraseña que el publicador remoto utilice para conectarse al distribuidor. Utilice esta contraseña en cada publicador remoto cuando configure 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. Configure el publicador remoto de Node3 y Node4.

    @security_mode determina cómo los agentes de replicación se conectan a la réplica principal 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 algunos agentes de replicación modificados se ejecutan en un equipo distinto del distribuidor, el uso de la autenticación de Windows para la conexión a la principal requiere la 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 la conexión a la réplica principal actual no necesita 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

Configuración del publicador (Peer2)

  1. Configure la distribución remota en (Node3). Especifique el mismo valor para @password que el 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';

Configuración del host de la réplica secundaria como el publicador de replicación (Node4)

En cada host de réplica secundaria (Node4) del segundo grupo de disponibilidad, configure la distribución. Especifique el mismo valor para @password que el 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>'

Configuración de la base de datos para que forme parte del grupo de disponibilidad y creación del agente de escucha (Peer2)

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

  2. Cree un agente de escucha de DNS para el grupo de disponibilidad. El agente de replicación se conecta a la réplica principal actual usando el agente de escucha. En el ejemplo siguiente se crea un agente de escucha llamado MyAG2ListenerName.

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

    Nota:

    En el script anterior, la información que sale entre corchetes ([ ... ]) es opcional. Úsela para especificar un valor no predeterminado para el puerto TCP. No incluya 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 script anterior, ,<port> es opcional. Solo se requiere si se usan 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 Node3, ejecute el comando siguiente para crear la publicación del mismo nivel.

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

-- Note – 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

Configuración de una publicación del mismo nivel para que sea compatible con el grupo de disponibilidad (Peer2)

En el publicador original (Node3), ejecute el script siguiente para 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 script anterior, ,<port> es opcional. Solo se requiere si se usan 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 de inserción de Peer1 en el agente de escucha del grupo de disponibilidad Peer2, ejecute el comando siguiente en Node1.

Ejecute el siguiente script en Node1. Se supondrá que Node1 está ejecutando la réplica principal.

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 script anterior, ,<port> es opcional. Solo se requiere si se usan puertos no predeterminados. No incluya corchetes 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

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

Para crear una suscripción de inserción de Peer2 en el agente de escucha del grupo de disponibilidad (Peer1), ejecute el comando siguiente en Node3.

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 script anterior, ,<port> es opcional. Solo se requiere si se usan 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

Configuración de servidores vinculados

En cada host de réplica secundaria, asegúrese de que los suscriptores de inserción de publicaciones de la base de datos aparezcan como servidores vinculados.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

Pasos siguientes