How To Implement Bidirectional Transactional Replication (820675)
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions)
- Microsoft SQL Server 2000 64 bit (all editions)
SUMMARYThis step-by-step article describes how to implement
bidirectional transactional replication. This article also discusses the issues
that are involved in implementing bidirectional transactional
replication. back to the
topBidirectional Transactional ReplicationBidirectional transactional replication, also known as two-way
transactional replication, permits a server to be both a publisher and a
subscriber to the same data. Because the servers that participate in the
replication will replicate any changes to the other servers, any changes are
not be propagated back to the originating server. For example, if you
have two servers (Server A and Server B), the servers are said to be in
bidirectional transactional replication if both of the following conditions are
true:
- The changes that are made to Table T1 at Server A are
replicated to Table T1 at Server B.
- The changes that are made to Table T1 at Server B are
replicated to Table T1 at Server A.
Therefore, if a change originates from Server A, the change is
replicated to Server B, but Server B does not propagate the same change back to
Server A. Replication uses a loopback detection mechanism that the distributor
uses to determine whether to send the changes back to originating
server. back to the topPlan the Topology for Bidirectional Transactional ReplicationFor bidirectional transactional replication, one of the servers
can act as central subscriber, and all the
other servers subscribe to the central subscriber. Therefore, any changes that
originate at a server are replicated to the central subscriber, and then the
central subscriber, in turn, replicates the changes to all the other servers
that participate in the replication. However, with the help of the loopback
detection mechanism, the distributor stops the change from being propagated to
the originating server. For example, if three servers (Server A,
Server B, and Server C) participate in bidirectional transactional replication
and Server A is the central subscriber, the publishers and subscribers are
maintained in the following ways:
- Server A publishes to Server B and Server C.
- Server A subscribes from Server B and Server C.
- Server B publishes to and subscribes from only Server
A.
- Server C publishes to and subscribes from only Server
A.
Therefore, any change that originates at Server B is replicated
to Server A and Server C. back to the
topConflicts in Bidirectional Transactional ReplicationWhen you make changes on a server that is participating in
replication, the changes are replicated to all other participating servers.
During this replication, conflicts may occur and replication may fail. The
following list describes the possible conflicts and the ways that you can avoid
these conflicts:
- If you insert a record that has a key into a table on one
of the servers and another record that has the same key already exists on the
other servers that participate in the replication, the replication does not
propagate the changes to the other servers.
Suggested
Action To avoid this problem, make sure that you use different keys on
each server that participates in the replication. To do so, allocate a
predetermined range of keys to each server that participates in the
replication. You can also use a composite key on each server. - When you update a record that has been deleted on another
server, the UPDATE statement affects zero rows on the server where the record
has been deleted, and the replication fails with an error.
Suggested Action To avoid this problem, perform one of the following steps:
- Remove the @@ROWCOUNT check after the actual UPDATE statement in the update custom
stored procedure.
-or- - Use the -Skiperrors parameter for the distribution agent to skip this error. For more
information about skipping errors in transactional replication, visit the
following Microsoft Web site:
-or- - Look for a record before the UPDATE statement in the
update stored procedure. If no record exists, bypass the UPDATE statement, and
the record is deleted on all the subscribers.
- When you update a column in a record that is updated at the
same time on another server, the data may be different on the two
servers.
Suggested Action To avoid this problem, determine if the data is being updated at
the same time on other servers, and then take any necessary action. To do so,
modify the update custom stored procedure and use XCALL syntax to call the
update stored procedure. The XCALL syntax provides the values for all the
columns before the update procedure is called and provides the updated values
in the column. You can compare the current value of the column against the
value before the update stored procedure is called. If you see different
values, the column is being updated at the same time by different servers. You
can customize the stored procedure to select which value persists. For more
information about how to use XCALL, visit the following Microsoft Web
site: Note You can specify the XCALL syntax to call the corresponding update
stored procedure or delete stored procedure by using sp_addarticle during publication. You can also specify the XCALL syntax by
using SQL Server Enterprise Manager. To do so, follow these steps:
- In SQL Server Enterprise Manager, locate the
publication that you want.
- Right-click the publication, and then click
Properties.
- Click the Articles tab, locate the
article, and then click the article properties button (...) next to the
article.
- In the Table Article Properties dialog
box, click the Commands tab.
- In the Replace UPDATE commands with this stored
procedure call text box, type XCALL, and then
click OK.
- In the Publication Properties dialog
box, click OK.
- When you update different columns in a record, simultaneous
updates of different columns of a record may sometimes lead to
conflicts.
Suggested Action To avoid this problem, determine if the different columns in the
same record are updated at the same time, and then take any necessary action.
To do so, modify the update custom stored procedure, and then use the XCALL
syntax to call the update stored procedure. Because the XCALL syntax provides
the values before the update stored procedure is called, you can add one of the
following options to the update stored procedure before the actual update is
performed:
- Compare the current values of all the columns against
their values before the update stored procedure is called.
- Add a column to represent the row version and to
compare its current value with its value before the update stored procedure is
called.
Different values indicate that different columns are being
updated at the same time. You can then decide whether to update the
column. - When you delete a row that is being updated by another
server at the same time, the replication may fail.
Suggested Action To determine if a row is updated and deleted at the same time,
use the XCALL syntax in the delete stored procedure. Compare every column of
the row that is being deleted against the values before the delete stored
procedure is called. Different values indicate that these updates are being
performed at the same time. You can either delete or retain the updated
row.
Note Even if you do not delete the record on the subscriber, the
record no longer exists on the server that originated the DELETE
statement. - When you delete a row that is being deleted at the same
time on another server that is participating in the replication, the
replication fails because the DELETE statement does not affect any rows on some
of the subscribers.
Suggested
Action To avoid this problem, perform one of the following steps:
- Remove the @@ROWCOUNT check after the actual DELETE statement in the update custom
stored procedure.
-or- - Use the -Skiperrors parameter at the distribution agent to skip this error. For more
information about skipping errors in transactional replication, visit the
following Microsoft Web site:
Note Each deployment may require a different approach to resolve these
conflicts, depending on business requirements. These conflicts are easier to
resolve when only two servers are involved. When more than two servers are
involved, you may be able to use stored procedures to determine which server
originated the changes. The update stored procedure that is used to update the
records in Server C does not know if the change originated in Server A or in
Server B. Unlike merge replication, transactional replication is not designed
to resolve conflicts. Deploy transactional replication only in scenarios where
conflicts can be avoided instead of resolved. back to the topImplement Bidirectional Transactional ReplicationTo implement bidirectional transactional replication, all the
following conditions must be true:
- The data is synchronized between the replicating
servers.
- Stored procedures that are used by replication are located
in all participating databases.
- The subscription was set up by using the @loopback_detection = 'true' parameter.
Note The option to set @loopback_detection = 'true' is not currently available in the user interface. Therefore, you
must subscribe by using the sp_addsubscription stored procedure.
If you perform a backup and a restore, remember that different
sites require different constraints on the primary key to make sure that
duplicate primary keys are not created. Also remember to create all constraints
with the NOT FOR REPLICATION clause. You can use the following
example to set up bidirectional transactional replication on a computer running
SQL Server 2000. Note Transact-SQL statements are listed for each of the following
steps. Run the Transact-SQL statements to perform the task that is mentioned in
the previous step.
- Create a distributor, publishers, and subscribers on a
computer running SQL Server. To do so, follow these steps:
- Create two databases:
use master
go
create database test1
go
create database test2
go
- Create two tables that have an IDENTITY column with the NOT FOR REPLICATION option set:
use test1
go
create table two_way_test1
(
pkcol INTEGER PRIMARY KEY NOT NULL,
intcol INTEGER IDENTITY(1,1) NOT FOR REPLICATION,
charcol CHAR(100),
timestampcol TIMESTAMP
)
use test2
go
create table two_way_test2
(
pkcol INTEGER PRIMARY KEY NOT NULL,
intcol INTEGER IDENTITY(1000000000,1) NOT FOR REPLICATION,
charcol CHAR(100),
timestampcol TIMESTAMP
)
go - Allocate a predetermined range of values to the primary
key column so that the values on the different servers are not in the same
range. For example, you can enforce 1-1000 as the key range for the two_way_test1 table in the test1 database, and then enforce 1001 -2000 as the key range for two_way_test2 table in the test2 database. To do so, use the following code:
-- Constraint to enforce a range of values between 1 and 1000 in database test1
use test1
go
alter table
two_way_test1
with nocheck
add constraint
checkprimcol check NOT FOR REPLICATION
(
pkcol BETWEEN 1 and 1000
)
go
use test2
go
-- Constraint to enforce a range of values between 1001 and 2000 in the database test2
alter table
two_way_test2
with nocheck
add constraint
checkprimcol check NOT FOR REPLICATION
(
pkcol BETWEEN 1001 and 2000
)
go
- Enable your server as the distributor, and then create a
distribution database:
use master
go
sp_adddistributor @distributor = '<distributor name>'
go
use master
go
sp_adddistributiondb @database='distribution'
go - Enable all the computers running SQL Server that are participating in the
replication as publishers:
use master
go
exec sp_adddistpublisher
@publisher = '<Your Server Name>',
@distribution_db ='distribution',
@security_mode = 0,
@login = 'sa',
@password = 'sa',
@working_directory ='<Location of Directory>' - Enable all the identified databases for replication:
use master
go
exec sp_replicationdboption N'test1', N'publish', true
go
exec sp_replicationdboption N'test2', N'publish', true
go
- Create custom stored procedures for INSERT, UPDATE, and
DELETE operations on all the databases to apply the changes that are made
during replication.
Note Typically, when you insert a value into an IDENTITY column, the
IDENTITY_INSERT option for the table must be ON. This task is achieved by the
NOT FOR REPLICATION option for incoming replication agents.
You
cannot update the values in the IDENTITY column. Therefore, when you update the
values during the replication, you have to remove the old values and insert the
new values. To create the custom stored procedures, follow these steps:
- Create the custom stored procedures in the test1 database:
use test1
go
-- INSERT Stored Procedure
create procedure sp_ins_two_way_test1
@pkcol int,
@intcol int,
@charcol char(100),
@timestampcol timestamp,
@rowidcol uniqueidentifier
as
insert into two_way_test1
(
pkcol,
intcol,
charcol
)
values
(
@pkcol,
@intcol,
@charcol
)
go
--UPDATE Stored Procedure
create procedure sp_upd_two_way_test1
@pkcol int,
@intcol int,
@charcol char(100),
@timestampcol timestamp,
@rowidcol uniqueidentifier,
@old_pkcol int
as
declare @x int
declare @y int
declare @z char(100)
select
@x=pkcol,
@y=intcol,
@z=charcol
from
two_way_test1
where
pkcol = @pkcol
delete
two_way_test1
where
pkcol=@pkcol
insert into two_way_test1
(
pkcol,
intcol,
charcol
)
values
(
case isnull(@pkcol,0) when 0 then @x else @pkcol end,
case isnull(@intcol,0) when 0 then @y else @intcol end,
case isnull(@charcol,'N') when 'N' then @z else @charcol end
)
go
-- DELETE Stored Procedure
create procedure sp_del_two_way_test1
@old_pkcol int
as
delete
two_way_test1
where
pkcol = @old_pkcol
go
- Create the custom stored procedures in the test2 database:
use test2
go
-- INSERT Stored Procedure
create procedure sp_ins_two_way_test2
@pkcol int,
@intcol int,
@charcol char(100),
@timestampcol timestamp,
@rowidcol uniqueidentifier
as
insert into two_way_test2
(
pkcol,
intcol,
charcol
)
values
(
@pkcol,
@intcol,
@charcol
)
go
--UPDATE Stored Procedure
create procedure sp_upd_two_way_test2
@pkcol int,
@intcol int,
@charcol char(100),
@timestampcol timestamp,
@rowidcol uniqueidentifier,
@old_pkcol int
as
declare @x int
declare @y int
declare @z char(100)
select
@x=pkcol,
@y=intcol,
@z=charcol
from
two_way_test2
where
pkcol = @pkcol
delete
two_way_test2
where
pkcol=@pkcol
insert into two_way_test2
(
pkcol,
intcol,
charcol
)
values
(
case isnull(@pkcol,0) when 0 then @x else @pkcol end,
case isnull(@intcol,0) when 0 then @y else @intcol end,
case isnull(@charcol,'N') when 'N' then @z else @charcol end
)
go
-- DELETE Stored Procedure
create procedure sp_del_two_way_test2
@old_pkcol int
as
delete
two_way_test2
where
pkcol = @old_pkcol
go
- Create a transactional publication, and then add articles
to the publication in both the test1 and the test2 databases:
--In the database test1.
use test1
go
-- Adding the transactional publication.
exec sp_addpublication
@publication = N'two_way_pub_test1',
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'Transactional publication of database test1.',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'false',
@immediate_sync = N'false',
@allow_sync_tran = N'true',
@autogen_sync_procs = N'true',
@retention = 72
go
exec sp_addpublication_snapshot
@publication = N'two_way_pub_test1',
@frequency_type = 4,
@frequency_interval = 1,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 1,
@frequency_subday = 1,
@frequency_subday_interval = 0,
@active_start_date = 0,
@active_end_date = 0,
@active_start_time_of_day = 233000,
@active_end_time_of_day = 0
go
-- Adding the transactional articles.
exec sp_addarticle
@publication = N'two_way_pub_test1',
@article = N'two_way_test1',
@source_owner = N'dbo',
@source_object = N'two_way_test1',
@destination_table = N'two_way_test1',
@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'CALL sp_del_two_way_test2',
@upd_cmd = N'CALL sp_upd_two_way_test2',
@filter = null,
@sync_object = null
go
-- In the database test2
use test2
go
-- Adding the transactional publication.
exec sp_addpublication
@publication = N'two_way_pub_test2',
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'Transactional publication of database test2',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'false',
@immediate_sync = N'false',
@allow_sync_tran = N'true',
@autogen_sync_procs = N'true',
@retention = 72
go
exec sp_addpublication_snapshot
@publication = N'two_way_pub_test2',
@frequency_type = 4,
@frequency_interval = 1,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 1,
@frequency_subday = 1,
@frequency_subday_interval = 0,
@active_start_date = 0,
@active_end_date = 0,
@active_start_time_of_day = 233000,
@active_end_time_of_day = 0
go
-- Adding the transactional articles.
exec sp_addarticle
@publication = N'two_way_pub_test2',
@article = N'two_way_test2',
@source_owner = N'dbo',
@source_object = N'two_way_test2',
@destination_table = N'two_way_test2',
@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'CALL sp_del_two_way_test1',
@upd_cmd = N'CALL sp_upd_two_way_test1',
@filter = null,
@sync_object = null
go
- Enable as subscribers all the servers that participate in
replication:
exec sp_addsubscriber
@subscriber = '<Your Server Name>',
@login = '<login name>',
@password = '<password>'
go
- Identify one of the databases as the central subscriber.
Create transactional subscriptions in all the databases that participate in the
replication so that all the databases subscribe to the central subscriber and
the central subscriber subscribes to all the other databases.
For
example, in this scenario, the test1 database is the central subscriber. Create transactional
subscriptions in the test2 database that subscribe to the publication at test1 and in the test1 database that subscribe to the publication at test2.
Note Create all the subscriptions with the LOOPBACK_DETECTION option
enabled.
To do so, use the following code:
--Adding the transactional subscription in test1.
use test1
go
exec sp_addsubscription
@publication = N'two_way_pub_test1',
@article = N'all',
@subscriber = '<Your Server Name>',
@destination_db = N'test2',
@sync_type = N'none',
@status = N'active',
@update_mode = N'sync tran',
@loopback_detection = 'true'
go
-- Adding the transactional subscription in test2.
use test2
go
exec sp_addsubscription
@publication = N'two_way_pub_test2',
@article = N'all',
@subscriber = '<Your Server Name>',
@destination_db = N'test1',
@sync_type = N'none',
@status = N'active',
@update_mode = N'sync tran',
@loopback_detection = 'true'
go
Note You can also create custom stored procedures for all publications
by using the sp_scriptpublicationcustomprocs system stored procedure. For more information about the sp_scriptpublicationcustomprocs system stored procedure, see the
"sp_scriptpublicationcustomprocs" topic in SQL Server 2000 Updated Books
Online. Note SQL Query Analyzer returns only 256 characters per column. You
can change this option to the maximum allowed value. back to the topREFERENCES For additional information, click the following article
numbers to view the articles in the Microsoft Knowledge Base: 320499
How To Manually Synchronize Replication Subscriptions by Using Backup or Restore
299903 FIX: sp_scriptpublicationcustomprocs Generates Replication Stored Procedures
327817 INF: Use the "-SkipErrors" Parameter in Distribution Agent Cautiously
For additional information about implementing bidirectional transactional
replication in SQL Server 7.0, click the following article numbers to view the articles in the Microsoft Knowledge Base:
300164
INF: How to Set Up an Identity Column on both the Publisher and the
Subscriber with Transactional Replication
240235 BUG: "Implementing Nonpartitioned, Bi-directional, Transactional Replication" Sample in Books Online Contains Errors
back to
the top
Modification Type: | Minor | Last Reviewed: | 7/15/2004 |
---|
Keywords: | kbReplication kbcode kbTSQL kbHOWTOmaster KB820675 kbAudDeveloper |
---|
|
|
©2004 Microsoft Corporation. All rights reserved.
|
|