INF: Setting up Bidirectional Replication

• Microsoft SQL Server versions 6.0, 6.5

SUMMARY

This article details the steps to set up bidirectional transaction-based replication using a simple conflict resolution mechanism (also known as reconciliation), based on record ownership. It also discusses some situations that are handled by the custom stored procedures as well as recommendations on issues to be handled by the application.

MORE INFORMATION

Replication in SQL Server 6.x

Assumptions

1. The sample table is assumed to have the following structure:
   

   
         create table twoway
         (
         PKcol       int primary key not null,
         Data       int)
     

   You have to add a "replicate_flag" to indicate if a particular record needs be replicated or not. If this field is 1, any changes to this record will be replicated. Also, this flag will indicate ownership of the record -- if this flag is 1 on a server, it should be 0 on the other server. The server on which it is 1 is considered the "owner server" for the record, thereby establishing ownership (and consequently, ability to modify) at a record level. Any server that needs to modify this record needs to first obtain ownership of the record.



2. All application modifications will insert with the "replicate_flag" set to 1, or modify (update/delete) the record after obtaining ownership, by executing "update <table> set replicate_flag =1 where PKcol = <PK_value>" prior to executing the real update/delete command. For example, if a delete operation is to be done on a record, then, the replicate_flag must be updated to 1 before the actual delete command is executed on the record. This update must happen in the same transaction as the delete/update operation.



3. You have to use Custom Stored Procedures for Replication to handle insert/update/delete at the subscriber -- this procedure will always set "replicate_flag" to 0, thereby signifying that this is a replica of data owned by another server; it also avoids re-applying transactions or "looping" on replicated data. Often, an Update command is replicated as a Delete command followed by an Insert command - the procedures will be coded to handle cases where the delete part or insert part of an update command is masked out due to filtering. These procedures will also handle cases where the logreader generates an update command in the place of a delete/insert pair at the publisher. The custom procedures will have to be created on the subscriber -- assume the names of the procedures are:
   

   
         sp_twoway_i -- for insert
         sp_twoway_u -- for update
         sp_twoway_d -- for delete
     




4. No additional effort is required on a setup with empty tables at both ends during the setup; if the tables contain data prior to publishing, use manual synchronization. Synchronize the tables this way: data in the tables should contain replicate_flag=1; Data from one server should be BCPed into the other server with replicate_flag=0. This will establish the respective servers as "owners" of existing records.

Constraints

1. Each record in the table is owned by one server at any time. Custom Stored Procedures for Replication should be coded to insert/update/delete records at subscriber to automatically indicate the data is a replica. The application should insert with replicate_flag=1.



2. The application will take ownership of a record prior to modification. This should be done within the scope of the transaction doing the modification to eliminate the possibility of conflicts.



3. The application should handle unique primary key values for inserts by submitting the inserts to one server or have disjoint values on different servers. This will avoid conflicts on inserts -- otherwise, the distribution task may fail attempting to insert duplicate values or depending on the procedures, it may just update the information at the subscriber. This may be misinterpreted as missing data.



4. The replication mechanism should be nearly real-time. This will almost serialize operations and thereby reduce the possibility of a conflict; in cases where a conflict arises, we will reconcile using custom procedures. It is not suitable for servers replicating over a slow LAN or WAN network or over RAS, largely due to the possibility of delays in distribution.

Create the Procedures in the Subscribing Database

/* custom procedure for insert */ 

create proc sp_twoway_i @PKcol int, @Data int, @repl_flag smallint
as
begin
   set nocount on
   /* handle condition where delete part of update is filtered */ 
   if exists (select * from twoway where PKcol = @PKcol)
   begin
      update twoway
      set Data = @Data, replicate_flag=0
      where PKcol=@PKcol
   end
   else
   begin
      insert into twoway values (@PKcol,@Data,0)
   end
end
go

/* custom procedure for update */ 

create proc sp_twoway_u @PKcolNew int, @Data int,
@replicate_flag smallint, @PKcol int
as
begin
   set nocount on
   declare @flag smallint

   /* Update data only if this is subscribed data */ 
   /* replicate_flag will be 0 for subscribed data */ 
   if exists (select * from twoway where PKcol=@PKcol)
   begin
      select @flag = replicate_flag from twoway
      where PKcol=@PKcol
      if (@flag=0)
      begin
         update twoway
         set PKcol=@PKcolNew, Data=@Data,replicate_flag=0
         where PKcol=@PKcol
      end
   end
   else
   /* handle cases where delete logreader generates */ 
   /* update for delete/insert */ 
   begin
      insert twoway values (@PKcolNew,@Data,0)
   end
end
go

/* custom procedure for delete */ 

create proc sp_twoway_d @PKcol int
as
begin
   set nocount on
   /* Replication cannot update data owned by the site */ 
   /* data owned by the site will have replicate_flag = 1 */ 
   if exists (select * from twoway
      where PKcol = @PKcol and replicate_flag = 0)
   begin
      delete twoway where PKcol=@PKcol
   end
end
go 

1. from the Manage menu, choose Replication, and then from the drop-down menu that appears, choose Publications.



2. Select a database, and then click New. In the Edit Publications window, type a name, choose the table to be published and click Edit.



3. In the Manage Articles dialog box, specify the Restriction Clause as replicate_flag = 1 on the Filters tab.



4. On the Scripts tab, under Data Replication Mechanism, click Custom option for Insert/Update/Delete and specify the appropriate procedure name. For example, for insert, the entry similar to the following:
   

   
         call sp_twoway_i
    




5. Now, click Generate. In the Auto-Generate Sync Scripts dialog box, under If Existing Table Detected in Subscriber, select the Do Nothing option. This is required because the table will exist at the subscriber and will be published. On synchronization, the default Drop Table option will be unable to drop a published table.



6. To finish the setup, click Auto-Generate, click OK, and then click Add.

Additional query words: 2-way repl bi-directional bi-dir update anywhere

Keywords : kbsetup SSrvProg SSrvRep SSrvStProc
Version : Windows:6.5
Platform : WINDOWS
Issue type : kbhowto kbinfo