Merge replication - conflicts with primary keys

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SS2K5 Replication » Merge replication - conflicts with primary...

Merge replication - conflicts with primary keys

Rate Topic

Display Mode

Topic Options

Author

Message

Oleg Oransky

Posted Tuesday, July 22, 2008 7:58 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:56 AM
Points: 21, Visits: 248

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 :

BEGIN TRANSACTION
DELETE FROM TABLE1 WHERE Key_Field = @KEY
DELETE FROM TABLE2 WHERE Key_Field = @KEY
….
DELETE FROM TABLEn WHERE Key_Field = @KEY

INSERT INTO TABLE1… the same keys
INSERT INTO TABLE2… the same keys
…
INSERT INTO TABLEn… the same keys
END TRANSACTION

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
Key.

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'

GO
-----------------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
@ECHO OFF
REM -- Declare the variables.
SET Publisher= MYSERVER
SET Subscriber=%COMPUTERNAME%
SET PublicationDB=MYDB
SET SubscriptionDB= MYDB
SET Publication=Pub_ MYDB
SET DistributorLogin=XXXXXXX
SET DistributorPassword=XXXXXXX
SET PublisherLogin=XXXXXXX
SET PublisherPassword=XXXXXXX
@ECHO ON
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

« Prev Topic | Next Topic »

Permissions