How To Implement Bidirectional Transactional Replication

This article has been archived. It is offered "as is" and will no longer be updated.
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:
      -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:
    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:
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.
  1. Create a distributor, publishers, and subscribers on a computer running SQL Server. To do so, follow these steps:
    1. Create two databases:
      use mastergocreate database test1gocreate database test2go
    2. Create two tables that have an IDENTITY column with the NOT FOR REPLICATION option set:
      use test1gocreate table two_way_test1 (	pkcol		INTEGER PRIMARY KEY NOT NULL,	intcol		INTEGER IDENTITY(1,1) NOT FOR REPLICATION, 	charcol		CHAR(100),	timestampcol 	TIMESTAMP)use test2gocreate table two_way_test2 (	pkcol 		INTEGER PRIMARY KEY NOT NULL,	intcol 		INTEGER IDENTITY(1000000000,1) NOT FOR REPLICATION, 	charcol 	CHAR(100), 	timestampcol 	TIMESTAMP)go
    3. 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 test1goalter table 	two_way_test1 with nocheck add constraint 	checkprimcol check NOT FOR REPLICATION 	(		pkcol BETWEEN 1 and 1000	) go use test2go	-- Constraint to enforce a range of values between 1001 and 2000 in the database test2 alter table 	two_way_test2with nocheck add constraint 	checkprimcol check NOT FOR REPLICATION 	(		pkcol BETWEEN 1001 and 2000	) go 
  2. Enable your server as the distributor, and then create a distribution database:
    use mastergosp_adddistributor @distributor = '<distributor name>' gouse mastergosp_adddistributiondb @database='distribution'go
  3. Enable all the computers running SQL Server that are participating in the replication as publishers:
    use mastergoexec sp_adddistpublisher 	@publisher = '<Your Server Name>', 	@distribution_db ='distribution', 	@security_mode = 0, 	@login = 'sa', 	@password = 'sa',	@working_directory ='<Location of Directory>'
  4. Enable all the identified databases for replication:
    use mastergoexec sp_replicationdboption N'test1', N'publish', truegoexec sp_replicationdboption N'test2', N'publish', truego
  5. 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:
    1. Create the custom stored procedures in the test1 database:
      use test1go-- INSERT Stored Procedurecreate procedure sp_ins_two_way_test1		@pkcol int, 	@intcol int, 	@charcol char(100), 	@timestampcol timestamp,	@rowidcol uniqueidentifieras	insert into two_way_test1 	(		pkcol,		intcol,		charcol	) 	values	(		@pkcol, 		@intcol, 		@charcol	)go--UPDATE Stored Procedurecreate procedure sp_upd_two_way_test1		@pkcol int,     	@intcol int,     	@charcol char(100),     	@timestampcol timestamp,	@rowidcol uniqueidentifier,    	@old_pkcol intas	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 Procedurecreate procedure sp_del_two_way_test1 	@old_pkcol intas	delete 		two_way_test1 	where 		pkcol = @old_pkcolgo
    2. Create the custom stored procedures in the test2 database:
      use test2go-- INSERT Stored Procedurecreate procedure sp_ins_two_way_test2		@pkcol int, 	@intcol int, 	@charcol char(100), 	@timestampcol timestamp,	@rowidcol uniqueidentifieras	insert into two_way_test2 	(		pkcol,		intcol,		charcol	) 	values	(		@pkcol, 		@intcol, 		@charcol	)go--UPDATE Stored Procedurecreate procedure sp_upd_two_way_test2		@pkcol int,     	@intcol int,     	@charcol char(100),     	@timestampcol timestamp,	@rowidcol uniqueidentifier,    	@old_pkcol intas	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 Procedurecreate procedure sp_del_two_way_test2 	@old_pkcol intas	delete 		two_way_test2 	where 		pkcol = @old_pkcolgo
  6. Create a transactional publication, and then add articles to the publication in both the test1 and the test2 databases:
    --In the database test1.use test1go--  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 = 72goexec 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 = 0go--  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 = nullgo--  In the database test2use test2go--  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 = 72goexec 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 = 0go--  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 = nullgo
  7. Enable as subscribers all the servers that participate in replication:
    exec sp_addsubscriber 	@subscriber = '<Your Server Name>', 	@login = '<login name>', 	@password = '<password>'go
  8. 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 test1goexec 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 test2goexec 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
Properties

Article ID: 820675 - Last Review: 12/08/2015 02:43:26 - Revision: 1.9

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

  • kbnosurvey kbarchive kbreplication kbcode kbtsql kbhowtomaster KB820675
Feedback