Knowledge Base

How To Implement Bidirectional Transactional Replication

Article ID: 820675

Article Last Modified on 11/26/2007

APPLIES TO

Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2000 64-bit Edition

IN THIS TASK

SUMMARY
REFERENCES

SUMMARY

This 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 top

Bidirectional Transactional Replication

Bidirectional 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 top

Plan the Topology for Bidirectional Transactional Replication

For 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 top

Conflicts in Bidirectional Transactional Replication

When 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:
  
  http://msdn2.microsoft.com/en-us/library/aa178842(SQL.80).aspx
  
  -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:

http://msdn2.microsoft.com/en-us/library/Aa237133(SQL.80).aspx

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:
1. In SQL Server Enterprise Manager, locate the publication that you want.
2. Right-click the publication, and then click Properties.
3. Click the Articles tab, locate the article, and then click the article properties button (...) next to the article.
4. In the Table Article Properties dialog box, click the Commands tab.
5. In the Replace UPDATE commands with this stored procedure call text box, type XCALL, and then click OK.
6. 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:
  
  http://msdn2.microsoft.com/en-us/library/aa178842(SQL.80).aspx

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 top

Implement Bidirectional Transactional Replication

To 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 top

REFERENCES

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

Keywords: kbhowtomaster kbtsql kbreplication kbcode KB820675

Microsoft KB Archive/820675

Contents