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

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!

jwa082276Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Paul JacksonConnect With a Mentor Software EngineerCommented:
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
 
mcv22Commented:
You have to manually manage the identity ranges at the two locations.

See http://msdn.microsoft.com/en-us/library/ms152543.aspx
0
 
Paul JacksonSoftware EngineerCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jwa082276Author Commented:
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
 
Paul JacksonSoftware EngineerCommented:
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
 
jwa082276Author Commented:
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
 
Paul JacksonSoftware EngineerCommented:
correct
0
 
jwa082276Author Commented:
Can't I start the seeding any number? Say 20,000,000?
0
 
Paul JacksonSoftware EngineerCommented:
anything within the bounds of the column datatype
0
 
jwa082276Author Commented:
ok That is what I was thinking...its  bigint...8....

[AUDIT_LOG_ID] [bigint]
0
 
Paul JacksonSoftware EngineerCommented:
Yeah so plenty of scope then.
0
 
jwa082276Author Commented:
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
 
Paul JacksonSoftware EngineerCommented:
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
 
jwa082276Author Commented:
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
 
Paul JacksonSoftware EngineerCommented:
0
 
jwa082276Author Commented:
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
 
jwa082276Author Commented:
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
 
jwa082276Author Commented:
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
 
Paul JacksonConnect With a Mentor Software EngineerCommented:
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
 
jwa082276Author Commented:
This is done as wel.... :(

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

Any more ideas?
0
 
Paul JacksonSoftware EngineerCommented:
0
 
jwa082276Author Commented:
Update: after setting the Triggers to NOT FOR REPLICATION - this seemed to have fixed my issues!

Thanks everyone for your help in this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.