Merge Replication customize synchronization cycle and its performance

Question

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

Answer 1

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:

using System;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.SqlServer.Replication.BusinessLogicSupport;
using Microsoft.Samples.SqlServer.BusinessLogicHandler;

namespace Microsoft.Samples.SqlServer.BusinessLogicHandler
{
public class OrderEntryBusinessLogicHandler :
  Microsoft.SqlServer.Replication.BusinessLogicSupport.BusinessLogicModule
{
    // Variables to hold server names.
    private string publisherName;
    private string subscriberName;

    public OrderEntryBusinessLogicHandler()
    {
    }

    // Implement the Initialize method to get publication 
    // and subscription information.
    public override void Initialize(
        string publisher,
        string subscriber,
        string distributor,
        string publisherDB,
        string subscriberDB,
        string articleName)
    {
        // Set the Publisher and Subscriber names.
        publisherName = publisher;
        subscriberName = subscriber;
    }

    // Declare what types of row changes, conflicts, or errors to handle.
    override public ChangeStates HandledChangeStates
    {
        get
        {
            // Handle Subscriber inserts, updates and deletes.
            return ChangeStates.SubscriberInserts |
              ChangeStates.SubscriberUpdates | ChangeStates.SubscriberDeletes;
        }
    }

    public override ActionOnDataChange InsertHandler(SourceIdentifier insertSource,
      DataSet insertedDataSet, ref DataSet customDataSet, ref int historyLogLevel,
      ref string historyLogMessage)
    {
        // custom logic for updates                 
    }

    public override ActionOnDataChange UpdateHandler(SourceIdentifier updateSource,
      DataSet updatedDataSet, ref DataSet customDataSet, ref int historyLogLevel,
      ref string historyLogMessage)
    {
 // custom logic for updates 
  }
    public override ActionOnDataDelete DeleteHandler(SourceIdentifier deleteSource,
      DataSet deletedDataSet, ref int historyLogLevel, ref string historyLogMessage)
    {
     // custom logic for deletes 
}
}