Solved

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

Posted on 2011-02-18
22
1,726 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

627 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