Solved

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

Posted on 2011-02-18
22
1,708 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.

739 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