Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Merge replication - conflicts with primary keys Expand / Collapse
Posted Tuesday, July 22, 2008 7:58 AM


Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:04 AM
Points: 21, Visits: 272
I am running merge replication (pull) between few dozens leptops and once in a while when two people update information in close proximty in time, I will get a primary key violation. It happens because application that running in laptops does next steps :


INSERT INTO TABLE1… the same keys
INSERT INTO TABLE2… the same keys

INSERT INTO TABLEn… the same keys

I can not change application in order to replace delete + insert with update, it is hardcoded and I don’t want to change table structure and define another primary

What is the best way to handle this error. Should I redesing the table and primary keys or there is another way just remove all rows from replicated tables where Key_Field = @KEY on Subscriber? My intent is resolve conflicts without using the interactive conflict resolution.
Any assistance in the matter would be greatly appreciated.

My System is:
Publisher: SQL Server 2005 std + SP2 + CU8
Subscribers: SQL Server 2005 Express + SP2 + CU8

--CREATE Publication
exec sp_addmergepublication
@publication = N'Pub_MyDB',
@description = N'Merge publication of database ''MyDB'' from Publisher ''MYSERVER''.',
@sync_mode = N'native',
@retention = 14,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'true',
@enabled_for_internet = N'false',
@snapshot_in_defaultfolder = N'false',
@alt_snapshot_folder = '\\...\repldata',
@compress_snapshot = N'true',
@ftp_port = 21,
@ftp_login = N'anonymous',
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@dynamic_filters = N'false',
@conflict_retention = 14,
@keep_partition_changes = N'false',
@allow_synctoalternate = N'false',
@max_concurrent_merge = 0,
@max_concurrent_dynamic_snapshots = 0,
@use_partition_groups = null,
@publication_compatibility_level = N'90RTM',
@replicate_ddl = 1,
@allow_subscriber_initiated_snapshot = N'false',
@allow_web_synchronization = N'false',
@allow_partition_realignment = N'true',
@retention_period_unit = N'days',
@conflict_logging = N'both',
@automatic_reinitialization_policy = 0

-----------------BEGIN: Script to be run at Publisher 'Publisher'-----------------
DECLARE @subscriber VARCHAR(120)
SET @subscriber = 'XP_MYSYBSCRIBER_O'
use [GilatConfig]

exec sp_addmergesubscription @publication = N'Pub_MyDB',
@subscriber = @subscriber,
@subscriber_db = N'MyDB',
@subscription_type = N'pull',
@subscriber_type = N'local',
@subscription_priority = 0,
@sync_type = N'Automatic'

-----------------END: Script to be run at Publisher 'Publisher'-----------------
-----------------BEGIN: Script to be run at Subscriber 'Subscriber'-----------------
use [GilatConfig]
DECLARE @publisher VARCHAR(120)
DECLARE @distributor VARCHAR(120)
SET @publisher = 'MYSERVER'
SET @distributor= 'MYSERVER'
exec sp_addmergepullsubscription @publisher = @publisher,
@publication = N'Pub_MyDB',
@publisher_db = N'MyDB',
@subscriber_type = N'local',
@subscription_priority = 0,
@description = N'',
@sync_type = N'Automatic'

-----------------END: Script to be run at Subscriber 'Subscriber'-----------------

-- Merge Agent
REM -- Declare the variables.
SET PublicationDB=MYDB
SET SubscriptionDB= MYDB
SET Publication=Pub_ MYDB
SET DistributorLogin=XXXXXXX
SET DistributorPassword=XXXXXXX
SET PublisherLogin=XXXXXXX
SET PublisherPassword=XXXXXXX
REM --Start the Merge Agent with concurrent upload and download processes.
REM -- The following command must be supplied without line breaks.
"C:\Program Files\Microsoft SQL Server\90\COM\REPLMERG.EXE" -Publication %Publication% -Publisher %Publisher% -Subscriber %Subscriber% -Distributor %Publisher% -PublisherDB %PublicationDB% -SubscriberDB %SubscriptionDB% -PublisherSecurityMode 0 PublisherLogin %PublisherLogin% -PublisherPassword %PublisherPassword% -OutputVerboseLevel 2 -SubscriberSecurityMode 1 -SubscriptionType 1 -DistributorSecurityMode 0 -DistributorLogin %DistributorLogin% -DistributorPassword %DistributorPassword% -Validate 3 -ParallelUploadDownload 0 -StartQueueTimeout 60 -QueryTimeout 600

Post #538489
Posted Thursday, March 10, 2016 2:35 AM


Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 472, Visits: 1,666
As long as your application allows users to specify the primary key you'll get these primary key violation conflicts. In a non-replicated environment the users would not be allowed to this same operation: the primary key would prevent them from creating the duplicate row.

I would suggest to adapt the application such that it allows the users to update the rows instead of deleting and then recreating them. Plus make the users aware of the problem they're creating when deleting and recreating the same values.

An alternative approach could be to have the software not physically delete the rows upon the ned user's request, but instead update a column indicating the row is not to be shown anymore, i.e. 'soft-deleted'. When recreating a previously 'soft-deleted' row, you do not insert a new row but update the soft-deleted row to remove the 'deleted' marking. This way the row's guid will remain the same. A conflict will still occur if the laptops haven't been synced in time, but this conflict will only be a type 2(Column update conflict) which is easy to resolve once you know which is the latest update. The application logic for creating a new row is going to be a little more complicated, as well as you now need to check for the "deleted" column in any and of the queries referencing this table. But it does solve your problem

Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw

If you don't have time to do it right, when will you have time to do it over?
Post #1768183
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse