25252

PK Violation after transactional replication

Question:

I have an application set up with transactional replication being pushed to a standby machine that will be used for emergency failovers. The replication appears to be working, any inserts made to Server 1 will automatically appear at Server 2.

However, I can't quite get the failover working. In the scenario that Server 1 becomes unavailable (which is the only scenario where Server 2 will ever be used, so the replication is one-way), the idea is that work should continue at Server 2, and that the transition should be somewhat seamless since all data has already been replicated.

But when moving to Server 2, after making sure that all updates on Server 1 have been transferred, I keep getting primary key violation exceptions, for some tables.

<blockquote>

Violation of PRIMARY KEY constraint 'PK_TableA'. Cannot insert duplicate key in object 'dbo.TableA'.

</blockquote>

A simple query such as

INSERT INTO TableA (Field1, Field2, TableB_ID) VALUES ('a','b', 6)

will yield the above error. It seems that when I instruct the table to assign an identity of its own, by omitting it from the query (TableA has an ID int identity(1,1) field), SQL Server will auto-assign an ID that violates a PK constraint. Why would this be?

<hr />

TableA has a trigger for INSERT and DELETE that does a simple denormalization job

UPDATE TableB SET Count = (SELECT COUNT(1) FROM TableA WHERE TableB.ID = TableA.TableB_ID) WHERE ID IN( -- Fetch affected ID's from deleted or inserted rows SELECT DISTINCT TableB_ID FROM deleted UNION SELECT DISTINCT TableB_ID FROM inserted )

This was accidentally not a part of the Server 2 database at the time of the replication, and I inserted it afterwards. Consistency in the TableB.Count field is not critical for the task at hand. The PK Violation occurred before the trigger existed in Server 2, as well as after creating it.

<hr />

At both Publisher and Subscriber, the ID field that is yielding the violations has the following definition:

[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL

I suppose the NOT FOR REPLICATION part is redundant on the Publisher, as no replication job will ever write to it, but I can't see that it should be the cause of the problem, either.

Answer1:

IDENTITY ranges must be managed explicitly in a replication scenario.

<ul><li><a href="http://msdn.microsoft.com/en-us/library/ms152543.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms152543.aspx</a></li> <li><a href="http://msdn.microsoft.com/en-us/library/ms151736.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms151736.aspx</a></li> </ul>

In your case you need to find, for each IDENTITY, a value larger than all current values.

Then you can configure one server to assign only odd numbers and the other only even numbers. JUst change the definition to IDENTITY(MAXPLUS1,2) on the publisher and IDENTITY(MAXPLUS2,2) on the subscriber.

Obviously you can extend this scheme to support any number of susbcribers.

Recommend

  • SQL Server 2008 R2 Inner Join Fails to match varchar fields because it contains special characters
  • REST Web Service - Dynamic Query Parameters
  • Join a table using on multiple columns using a OR syntax [duplicate]
  • join two tables with different number of rows(in sql server)
  • How To Query Many-to-Many Table (one table's values becomes column headers)
  • How to make an elasticsearch query that filters on the maximum value of a field?
  • ValidationResult Returned From IValidatableObject.Validate Is Not Localized
  • What is the JSF behaviour, if you bind the same backing bean property to two input fields in the sam
  • Order By Split Column
  • How to retrieve multiple columns from non-entity type sql query?
  • Specifying virtual keyboard type for EditText in XML
  • Strong vs Weak entities MYSQL
  • distinct values from multiple fields within one table ORACLE SQL
  • Extending the Django 1.11 User Model
  • How to enable mapping the private property of the entity
  • how to change api_token column in token guard
  • sweetalert2 inputoptions from file in select example
  • jQuery: How to AJAXify WordPress Search?
  • jwtBearer bearer token with rc-1 update to ASP.Net 5
  • MYSQ & MVC3 SQL connection error \\ ProviderManifestToken but I am using MySQL
  • Unable to install Git-core+svn by MacPorts
  • Django simple Captcha “No module named fields” error
  • PHP CURL timing out but CLI CURL works
  • Do I need to reset a Perl hash index?
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Why querying a date BC is changed to AD in Java?
  • Could not find rake using whenever rails
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • ilmerge with a PFX file
  • Why value captured by reference in lambda is broken? [duplicate]
  • Display Images one by one with next and previous functionality
  • Alternatives to the OPTIONAL fallback SPARQL pattern?
  • Comma separated Values
  • SQL merge duplicate rows and join values that are different
  • Understanding cpu registers
  • Why joiner is not used after Sequence generator or Update statergy
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • UserPrincipal.Current returns apppool on IIS