Planning for Merge Replication

Merge replication requires planning in the following areas:

• timestamp columns.
  
  
• Identity ranges.
  
  
• Data integrity.
  
  
• Primary keys.
  
  
• Synchronizing with alternate synchronization partners.
  
  
• Row-level tracking and column-level tracking.
  
  
• Triggers and business rules.
  
  
• text and image data types in merge replication.
  
  
• Conflict resolution.
  
  
• Occassional maintenance for online/offline applications

timestamp Columns

Merge replication supports timestamp columns. The timestamp column is replicated, but the literal timestamp values are not. The timestamp values are regenerated when the initial snapshot rows are applied at the Subscriber. This allows timestamp values to be used by client applications at the Subscriber for functions such as optimistic concurrency control. In those cases, the ODBC driver, OLE DB provider, DB-Library cursor, or server cursor used to implement optimistic concurrency control compares the timestamp value of the row being updated with the current local value of the original row. If the timestamp values are different, indicating a row has changed, the application can take appropriate action (such as rolling back the transaction or rereading the data). Because the timestamp values are regenerated at the Subscriber, timestamp columns are filtered out when performing article validation.

Data Integrity

Because merge replication propagates changes made at the Subscriber, you must ensure that the application integrity is preserved at each Subscriber. All controls used to validate data changes at the Publisher should also be present at the Subscriber.

There are options to ensure that the login used by the Merge Agent to connect to the Publisher can also be used to control that only authenticated users can propagate data changes made at the Subscriber to the Publisher.

Foreign Keys

When creating a merge publication, specify the tables that are included as articles in that publication. If you include tables that contain foreign keys, the referenced table should also be included in the publication. If an attempt is made to add new rows to an article referencing a primary key in a missing table, the insert fails because SQL Server 2000 cannot find the required primary key. If an attempt is made to update data in an existing row(s) of the article, the update succeeds because SQL Server 2000 does not have to add a new row(s) and key(s).

After they are created, merge publications can be modified to include additional articles. You can add any missing, referenced tables to a publication if you discover that an article must be updated with additional rows and not just with modifications to existing rows. Use the publication properties dialog box to add the missing table.

Synchronizing with Alternate Synchronization Partners

Subscribers to merge publications can synchronize with servers other than the Publisher where the subscription originated. Synchronizing with alternate synchronization partners provides the ability for a Subscriber to synchronize data even if the primary Publisher is unavailable, or if you can connect to another synchronization partner because of physical location (for example, if you are visiting a remote office and can connect to an alternate synchronization partner there).

Determine whether it will be necessary for merge replication Subscribers to have alternate synchronization partners, and then prepare those alternate servers for the synchronization.

For more information, see Alternate Synchronization Partners.

Conflict Detection and Resolution

When determining merge replication conflict detection and resolution, you can specify whether you want the conflicts recognized at the row level or at the column level.

Whether to use row-level or column-level tracking should be decided based upon whether you want to consider any change within a row as a conflict (row-level tracking) or if different users will be allowed to update the same row simultaneously, but not the same column between synchronizations (column-level tracking).

The choice to use row-level versus column-level tracking should be based on your application and whether you want to consider any change to the same row in a table as a conflict or whether it is okay for different users to simultaneously update the same row, but not the same column, between synchronizations. For example, it might be considered acceptable in some applications that changes to different columns can be merged by using column-tracking. This means that if the Publisher changes column 1 and the Subscriber changes column 2, the merge process accepts the change to column 1 from the Publisher and change to column 2 from the Subscriber. Or some applications might require that changes to the same row at multiple sites (even if the values are in different columns) should be considered conflicts, detected and resolved at the row level.

For more information, see Merge Replication Conflict Detection and Resolution.

Triggers and Business Rules

You should be aware of all triggers and constraints on a table that is replicated. Without planning, the triggers and constraints can be replicated along with the table and can cause recurring conflicts during merge replication. For more information, see Publishing Data and Database Objects and Using NOT FOR REPLICATION.

text and image Data Types in Merge Replication

Merge replication supports the replication of text, ntext, and image columns only if they have been updated explicitly by an UPDATE statement because it causes a trigger to fire that updates meta data ensuring that the transaction gets propagated to other Subscribers.

Using only the WRITETEXT and UPDATETEXT operations will not propagate the change to other sites. If your application uses WRITETEXT and UPDATETEXT to update the text or ntext columns, explicitly add a dummy UPDATE statement after the WRITETEXT or UPDATETEXT operations, within the same transaction, to fire the trigger and thereby guarantee that the change will be propagated to other sites.

Example

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
DECLARE @mytextptr varbinary(16)
SELECT @mytextptr = textptr(Notes) 
FROM Employees 
WHERE EmployeeID = '7'
   IF @mytextptr IS NOT NULL 
BEGIN
UPDATETEXT Employees.Notes @mytextptr 0 NULL 'Terrific job this review period.'
-- Dummy update to fire trigger that will update meta data and ensure the update gets propagated to other Subscribers.
UPDATE Employees 
-- Set value equal to itself.
SET Notes = Notes
WHERE EmployeeID = '7' 
END
COMMIT TRAN 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED