I'm trying to implement Merge Replication between just two servers, a publisher and one Subscriber.
Assume these tables :
--Main table in Publisher
Ticket (id, userId, reqId, blockedDate, usedDate, returnDate, transferDate, ...)
--On Subscriber (The fields are in Publication)
Ticket (id, userId, blockedDate, usedDate)
We must assign current date to transferDate
whenever a row goes to Subscriber, and assign to returnDate
whenever it comes back from Subscriber. As we HAVE to consider a file syncing (transfer/return data to/from subscriber with a USB stick!) these fields must be assigned on syncing either on file syncing or on replication.
I know you can add your custom business logic for Conflict Resolution (when a conflict occurs). I'm looking for something like triggers for replication events, which gain me the ability to manipulate some fields out of Publication (or even another table whenever needed).
Questions
-
Is it possible to handle replication events(push and pull) manually?**
(customize or override PULL/PUSH's. Not conflict resolver)** -
Can you give me a comparison between implementing a customized merge replication and syncing with help of a
distributed transaction
SP?
I need synchronization to be scheduled (at midnight when there's no load on DB server), BUT it is highly needed we'll be able to sync manually (from within our web application).
In second question I need their performance, reliability, and of course their maintainability. These factors especially the first two, are a big concern. Suppose Publisher may have about millions of records, and Subscriber may have about several ten thousand. So with each synchronization, about several ten thousand records will be transferred.
Merge replication checks rows; if there's a change and there's not a conflict, the row merges. if there's a conflict, a conflict resolver (could be your custom stored procedure resolver) handles the conflict. I want to write my own logic in the first case (data changed without conflict) instead of SQL's built-in merge mechanism. Clearly I want to customize it.
There's onlyid, userId, blockedDate, usedDate
columns in the article. I want to fill returnDate
whenever a Ticket
comes back to Publisher (on a pull) and fill transferDate
whenever a Ticket
goes to Subscriber (on a push).
Best Answer
To customize the merge process, you'll need to implement a custom business logic handler (requires .NET code - cannot be implemented using TSQL). See Execute Business Logic During Merge Synchronization in the documentation. It is available in SQL Server 2012 and later.
Override methods Update, Insert and Commit to transform changed rows, and conflict resolver method to modify conflict logic. Can't use T-SQL, must be done with .NET code. The main limitation is that each overridable method is called once for every row (like SSIS row transformations), to the best of my knowledge there isn't a method called on all changed rows.
There are some workarounds to handle your case, but this the general solution.
This is the BOL C# template: