Export (0) Print
Expand All

How to: Implement a Business Logic Handler for a Merge Article (Replication Programming)

The Microsoft.SqlServer.Replication.BusinessLogicSupport namespace implements an interface that enables you to write complex business logic to handle events that occur during the merge replication synchronization process. Methods in the business logic handler can be invoked by the replication process for each changed row that is replicated during synchronization.

The general process for implementing a business logic handler is:

  1. Create the business logic hander assembly.
  2. Register the assembly at the Distributor.
  3. Deploy the assembly at the server on which the Merge Agent runs. For a pull subscription the agent runs on the Subscriber, and for a push subscription the agent runs on the Distributor. When you are using Web synchronization, the agent runs on the Web server.
  4. Create an article that uses the business logic handler or modify an existing article to use the business logic handler.

The business logic handler you specify is executed for every row that is synchronized. Complex logic and calls to other applications or network services can impact performance. For more information about business logic handlers, see Executing Business Logic During Merge Synchronization.

  1. In Microsoft Visual Studio, create a new project for the .NET assembly that contains the code that implements the business logic handler.

  2. Add references to the project for the following namespaces.

    Assembly Reference Location

    Microsoft.SqlServer.Replication.BusinessLogicSupport

    C:\Program Files\Microsoft SQL Server\90\COM (default installation)

    System.Data

    GAC (component of the .NET Framework)

    System.Data.Common

    GAC (component of the .NET Framework)

  3. Add a class that overrides the BusinessLogicModule class.

  4. Implement the HandledChangeStates property to indicate the types of changes that are handled.

  5. Override one or more of the following methods of the BusinessLogicModule class:

    • CommitHandler - invoked when a data change is committed during synchronization.
    • DeleteErrorHandler - invoked when an error occurs when a DELETE statement is being uploaded or downloaded.
    • DeleteHandler - invoked when DELETE statements are being uploaded or downloaded.
    • InsertErrorHandler - invoked when an error occurs when an INSERT statement is being uploaded or downloaded.
    • InsertHandler - invoked when INSERT statements are being uploaded or downloaded.
    • UpdateConflictsHandler - invoked when conflicting UPDATE statements occur at the Publisher and Subscriber.
    • UpdateDeleteConflictHandler - invoked when UPDATE statements conflict with DELETE statements at the Publisher and Subscriber.
    • UpdateErrorHandler - invoked when an error occurs when an UPDATE statement is being uploaded or downloaded.
    • UpdateHandler - invoked when UPDATE statements are being uploaded or downloaded.
  6. Build the project to create the business logic handler assembly.

  7. Deploy the assembly in the directory that contains the Merge Agent executable file (replmerg.exe), which for a default installation is C:\Program Files\Microsoft SQL Server\90\COM, or install it in the .NET global assembly cache (GAC). You should only install the assembly in the GAC if applications other than the Merge Agent require access to the assembly. The assembly can be installed into the GAC using the Global Assembly Cache tool (Gacutil.exe) provided in the .NET Framework SDK.

    ms147911.note(en-US,SQL.90).gifNote:
    A business logic handler must be deployed on every server on which the Merge Agent runs, which includes the IIS server that hosts the replisapi.dll when using Web synchronization.

  1. At the Publisher, execute sp_enumcustomresolvers (Transact-SQL) to verify that the assembly has not already been registered as a business logic handler.

  2. At the Distributor, execute sp_registercustomresolver (Transact-SQL), specifying a friendly name for the business logic handler for @article_resolver, a value of true for @is_dotnet_assembly, the name of the assembly for @dotnet_assembly_name, and the fully-qualified name of the class that overrides BusinessLogicModule for @dotnet_class_name.

    ms147911.note(en-US,SQL.90).gifNote:
    If the assembly is not deployed in the same directory as the Merge Agent executable, in the same directory as the application that synchronously starts the Merge Agent, or in the global assembly cache (GAC), you need to specify the full path with the assembly name for @dotnet_assembly_name. When using Web synchronization, you must specify the location of assembly at the Web server.

  1. Execute sp_addmergearticle (Transact-SQL) to define an article, specifying the friendly name of the business logic handler for @article_resolver. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

  1. Execute sp_changemergearticle (Transact-SQL), specifying @publication, @article, a value of article_resolver for @property, and the friendly name of the business logic handler for @value.

This example shows a business logic handler that creates an audit log.

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)
		{
			if (insertSource == SourceIdentifier.SourceIsSubscriber)
			{
				// Build a line item in the audit message to log the Subscriber insert.
				StringBuilder AuditMessage = new StringBuilder();
				AuditMessage.Append(String.Format("A new order was entered at {0}. " +
				  "The SalesOrderID for the order is :", subscriberName));
				AuditMessage.Append(insertedDataSet.Tables[0].Rows[0]["SalesOrderID"].ToString());
				AuditMessage.Append("The order must be shipped by :");
				AuditMessage.Append(insertedDataSet.Tables[0].Rows[0]["DueDate"].ToString());

				// Set the reference parameter to write the line to the log file.
				historyLogMessage = AuditMessage.ToString();
				
				// Set the history log level to the default verbose level.
				historyLogLevel = 1;

				// Accept the inserted data in the Subscriber's data set and 
				// apply it to the Publisher.
				return ActionOnDataChange.AcceptData;
			}
			else
			{
				return base.InsertHandler(insertSource, insertedDataSet, ref customDataSet,
				  ref historyLogLevel, ref historyLogMessage);
			}
		}

		public override ActionOnDataChange UpdateHandler(SourceIdentifier updateSource,
		  DataSet updatedDataSet, ref DataSet customDataSet, ref int historyLogLevel,
		  ref string historyLogMessage)
		{
			if (updateSource == SourceIdentifier.SourceIsPublisher)
			{
				// Build a line item in the audit message to log the Subscriber update.
				StringBuilder AuditMessage = new StringBuilder();
				AuditMessage.Append(String.Format("An existing order was updated at {0}. " +
				  "The SalesOrderID for the order is ", subscriberName));
				AuditMessage.Append(updatedDataSet.Tables[0].Rows[0]["SalesOrderID"].ToString());
				AuditMessage.Append("The order must now be shipped by :");
				AuditMessage.Append(updatedDataSet.Tables[0].Rows[0]["DueDate"].ToString());

				// Set the reference parameter to write the line to the log file.
				historyLogMessage = AuditMessage.ToString();
				// Set the history log level to the default verbose level.
				historyLogLevel = 1;

				// Accept the updated data in the Subscriber's data set and apply it to the Publisher.
				return ActionOnDataChange.AcceptData;
			}
			else
			{
				return base.UpdateHandler(updateSource, updatedDataSet,
				  ref customDataSet, ref historyLogLevel, ref historyLogMessage);
			}
		}

		public override ActionOnDataDelete DeleteHandler(SourceIdentifier deleteSource,
		  DataSet deletedDataSet, ref int historyLogLevel, ref string historyLogMessage)
		{
			if (deleteSource == SourceIdentifier.SourceIsSubscriber)
			{
				// Build a line item in the audit message to log the Subscriber deletes.
				// Note that the rowguid is the only information that is 
				// available in the dataset.
				StringBuilder AuditMessage = new StringBuilder();
				AuditMessage.Append(String.Format("An existing order was deleted at {0}. " +
				  "The rowguid for the order is ", subscriberName));
				AuditMessage.Append(deletedDataSet.Tables[0].Rows[0]["rowguid"].ToString());

				// Set the reference parameter to write the line to the log file.
				historyLogMessage = AuditMessage.ToString();
				// Set the history log level to the default verbose level.
				historyLogLevel = 1;

				// Accept the delete and apply it to the Publisher.
				return ActionOnDataDelete.AcceptDelete;
			}
			else
			{
				return base.DeleteHandler(deleteSource, deletedDataSet,
				  ref historyLogLevel, ref historyLogMessage);
			}
		}
	}
}

The following example registers a business logic handler assembly at the Distributor and changes an existing merge article to use this custom business logic.

DECLARE @publication AS sysname;
DECLARE @article AS sysname;
DECLARE @friendlyname AS sysname;
DECLARE @assembly AS nvarchar(500);
DECLARE @class AS sysname;
SET @publication = N'AdvWorksCustomers';
SET @article = N'Customers';
SET @friendlyname = N'OrderEntryLogic';
SET @assembly = N'C:\Program Files\Microsoft SQL Server\90\COM\CustomLogic.dll';
SET @class = N'Microsoft.Samples.SqlServer.BusinessLogicHandler.OrderEntryBusinessLogicHandler';

-- Register the business logic handler at the Distributor.
EXEC sys.sp_registercustomresolver 
	@article_resolver = @friendlyname,
	@resolver_clsid = NULL,
	@is_dotnet_assembly = N'true',
	@dotnet_assembly_name = @assembly,
	@dotnet_class_name = @class;

-- Add an article that uses the business logic handler
-- at the Publisher.
EXEC sp_changemergearticle 
	@publication = @publication, 
	@article = @article,
	@property = N'article_resolver', 
	@value = @friendlyname,
	@force_invalidate_snapshot = 0,
	@force_reinit_subscription = 0;
GO

Community Additions

ADD
Show:
© 2014 Microsoft