Merge Replication Conflict Detection vs Conflict Resolution Part 2: Custom Business Logic Handler

Attached is a sample Merge Conflict Handler sample I created. I am including a walkthrough of the sample below.

1.  Create the sample merge publication/subscription databases

Code Snippet: From file <1-CreatePub_Sub_DBs.sql>

2. Via management studio or TSQL scripts ( file 2-CreatePublication.sql) , create the merge publication

· For Publication name specify: SamplePub1

· Publish both tables: t1, t2

· For each article's Tracking Level specify to use "Column-Level tracking" (@column_tracking=true)

3. Via management studio or TSQL scripts ( file 3-CreateSubscription.sql), create a merge push subscription

 

4. Run the Snapshot Agent

    Then run the Merge Agent to apply the initial snapshot

 

5. Create the Merge Conflict Handler on a machine with Visual Studio. To do this we will create the C_t1ConflictHandler class. Create a new Visual Studio C# Windows Class Library project, named Sample_t1MergeConflictHandler_Lib

a. Add the following references to the project:

 

C:\Program Files\Microsoft SQL Server\100\COM\Microsoft.SqlServer.Replication.BusinessLogicSupport.dll

 

Note: RMO depends on the replication components and client connectivity components that are included with all versions of SQL Server except SQL Server Compact. To deploy an application based on RMO, you must install a version of SQL Server that includes replication components and client connectivity components on the computer on which the application will run. https://msdn.microsoft.com/en-us/library/ms146869.aspx

b. (optional) Rename Class1.cs to Sample_t1MergeConflictHandler_Lib.cs

c. In the class source code file (Sample_t1MergeConflictHandler_Lib.cs), add the necessary using statements to include the proper libraries.

Code Snippet: from file <5-Sample_t1MergeConflictHandler_Lib.cs>

d. This class is defined as follows (from Sample_t1MergeConflictHandler_Lib.cs), I will describe in sections

Code Section 1

· Note the class inherits from the BusinessLogicModule class. This is necessary in order to implement the custom business logic that is invoked during the merge replication synchronization process.

· We override the HandledChangedStates method to specify what type of activity our Business Logic Handler will address. In this case we are specifying we will handle update conflicts only.

Reference: <https://technet.microsoft.com/en-us/library/microsoft.sqlserver.replication.businesslogicsupport.businesslogicmodule(SQL.100).aspx>

 

Code Section 2

· The Populate_ConflictRow_VersionInfo function, makes a direct connection to the publisher and subscriber (lines 41-48)

· Calls sp_showrowreplicaninfo for the rowguid provided (lines 52-54,lines 66-68)

· The stored procedure sp_showrowreplicaninfo returns TWO resultsets, the first contains row version information

· The second resultset contains column version information for each column in the row

· The two publisher resultsets are place in PubReplicaDataSet (line 55)

· The two subscriber resultsets are place in SubReplicaDataSet (line 69)

· The publisher row version is extracted from the first resultset and written to a variable pVer (line 62)

· The subscriber row version is extracted from the first resultset and written to a variable sVer (line 77)

· The resultsets are returned by reference

 

Code Section 3

· We are also overriding the method UpdateConflictsHandler to provide our own logic to handle an update conflict

· When the conflict is raised, we will have access to the publisher and subscriber row via the publisherDataSet and subscriberDataSet parameters

· We will build the final row to send to the publisher + subscriber by setting the column values we want in the customDataSet

· We have the ability to set a custom conflict message and history message

· By setting the conflictLogType, we can specify if a conflict error is raised during sync. Note what you set this value to also affects the output seen in the conflict tables.

· In line 105 we prepare the customDataSet object to initially hold the column values in the publisher row

· We then call Populate_ConflictRow_VersionInfo to populate the replica datasets with the output of sp_showrowreplicainfo

Code Section 4

· We then loop through all the columns in the conflict row to see which columns were modified at the publisher or subscriber - or both. If a column was changed it will contain replica output in sp_showrowreplicainfo (lines 137-139, 147-149)

· If a column was changed at both the publisher and subscriber, the logic below will choose the publisher row (lines 167-168)

Code Section 5

· Set logging options

· If we set "conflictLogType = ConflictLogType.ConflictLogNone" thenmerge agent sync status will not indicate a conflict has occurred

· If we set " conflictLogType = ConflictLogType.ConflictLogSubscriber" then the subriber row is marked as the losing row - which means in the conflict tables it will indicate the publisher row won - when in reality the handler build a custom row

· I have set it to ConflictLogSubscriber to receive an indication a conflict was raised in the sync status

6. Build the project above and copy the generated DLL to your sql server machine to install the Business Logic Handler (6-InstallBusinessLogicHandler.sql)

· Note the script below registers the resolver at the distributor and then sets the article property to use the resolver

· Note, I am also setting verify_resolver_signature=0 because for this specific sample I am not verifying the dll signature which is a potential security violation

7. Step 7: Now reproduce a conflict (file 7-ReproduceConflict.sql) for the table t1 which has a custom business logic handler ,and table t2 which does not. Observe the difference in the final results:

· Initial row values:

· Now update columns c2,c3 on the publisher.

· For the same row, update columns c3,c4 on the subscriber.

· Even with column-level tracking this is a conflict because c3 is changed at both the publisher and subscriber

 

· Now run the merge agent

· Note, the final row on the t1 table (which has a custom handler) includes values from the publisher and subscriber changes, note c2,c3 contains the publisher changed value, c4 contained the subscriber provided value

 

· Note, the final row on the t2 table includes values from the publisher only, note c2,c3 contains the publisher changed value, c4 contains the previous publisher value as well, all subscriber changes are lost for this row (default handler behavior)

Sample Download Files: 

SampleMergeHandler.zip

- Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services