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

Comments

• Anonymous
  January 09, 2013
  social.msdn.microsoft.com/.../950c6516-d4f3-4f5e-bf62-df45a1855231 I'm having trouble dumplicating your project. It's all explained at the above link. Thanks,

• Anonymous
  January 14, 2013
  It seems currently you are receiving the error below when running the merge agent: "Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server.The server was not found or was not accessible.  Verify that the instance name is correct and that SQL Server is configured to allow remote connections."    

1. In your case the publisher and subscriber SQL servers reside on the same machine, but different SQL instances. To see what protocols each instance is actually listening on, check the SQL Server errorlog for each instance. On startup, SQL will write to the errorlog what the server name is and what protocols it is listening on.
2. Replication is very picky when it comes to the server name. If you ever renamed the machine at some point, then it is possible the servername SQL has stored may not match the actual machine name. You may have: <OLDservername>yourinstancename          instead of <NEWservername>yourinstancename In this scenario, you'll see that you are able to make a connection to SQL using the new name (<NEWservername>yourinstancename) just fine, however, replication will fail, because replication actually checks the correctness of @@servername (see support.microsoft.com/.../818334) Please make sure the server name SQL has stored is up-to-date: --Run this within SQL management studio select @@servername --what is the servername returned? Is this the "correct" sql name? If @@servername is not correct, you'll need to update it using sp_dropserver/sp_addserver stored procedures: How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server msdn.microsoft.com/.../ms143799(v=sql.105).aspx
3. Also, note the sample code above is using an integrated connection to connect to SQL Server. Therefore, the windows account used to run the merge agent job (by default this is  is the  same as the windows account that runs the SQL Agent process)  needs to have permissions to log on to the publisher AND subscriber using windows authentication. Let me know if you still have issues after checking the above items. I can contact you directly if you provide your email address. -fcv

• Anonymous
  April 01, 2013
  Hi, I have gone through your sample, I have one question-   How to handle conflict for more that one table, if we have 100 table then should we have to run that script 100 times?

• Anonymous
  April 16, 2013
  Yes, you can build a more generic resolver and you would then have to register the same resolver for each article.