Compartir vía

Configuración de un nodo del mismo nivel como parte de un grupo 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.

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: primera réplica en el grupo de disponibilidad de MyAG.
  • Node2: segunda réplica en el grupo de disponibilidad de MyAG.
  • MyAG: nombre del grupo de disponibilidad que se creará en Node1 y Node2.
  • MyAGListenerName: nombre del agente de escucha del grupo de disponibilidad.
  • Dist1: nombre de la instancia del distribuidor remoto.
  • MyDBName: nombre de la base de datos.
  • P2P_MyDBName: nombre de publicación.

Peer2

  • Node3: servidor independiente que hospeda una instancia predeterminada de SQL Server.
  • Dist2: nombre de la instancia del distribuidor remoto.
  • MyDBName: nombre de la base de datos.
  • P2P_MyDBName: nombre de publicación.

Requisitos previos

  • Dos instancias de SQL Server en servidores virtuales o físicos separados para alojar el grupo de disponibilidad. El grupo de disponibilidad contendrá una base de datos del mismo nivel.

  • Una instancia de SQL Server para hospedar otra 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)

  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 Node1 y Node2 como publicador remoto.

    @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, 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 MyAGListername.

    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. No incluya corchetes angulares <>.

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 una instancia independiente de SQL Server (Peer2) para que participe.

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

  1. Configure la distribución en el distribuidor.

    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. Configuración de Node3 como publicador remoto en el distribuidor Dist2

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

Configuración del publicador (Peer2)

  1. En Node3, configure la distribución remota.

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

  2. En Node3, habilite la base de datos para replicación.

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

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

Creación de una suscripción de inserción de Peer1 a Peer2

Este paso crea una suscripción de inserción del grupo de disponibilidad a la instancia independiente de SQL Server.

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

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'Node3'
 , @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'Node3'
 , @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 crear una suscripción de inserción de Peer2 en el agente de escucha del grupo de disponibilidad, 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>'

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 aparezcan como servidores vinculados.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

Paso siguiente

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