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
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.
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 verify that data changes made at the Subscriber are propagated to the Publisher by authenticated users only.
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.
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
Occasional Maintenance for Online/Offline Applications
When planning for an online/offline application that uses replication, plan for occasional maintenance in the deployment of the application and for a way to transfer new datasets to the disconnected Subscribers.
Although SQL Server 2000 replication allows for rich data access for occasionally connected Subscribers, or for Subscribers using a slow link, there will still be a need to plan for occasional maintenance of the application and possibly for reapplying a snapshot at the Subscriber.