Solved

Merge Replication (BI-Directional) - Getting PK constraint Violation

Posted on 2011-02-18
22
1,665 Views
Last Modified: 2012-05-11
All - I have merge replication setup (bi directional over a wan).  
The publication is setup in Location "A" and the Subscriber is in Location "B".
We have setup an application to connect to the database in Location A and then we have the application setup in Location "B" to connect to a database in Location B, that is replicated from Location A.

I am replicating a table with the following schema:
CREATE TABLE [dbo].[AUDIT_LOG](
      [AUDIT_LOG_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
      [AUDIT_TEXT] [varchar](255) NOT NULL,
      [AUDIT_EVENT_FK] [bigint] NOT NULL,
      [AUDIT_SUBMIT_FK] [bigint] NULL,
      [AUDIT_MEMBER_FK] [bigint] NULL,
      [BROKER_ASSIST_NAME] [varchar](255) NULL,
      [CREATE_DATE] [datetime] NOT NULL,
      [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
PRIMARY KEY CLUSTERED
(
      [AUDIT_LOG_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[AUDIT_LOG]  WITH CHECK ADD  CONSTRAINT [AUDIT_LOG_AUDIT_EVENT_FKC] FOREIGN KEY([AUDIT_EVENT_FK])
REFERENCES [dbo].[AUDIT_EVENT_TYPE] ([AUDIT_EVENT_TYPE_ID])
GO

ALTER TABLE [dbo].[AUDIT_LOG] CHECK CONSTRAINT [AUDIT_LOG_AUDIT_EVENT_FKC]
GO

ALTER TABLE [dbo].[AUDIT_LOG]  WITH CHECK ADD  CONSTRAINT [AUDIT_LOG_AUDIT_MEMBER_FKC] FOREIGN KEY([AUDIT_MEMBER_FK])
REFERENCES [dbo].[MEMBER] ([MEMBER_ID])
GO

ALTER TABLE [dbo].[AUDIT_LOG] CHECK CONSTRAINT [AUDIT_LOG_AUDIT_MEMBER_FKC]
GO

ALTER TABLE [dbo].[AUDIT_LOG]  WITH CHECK ADD  CONSTRAINT [AUDIT_LOG_SUBMIT_AUDIT_FKC] FOREIGN KEY([AUDIT_SUBMIT_FK])
REFERENCES [dbo].[SUBMISSION] ([SUBMISSION_ID])
GO

ALTER TABLE [dbo].[AUDIT_LOG] CHECK CONSTRAINT [AUDIT_LOG_SUBMIT_AUDIT_FKC]
GO

ALTER TABLE [dbo].[AUDIT_LOG] ADD  DEFAULT ((0)) FOR [AUDIT_EVENT_FK]
GO

ALTER TABLE [dbo].[AUDIT_LOG] ADD  DEFAULT ((0)) FOR [AUDIT_SUBMIT_FK]
GO

ALTER TABLE [dbo].[AUDIT_LOG] ADD  CONSTRAINT [MSmerge_df_rowguid_4F6D3FB463764D58B41FCDCCA2E310D7]  DEFAULT (newsequentialid()) FOR [rowguid]
GO

When the application in Location B is launched,  it is suppose to write a record into this table. However, it is throwing the following error:

Violation of PRIMARY KEY constraint 'PK__AUDIT_LOG__22951AFD'. Cannot insert duplicate key in object 'dbo.AUDIT_LOG'.

When the application in Location A is launched, it writes a record in this table successfully without throwing an error.

Does anyone know why it would do this?

Thank you for any help!

0
Comment
Question by:jwa082276
  • 11
  • 10
22 Comments
 
LVL 12

Expert Comment

by:mcv22
ID: 34930437
You have to manually manage the identity ranges at the two locations.

See http://msdn.microsoft.com/en-us/library/ms152543.aspx
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 34930451
Because of your identity column when the application starts up at location b it will try to use the same value.
To solve this problem before I have used a script at the secondary location to start the identity value at a higher value say for example 50,000.
The command would be DBCC CHECKIDENT("AUDIT_LOG", RESEED, 50000).

The alternative would be to change the column datatype to a uniqueidentifier but obviously this would require changes to your application.

Here's a link about dbcc checkident :

http://msdn.microsoft.com/en-us/library/ms176057.aspx
0
 

Author Comment

by:jwa082276
ID: 34930526
So I ran the command (DBCC CHECKIDENT('audit_log')) on both databases and got this for the publisher:

Checking identity information: current identity value '14', current column value '11014'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

And this for the Subscriber:
Checking identity information: current identity value '13', current column value '11011'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Should I run this on the "subscriber" only?
DBCC CHECKIDENT("AUDIT_LOG", RESEED, 20000).

Do I not need to reseed the publisher?

0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 34930559
Yes on the subscriber only, the publisher will use range 1 - 19999, after running the script the subscriber will use 20000 +
Not sure why you're column values have 110 in front of them though.
0
 

Author Comment

by:jwa082276
ID: 34930577
This would mean that the Subscriber would start at 20000 range...so that it wouldnt cause the PK constraint violation I was seeing when the insert would happen on the Subscriber side..correct?  
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 34930596
correct
0
 

Author Comment

by:jwa082276
ID: 34955513
Can't I start the seeding any number? Say 20,000,000?
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 34956106
anything within the bounds of the column datatype
0
 

Author Comment

by:jwa082276
ID: 34956112
ok That is what I was thinking...its  bigint...8....

[AUDIT_LOG_ID] [bigint]
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 34956560
Yeah so plenty of scope then.
0
 

Author Comment

by:jwa082276
ID: 34965161
Thanks everyone so far for your help!
now I am runing into another issue - A row insert at 'subscriberserver.dbname' could not be propagated to publisher.dbname'. This failure can be caused by a constraint violation.  The INSERT statement conflicted with the FOREIGN KEY constraint "ADDRESS_APPLICANT_FKC". The conflict occurred in database "dbname", table "dbo.APPLICANT", column 'APPLICANT_ID'.

the check constraint is as follows on the address table:
ALTER TABLE [dbo].[ADDRESS]  WITH CHECK ADD  CONSTRAINT [ADDRESS_APPLICANT_FKC] FOREIGN KEY([APPLICANT_FK])
REFERENCES [dbo].[APPLICANT] ([APPLICANT_ID])
GO


See pic attached for message and details..constraint_violation

When I query the subsciber, I see the address_id of 21000001..see pic..constraint_violation2

and Of course when I query the publisher db..i dont see those records...why would they not be inserted?  If the records dont exist on the publisher side why wouldnt they be inserted?

Should I set the options to False for "copy check constraints"? If I do this wont I lose referential integrity?
constraint-violation2.png
constraint-violation.png
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 29

Expert Comment

by:Paul Jackson
ID: 34965581
Is the APPLICANT_ID column in the applicant table an identity insert column? Is it set 'NOT FOR REPLICATION' in the column definition?

Same question for the ADDRESS_ID column in the ADDRESS table.
0
 

Author Comment

by:jwa082276
ID: 34965623
yes, both applicant_id and address_id are identity and set for NOT FOR REPLICATION.

[APPLICANT_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
[ADDRESS_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 34965625
0
 

Author Comment

by:jwa082276
ID: 34966703
So I went back and changed the option on all the articles to "false" for "copy check constraints"...still having issues.

After reading the article: http://msdn.microsoft.com/en-us/library/ms180843.aspx   I looked at the keys on my tables; for my "publisher" the enforce for replication was set to "YES"..for my "subscriber" (this is bidirectional merge so both are technically publishers/subscribers") it was already set to "NO" ...was that b/c I selected "false" for "copy check constraints"?  Does this mean I only need to set this to "NO"  on my publisher?

Is there a script to easy find all FK constraints and setting the enforce for replication to NO for each of them?
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 34967628
Essentially you just need to turn off the option 'enforce for replication' you can be confident that you don't need this on because the constraint was checked when the column was inserted whether at subscriber or publisher. So yes I think you just need to set this to 'NO' on your publisher as you say this has already been done on the subscriber.
I don't think this was done by you setting false for copy check constraints but by the replication process setup.
I think maybe you need to set the copy check constraints to true to ensure they exist at the subscriber end to and get checked when a row is inserted at the subscriber.
0
 

Author Comment

by:jwa082276
ID: 34970240
Thanks! I set all the FK constraints for tables to NOT FOR REPLICATION and set the "copy check constraints" back to true.
People will start testing soon.
I hope this fixes the issue.
Thanks so far for all the valuable input.

0
 

Author Comment

by:jwa082276
ID: 34970836
no luck..I am still getting the following conflicts when data is entered in on the "subscriber" side which needs to replicate back to the publisher side.

A row insert at 'subscriber.dbname could not be propagated to publisher.dbname'. This failure can be caused by a constraint violation.  Explicit value must be specified for identity column in table 'APPLICANT_HISTORY' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

all my identity columns are set to NOT FOR REPLICATION...and all the FK's are set to NOT FOR REPLICATION..

I am stumped. :(
0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 500 total points
ID: 34971097
OK seems you need to set the 'NOT FOR REPLICATIOn' option on all the check constraints as well

see : http://msdn.microsoft.com/en-us/library/ms152529.aspx
0
 

Author Comment

by:jwa082276
ID: 34971314
This is done as wel.... :(

I have reseeded the identity values on the subscriber side to start at 20000000

Any more ideas?
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 34971497
0
 

Author Comment

by:jwa082276
ID: 34993918
Update: after setting the Triggers to NOT FOR REPLICATION - this seemed to have fixed my issues!

Thanks everyone for your help in this.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now