Cómo configurar la replicación transaccional bidireccional sin particiones (programación de la replicación con Transact-SQL)
La replicación transaccional bidireccional es una topología de replicación transaccional específica que permite a dos servidores intercambiar cambios mutuamente: cada servidor publica datos y después se suscribe a una publicación con los mismos datos en el otro servidor.
Esta topología también es compatible con la replicación transaccional punto a punto, pero la replicación bidireccional puede proporcionar un mayor rendimiento. Para obtener más información, vea Replicación transaccional del mismo nivel.
Para configurar una topología de replicación transaccional bidireccional sin particiones entre las bases de datos de dos servidores
Habilite cada servidor como un publicador y un distribuidor. Para obtener más información, vea Cómo configurar la publicación y la distribución (programación de la replicación con Transact-SQL).
En cada servidor de la base de datos que se va a replicar bidireccionalmente, ejecute sp_replicationdboption (Transact-SQL). Especifique el nombre de la base de datos para @dbname, un valor de publish para @optnamey un valor de true para @value.
En cada servidor de la base de datos que se va a replicar bidireccionalmente, ejecute sp_addpublication (Transact-SQL). Especifique un nombre de publicación para @publication.
En cada servidor de la base de datos que se va a replicar bidireccionalmente, ejecute sp_addarticle (Transact-SQL). Especifique los parámetros siguientes:
@article - el nombre del artículo.
@publication - el nombre de la publicación del paso 3.
@source_object - el nombre de la tabla publicada.
@destination_table - el nombre de la tabla equivalente en la base de datos del otro servidor.
@schema_option - asegúrese de que no se establece el valor 0x02.
[!NOTA]
Cuando no se establece un valor de 0x02, una operación & (AND bit a bit) (Transact-SQL) en @schema_option devolverá un valor de 0. Para realizar esta operación, debe convertir los valores binary a int.
@ins_cmd - un valor de CALL sp_ins_destination_article_name.
@upd_cmd - un valor de SCALL sp_upd_destination_article_name.
@del_cmd - un valor de CALL sp_del_destination_article_name.
[!NOTA]
Estos valores representan los formatos de llamada predeterminados. Se pueden usar otros formatos de llamada.
Repita el paso 4 para cada artículo de la publicación bidireccional.
En cada servidor de la base de datos que se va a replicar bidireccionalmente, ejecute sp_addsubscription (Transact-SQL). Especifique los parámetros siguientes:
@publication - el nombre de la publicación del paso 3.
@subscriber - el nombre del otro servidor.
@destination_db - el nombre de la base de datos del otro servidor.
@sync_type - un valor de none.
@status - un valor de active.
@loopback_detection - un valor de true.
En cada servidor de la base de datos que se va a replicar bidireccionalmente, ejecute sp_addpushsubscription_agent (Transact-SQL). Especifique los parámetros siguientes:
@publication - el nombre de la publicación del paso 3.
@subscriber - el nombre del otro servidor.
@subscriber_db - el nombre de la base de datos del otro servidor.
@job_login - las credenciales de Microsoft Windows con las que se ejecuta el Agente de distribución en el distribuidor.
@job_password - la contraseña de las credenciales de Windows con las que se ejecuta el Agente de distribución en el distribuidor.
.gif "Nota de seguridad")
Nota de seguridadCuando sea posible, pida a los usuarios que proporcionen credenciales de seguridad en tiempo de ejecución. Si debe almacenar las credenciales en un archivo de script, protéjalo para evitar el acceso no autorizado.
Con los nombres especificados en el paso 4, cree los procedimientos almacenados personalizados para administrar los conflictos y realizar las operaciones de inserción, actualización y eliminación. Para obtener más información, vea Cómo establecer el método de propagación para cambios de datos en artículos transaccionales (programación de la replicación con Transact-SQL).
Ejemplo
Este ejemplo crea dos bases de datos de prueba en el mismo servidor y, a continuación, configura la replicación transaccional bidireccional sin particiones entre ellas.
-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables
-- on the command line and in SQL Server Management Studio, see the
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".
USE master;
GO
-- Create the test databases if they do not exist.
IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'test1')
CREATE DATABASE test1;
IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'test2')
CREATE DATABASE test2;
GO
-- Enable the server as a Distributor, if not already done.
DECLARE @distributor AS sysname;
EXEC sp_helpdistributor @distributor = @distributor OUTPUT;
IF @distributor <> @@SERVERNAME
BEGIN
EXEC master..sp_adddistributor @distributor = @@SERVERNAME;
EXEC master..sp_adddistributiondb @database= 'distribution';
END
GO
-- Enable the server as a Publisher, if not already done.
IF NOT EXISTS (SELECT * FROM msdb..MSdistpublishers WHERE name = @@SERVERNAME)
EXEC master..sp_adddistpublisher
@publisher = @@SERVERNAME,
@distribution_db = 'distribution',
@working_directory = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\repldata';
GO
-- Enable the databases for transactional publishing.
EXEC sp_replicationdboption N'test1', N'publish', true;
EXEC sp_replicationdboption N'test2', N'publish', true;
GO
-- Create a table in test1 and populate with 10 rows.
USE test1;
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_test1')
DROP TABLE two_way_test1;
GO
CREATE TABLE two_way_test1
(pkcol int primary key not null,
intcol int,
charcol char(100),
datecol datetime
);
GO
INSERT INTO two_way_test1 VALUES (1, 10, 'row1', GETDATE());
INSERT INTO two_way_test1 VALUES (2, 20, 'row2', GETDATE());
INSERT INTO two_way_test1 VALUES (3, 30, 'row3', GETDATE());
INSERT INTO two_way_test1 VALUES (4, 40, 'row4', GETDATE());
INSERT INTO two_way_test1 VALUES (5, 50, 'row5', GETDATE());
INSERT INTO two_way_test1 VALUES (6, 60, 'row6', GETDATE());
INSERT INTO two_way_test1 VALUES (7, 70, 'row7', GETDATE());
INSERT INTO two_way_test1 VALUES (8, 80, 'row8', GETDATE());
INSERT INTO two_way_test1 VALUES (9, 90, 'row9', GETDATE());
INSERT INTO two_way_test1 VALUES (10, 100, 'row10', GETDATE());
GO
-- Create a table in test2 and populate with 10 rows
USE test2;
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_test2')
DROP TABLE two_way_test2;
GO
CREATE TABLE two_way_test2
(pkcol int primary key not null,
intcol int,
charcol char(100),
datecol datetime
);
GO
INSERT INTO two_way_test2 VALUES (1, 10, 'row1', GETDATE());
INSERT INTO two_way_test2 VALUES (2, 20, 'row2', GETDATE());
INSERT INTO two_way_test2 VALUES (3, 30, 'row3', GETDATE());
INSERT INTO two_way_test2 VALUES (4, 40, 'row4', GETDATE());
INSERT INTO two_way_test2 VALUES (5, 50, 'row5', GETDATE());
INSERT INTO two_way_test2 VALUES (6, 60, 'row6', GETDATE());
INSERT INTO two_way_test2 VALUES (7, 70, 'row7', GETDATE());
INSERT INTO two_way_test2 VALUES (8, 80, 'row8', GETDATE());
INSERT INTO two_way_test2 VALUES (9, 90, 'row9', GETDATE());
INSERT INTO two_way_test2 VALUES (10, 100, 'row10', GETDATE());
GO
-- Add the transactional publication and article in test1
USE test1;
GO
DECLARE @publication AS sysname;
DECLARE @article1 AS sysname;
DECLARE @article2 AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS nvarchar(512);
SET @publication = N'two_way_pub_test1';
SET @article1 = N'two_way_test1';
SET @article2 = N'two_way_test2';
SET @login = $(Login);
SET @password = $(Password);
EXEC sp_addlogreader_agent
@job_login = @login,
@job_password = @password,
@publisher_security_mode = 1;
EXEC sp_addpublication @publication = @publication,
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'publ1',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'true',
@immediate_sync = N'false',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false',
@retention = 60;
EXEC sp_addarticle @publication = @publication,
@article = @article1,
@source_owner = N'dbo',
@source_object = @article1,
@destination_table = @article2,
@type = N'logbased',
@creation_script = null,
@description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F1,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_ins_two_way_test2',
@del_cmd = N'XCALL sp_del_two_way_test2',
@upd_cmd = N'XCALL sp_upd_two_way_test2',
@filter = null,
@sync_object = null;
GO
-- Add the transactional publication and article in test2
USE test2
GO
DECLARE @publication AS sysname;
DECLARE @article1 AS sysname;
DECLARE @article2 AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS nvarchar(512);
SET @publication = N'two_way_pub_test2';
SET @article1 = N'two_way_test1';
SET @article2 = N'two_way_test2';
SET @login = $(Login);
SET @password = $(Password);
EXEC sp_addlogreader_agent
@job_login = @login,
@job_password = @password,
@publisher_security_mode = 1;
EXEC sp_addpublication @publication = @publication,
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'Pub2',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'true',
@immediate_sync = N'false',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false',
@retention = 60;
EXEC sp_addarticle @publication = @publication,
@article = @article2,
@source_owner = N'dbo',
@source_object = @article2,
@destination_table = @article1,
@type = N'logbased',
@creation_script = null,
@description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F1,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_ins_two_way_test1',
@del_cmd = N'XCALL sp_del_two_way_test1',
@upd_cmd = N'XCALL sp_upd_two_way_test1',
@filter = null,
@sync_object = null;
GO
-- Add the transactional subscription in test1
USE test1
GO
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscription_db AS sysname;
SET @publication = N'two_way_pub_test1';
SET @subscriber = $(SubServer2);
SET @subscription_db = N'test2';
EXEC sp_addsubscription @publication = @publication,
@article = N'all',
@subscriber = @subscriber,
@destination_db = @subscription_db,
@sync_type = N'none',
@status = N'active',
@update_mode = N'read only',
@loopback_detection = 'true';
EXEC sp_addpushsubscription_agent
@publication = @publication,
@subscriber = @subscriber,
@subscriber_db = @subscription_db,
@job_login = $(Login),
@job_password = $(Password);
GO
-- Add the transactional subscription in test2
USE test2
GO
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscription_db AS sysname;
SET @publication = N'two_way_pub_test2';
SET @subscriber = $(SubServer1);
SET @subscription_db = N'test1';
EXEC sp_addsubscription @publication = @publication,
@article = N'all',
@subscriber = @subscriber,
@destination_db = @subscription_db,
@sync_type = N'none',
@status = N'active',
@update_mode = N'read only',
@loopback_detection = 'true';
EXEC sp_addpushsubscription_agent
@publication = @publication,
@subscriber = @subscriber,
@subscriber_db = @subscription_db,
@job_login = $(Login),
@job_password = $(Password);
GO
-- Create custom stored procedures in test1
USE test1
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_ins_two_way_test1' and type = 'P')
DROP proc sp_ins_two_way_test1;
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_upd_two_way_test1' and type = 'P')
DROP proc sp_upd_two_way_test1;
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_del_two_way_test1' and type = 'P')
DROP proc sp_del_two_way_test1;
GO
-- Insert procedure
CREATE proc sp_ins_two_way_test1 @pkcol int,
@intcol int,
@charcol char(100),
@datecol datetime
AS
INSERT INTO two_way_test1 (pkcol, intcol, charcol,
datecol)
VALUES (@pkcol, @intcol, @charcol, GETDATE());
GO
-- Update procedure
CREATE proc sp_upd_two_way_test1 @old_pkcol int,
@old_intcol int,
@old_charcol char(100),
@old_datecol datetime,
@pkcol int, @intcol int,
@charcol char(100),
@datecol datetime
AS
-- IF intcol conflict is detected, add values
-- IF charcol conflict detected, concatenate values
DECLARE @curr_intcol int, @curr_charcol char(100);
SELECT @curr_intcol = intcol, @curr_charcol = charcol
FROM two_way_test1 WHERE pkcol = @pkcol;
IF @curr_intcol != @old_intcol
SELECT @intcol = @curr_intcol +
(@intcol - @old_intcol);
IF @curr_charcol != @old_charcol
SELECT @charcol = rtrim(@curr_charcol) +
'_' + rtrim(@charcol);
UPDATE two_way_test1 SET intcol = @intcol,
charcol = @charcol, datecol = GETDATE()
WHERE pkcol = @old_pkcol;
GO
-- Delete procedure
CREATE proc sp_del_two_way_test1 @old_pkcol int,
@old_intcol int,
@old_charcol char(100),
@old_datecol datetime
AS
DELETE two_way_test1 WHERE pkcol = @old_pkcol;
GO
-- Create custom stored procedures in test2
USE test2
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_ins_two_way_test2' and type = 'P')
DROP proc sp_ins_two_way_test2;
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_upd_two_way_test2' and type = 'P')
DROP proc sp_upd_two_way_test2;
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_del_two_way_test2' and type = 'P')
DROP proc sp_del_two_way_test2;
GO
-- Insert procedure
CREATE proc sp_ins_two_way_test2 @pkcol int,
@intcol int,
@charcol char(100),
@datecol datetime
AS
INSERT INTO two_way_test2 (pkcol, intcol, charcol,datecol)
VALUES (@pkcol, @intcol, @charcol, GETDATE());
GO
-- Update procedure
CREATE proc sp_upd_two_way_test2 @old_pkcol int,
@old_intcol int,
@old_charcol char(100),
@old_datecol datetime,
@pkcol int,
@intcol int,
@charcol char(100),
@datecol datetime
AS
-- IF intcol conflict is detected, add values
-- IF charcol conflict detected, concatenate values
DECLARE @curr_intcol int, @curr_charcol char(100);
SELECT @curr_intcol = intcol, @curr_charcol = charcol
FROM two_way_test2 WHERE pkcol = @pkcol;
IF @curr_intcol != @old_intcol
SELECT @intcol = @curr_intcol +
(@intcol - @old_intcol);
IF @curr_charcol != @old_charcol
SELECT @charcol = rtrim(@curr_charcol) +
'_' + rtrim(@charcol);
UPDATE two_way_test2 SET intcol = @intcol,
charcol = @charcol, datecol = GETDATE()
WHERE pkcol = @old_pkcol;
GO
-- Delete procedure
CREATE proc sp_del_two_way_test2 @old_pkcol int,
@old_intcol int,
@old_charcol char(100),
@old_datecol datetime
AS
DELETE two_way_test2 WHERE pkcol = @old_pkcol;
GO
-- Execute updates to the first row in test1 and test2
USE test1
GO
UPDATE two_way_test1 SET intcol = 20 , charcol = 'updated at test1' WHERE pkcol = 1;
USE test2
GO
UPDATE two_way_test2 SET intcol = 60 , charcol = 'updated at test2' WHERE pkcol = 1;
-- Select data from both tables to verify that the changes were propagated
SELECT * FROM test1..two_way_test1 WHERE pkcol = 1;
SELECT * FROM test2..two_way_test2 WHERE pkcol = 1;
GO