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).