THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Hilary Cotter

Merge Replication Stored Procedure Conflict Resolver

A conflict in merge replication occurs when applying a replicated command to the subscriber and:

there is a constraint violation

Foreign Key Violation

Primary Key Violation

there is an error modifying data

Data domain violation

Missing dependencies (trigger firing referencing missing objects)

Security violation (the account the replication agent runs under does not have rights to apply the replicated command on the publisher/subscriber).

You are updating a row which does not exist on the other side of your replication topology

You are deleting a row which does not exist on the other side of the replication topology

You are updating the column on the same row on both sides of a replication topology and are using column level tracking

You are updating the same row on both sides of a replication topology and are using row level tracking.

SQL Server provides a rich set of resolvers to resolve the conflict. You can see the list by issuing a call to:

sp_enumcustomresolvers

On a machine with no COM or Business Logic Resolvers will return the below list.

Microsoft SQL Server Additive Conflict Resolver

Microsoft SQL Server Averaging Conflict Resolver

Microsoft SQL Server DATETIME (Earlier Wins) Conflict Resolver

Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver

Microsoft SQL Server Download Only Conflict Resolver

Microsoft SQL Server Maximum Conflict Resolver

Microsoft SQL Server Merge Text Columns Conflict Resolver

Microsoft SQL Server Minimum Conflict Resolver

Microsoft SQL Server Priority Column Resolver

Microsoft SQL Server Subscriber Always Wins Conflict Resolver

Microsoft SQL Server Upload Only Conflict Resolver

Microsoft SQLServer Stored Procedure Resolver

However in some cases you wish to extend the way a conflict is handled by using a stored procedure resolver. A stored procedure resolver allows you to fire a custom stored procedure when a conflict occurs. The custom stored procedure resolver can handle a conflict arising from a single article or multiple articles.

There is some overlap between what a Business Logic Resolver can do for you over a Stored Procedure Resolver. Here is a breakdown of what the differences between a Businesses Logic Resolver can handle vs a Stored Procedure Resolver.

Feature

Business Logic Resolver

Stored Procedure Resolver

Development Language

Any .Net Language

TSQL

Can be modified on the fly

Yes, but will require dll recompilation

Yes

Speed

Slow

Faster than BLR.

Can Handle the following Replication Events

Conflicts, Errors, DML

Conflicts

Can detect which column has changed?

No

Yes

Debugging

Somewhat complicated but allows step by step processing

Can be done by logging.

The parameters you must pass to your stored procedure resolver are:

Parameter

Data Type

Description

@tableowner

sysname

Name of the owner of the table for which a conflict is being resolved. This is the owner for the table in the publication database.

@tablename

Sysname

Name of the table for which a conflict is being resolved.

@rowguid

Uniqueidentifier

Unique identifier for the row having the conflict.

@subscriber

sysname

Name of the server from where a conflicting change is being propagated.

@subscriber_db

sysname

Name of the database from where conflicting change is being propagated.

@log_conflict OUTPUT

int

Whether the merge process should log a conflict for later resolution:

@conflict_message OUTPUT

nvarchar(512)

Message Logged in Conflict Tables

@destowner

Sysname

The owner of the published table at the Subscriber.

Table above snagged from ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10rp_1devconc/html/76bd8524-ebc1-4d80-b5a2-4169944d6ac0.htm

Here is an example of how this works.

use Master
GO
set nocount on
GO
--need to access cmdshell to create a directory called c:\temp
--on most modern os's the program files directory is locked down
--and you will be unable to create a snapshot in the default folder
exec sp_configure 'show advanced options',1
reconfigure with override
exec sp_configure 'xp_cmdshell',1
reconfigure with override
exec xp_cmdshell 'md c:\temp'
GO
if exists(select *from sys.sysdatabases where name='StoredProcedureResolverPub')
begin
exec sp_replicationdboption 'StoredProcedureResolverPub','publish', false
exec sp_replicationdboption 'StoredProcedureResolverPub','merge publish', false
alter database StoredProcedureResolverPub set single_user with rollback immediate
drop database StoredProcedureResolverPub
end
Create Database StoredProcedureResolverPub
GO
exec sp_replicationdboption 'StoredProcedureResolverPub','merge publish', true
GO
if exists(select *from sys.sysdatabases where name='StoredProcedureResolverSub1')
begin
exec sp_replicationdboption 'StoredProcedureResolverSub1','publish', false
exec sp_replicationdboption 'StoredProcedureResolverSub1','merge publish', false
alter database StoredProcedureResolverSub1 set single_user with rollback immediate
drop database StoredProcedureResolverSub1
end
GO
Create Database StoredProcedureResolverSub1
GO
if exists(select *from sys.sysdatabases where name='StoredProcedureResolverSub2')
begin
exec sp_replicationdboption 'StoredProcedureResolverSub2','publish', false
exec sp_replicationdboption 'StoredProcedureResolverSub2','merge publish', false
alter database StoredProcedureResolverSub2 set single_user with rollback immediate
drop database StoredProcedureResolverSub2
end
Create database StoredProcedureResolverSub2
use StoredProcedureResolverPub
GO
Create Table table1(PK int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 charcol varchar(40) NULL,
 originating_db varchar(40) NULL DEFAULT ('StoredProcedureResolverPub') ,
 rowguid uniqueidentifier ROWGUIDCOL 
 NOT NULL UNIQUE CONSTRAINT table1_rowguid_Constraint DEFAULT (newsequentialid()) )
GO
declare @counter int=1
while @counter<1000
begin
insert into table1( charcol) values('test')
select @counter=@counter+1
end
GO
--a log table which records when the stored procedure resolver was used.
Create table LogTable(PK int identity primary key, dtstamp datetime)
GO
--here is our stored procedure conflict resolver
CREATE PROC MyConflictResolver(@tableowner sysname, @tablename Sysname, @rowguid Uniqueidentifier, @subscriber sysname,  
@subscriber_db sysname, @log_conflict int  OUTPUT, @conflict_message nvarchar(512)  OUTPUT, @destowner Sysname)
AS
set nocount on

set @log_conflict =1--if set to NULL or 0 no conflicts are logged
--just for kicks I am going to update a column from a row with the pk value of 11 on the publisher.
--you will note that this change does not get replicated.
--this causes a lack of consistency between your publisher and subscriber
--this is generally not considered to be a good thing
--but something to tuck away into your little gray cells
--in case you ever have a req like this
update table1 set originating_db='from resolver' where pk=11

SELECT @conflict_message ='The same row was conflicted at both '+@@ServerName +'.'+db_name()+' and '+@subscriber+'.'+
@subscriber_db+'. The resolver chose the update from '++@@ServerName +'.'+db_name()+' as the winner.'

INSERT INTO LogTable(dtstamp) VALUES(getdate())

SELECT PK, 'From Stored Procedure Conflict Resolver',
originating_db, rowguid FROM table1  where rowguid=@rowguid
GO
exec sp_addmergepublication StoredProcedureResolver, @snapshot_in_defaultfolder=false,
@alt_snapshot_folder='c:\temp', @publication_compatibility_level='100RTM',  @conflict_logging =both
GO
exec sp_addpublication_snapshot StoredProcedureResolver
GO
exec sp_addmergearticle StoredProcedureResolver, table1, @source_object=table1, @article_resolver='Microsoft SQLServer Stored Procedure Resolver',
@resolver_info=MyConflictResolver
Go
sp_addmergesubscription StoredProcedureResolver, @@Servername, 'StoredProcedureResolverSub1'
GO
sp_addmergesubscription StoredProcedureResolver, @@Servername, 'StoredProcedureResolverSub2'
GO
exec sp_startpublication_snapshot StoredProcedureResolver
--waiting for 1 minute for the snapshot to be generated
WAITFOR DELAY '00:01'
exec sp_startmergepushsubscription_agent StoredProcedureResolver, @@ServerName, StoredProcedureResolverSub1
GO
exec sp_startmergepushsubscription_agent StoredProcedureResolver, @@ServerName, StoredProcedureResolverSub2
GO
--waiting for 1 minute for the snapshot to be applied
WAITFOR DELAY '00:01'
GO
use StoredProcedureResolverSub1
GO
update Table1 set charcol='StoredProcedureResolverSub1' where pk<10
GO
use StoredProcedureResolverSub2
GO
update Table1 set charcol='StoredProcedureResolverSub2' where pk<10
GO
use StoredProcedureResolverPub
GO

exec sp_startmergepushsubscription_agent StoredProcedureResolver, @@ServerName, StoredProcedureResolverSub1
GO
exec sp_startmergepushsubscription_agent StoredProcedureResolver, @@ServerName, StoredProcedureResolverSub2
GO
WAITFOR DELAY '00:01'
GO
select Reason_text from MSmerge_conflicts_info 
GO
select * from MSmerge_conflict_StoredProcedureResolver_table1
GO
select * from table1
GO
select * from Logtable
GO
Here is a conflict within the Conflict Viewer:


 
 
  
 

Now look for the row less traveled. This is the row modified by inside our conflict resolver. Notice how it does not travel

--the row less traveled!
--this was the one we updated in the stored procedure conflict resolver
--but it does not move to the subscribers!
GO
select * from StoredProcedureResolverPub.dbo.table1 where pk=11
GO
select * from StoredProcedureResolverSub2.dbo.table1 where pk=11
GO
select * from StoredProcedureResolverSub1.dbo.table1 where pk=11
GO


You may notice that the Publisher db has won the conflict, but the rows were both updated on the subscriber dbs. This is because the default is subscription type is Client – which means the first DML to the publisher is the one which will persist.

It is worth pointing out that the Stored Procedure Conflict Resolver not only return the results set of data to be applied on the publisher and subscriber, but can also send out alerts, write data to replicated tables (which will not be replicated), write data to replicated tables, log data, in short do anything your skill in TSQL allows you to within the context of the security context your replication agent runs under.

It can’t however access Web Sync Subscribers, Heterogeneous databases, or SQLCE subscribers.

Here is an example of using a stored procedure resolver to access data on the subscriber. Note we are using dynamic SQL for this.

alter PROC MyConflictResolver
   (
     @tableowner SYSNAME ,
     @tablename SYSNAME ,
     @rowguid UNIQUEIDENTIFIER ,
     @subscriber SYSNAME ,
     @subscriber_db SYSNAME ,
     @log_conflict INT OUTPUT ,
     @conflict_message NVARCHAR(512) OUTPUT ,
     @destowner SYSNAME
   )
AS
   DECLARE @str NVARCHAR(MAX)
   DECLARE @newstr NVARCHAR(MAX)
   DECLARE @params NVARCHAR(MAX)

   DECLARE @originating_db varchar(40)
   DECLARE @PK int 
   DECLARE @charcol varchar(40)
  
   DECLARE @table1 Table (PK int, charcol varchar(40), originating_db varchar(40),rowguid uniqueidentifier)  
   SET @str = 'SELECT @originating_db = originating_db, @PK = PK, @charcol = charcol
   FROM [@subscriber].@subscriber_db.dbo.Table1 WHERE rowguid=''@rowguid'''
   SELECT  @newstr = REPLACE(@str, '@subscriber_db', @subscriber_db)
   SELECT  @newstr = REPLACE(@newstr, '@subscriber', @subscriber)
   SELECT  @newstr = REPLACE(@newstr, '@rowguid', @rowguid)

   SELECT  @params = N'@subscriber sysname, @subscriber_db sysname, @rowguid uniqueidentifier,
     @originating_db varchar(40) OUTPUT, @PK int OUTPUT, @charcol varchar(40) OUTPUT'

 select @newstr
   EXEC sp_executesql @newstr, @params, @subscriber, @subscriber_db, @rowguid,
       @originating_db = @originating_db OUTPUT, @PK = @PK OUTPUT,
       @charcol = @charcol OUTPUT
  
   insert into @table1(PK, originating_db, charcol, rowguid)
   select @PK, @originating_db, @charcol, @rowguid
  
   select * From @table1

GO

Using custom stored procedures to resolve conflicts is a great method to extend the basic functionality of merge replication.

 

Published Sunday, November 06, 2011 9:23 AM by Hilary Cotter

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement