INF: Setting up Bidirectional Replication

• Microsoft SQL Server, versions 6.0 and 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

Replication in SQL Server 6.x is intended to distribute logically read-only copies of data to multiple servers to offload report processing load out of an online transaction processing (OLTP) server. SQL Server replication also assumes that the data at the subscriber is always in sync with the data at the publisher. This constrains the data that is obtained as a result of subscribing to a publication to be static at the subscriber. SQL Server replication is intended to satisfy this class of applications that are designed for near real-time, static data at the subscriber.

Another class of applications that can use SQL Server replication is the one that depends on partitioned data. In this case, the servers own a disjointed set of data and each server replicates the data it owns to the other server. Effectively, the tables are published with a restriction clause to achieve horizontal partitioning and do not update data that is owned by the other server. This partitioning scheme implements two completely disjointed replication streams, thereby enhancing data availability.

However, some applications may require completely distributed data. Though SQL Server replication is not intended for such an update anywhere scenario, it provides the basic framework for implementing a two-way or bidirectional replication setup, using custom stored procedures and filter procedures. However, there are some constraints on the schema and the transactions. It relies on the procedure logic or manual intervention to detect and resolve conflicts. This setup using bidirectional replication is intended to be used only with applications that yield themselves well to these constraints.

Though this article uses an sample setup of bidirectional replication between two servers, it can easily be expanded to multiple servers. Also, the concepts discussed here apply to both SQL Server 6.0 and 6.5, though the features available are limited in SQL Server 6.0. For example, text and image columns cannot participate in transaction-based replication. Hence, appropriate modifications need to be done to the publication definition or custom stored procedures to handle the different cases.

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

These constraints are based on the assumptions in the previous section.

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 custom stored procedures on both the servers involved. The following sample uses a simple reconciliation logic. The application can do data modification only on owned records (replicate_flag=1) or it will take ownership of the record prior to modification. Custom procedures will not update or delete data that is not a replica (replicate_flag=0). In such cases, update and delete commands will be ignored.

Create the Procedures in the Subscribing Database

/* custom procedure for insert */

   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

/* custom procedure for update */

   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

/* custom procedure for delete */

   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

Now that the table and the custom procedures have been created, set up replication on the table using a horizontal partition and select the "Do Nothing" option on the Auto-Generate Sync Scripts dialog box using the following steps:

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.

   replicate_flag = 1 on the Filters tab.
   

   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
   

   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.
   

The above steps must be performed on both servers. This will create the publications on both servers. From each server, subscribe to the other server's publication. If the tables do not contain any data, you may choose either the Data Synchronization Automatically Applied or the No Data Synchronization option in the Subscription Options dialog box. If the tables will contain data prior to publishing, on each server, the replication flag must be set to 1 and the primary key values must be unique. In this case, choose the Data Synchronization Manually Applied by Operator option and synchronize the tables as explained previously.

This will complete the setup of bidirectional replication with a very simple reconciliation scheme. This sample procedure should be modified to include any logic your specific application/design may require.