Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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

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?


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.


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.

share|improve this question
    
The IDENTs and max(ID)s are correct on both sides? I mean a situation like this –  dezso May 24 '12 at 9:23
    
@dezso: max(ID) on the problematic table yields the same ID for both databases, yes. What do you mean by IDENT here, and how can I verify it? For what it's worth: For tables where I can insert, I notice that the new records receive IDs from 1, although the replicated IDs are much higher. As long as the database recognizes that the higher IDs are in use, and skips them once reached, that shouldn't be a problem. –  David Hedlund May 24 '12 at 9:27
    
Then possibly a DBCC CHECKIDENT(TableA) can solve your problem? (By IDENT I was to mean the last inserted value in the identity column.) –  dezso May 24 '12 at 9:30
    
@dezso: CHECKIDENT on the subscriber yields: Checking identity information: current identity value '18', current column value '91987'. What does it mean that current identity value is 18? Is that what it's going to try to insert next? There is already records 18, 19, 20 in the table, and 91987 is the highest ID in the table; that has been pushed from the publisher. –  David Hedlund May 24 '12 at 9:33
    
It means that the subscriber wanted to issue 19 as the next ID (tried 18 on the last insert). DBCC CHECKIDENT(TableA) has set the seed automatically. –  dezso May 24 '12 at 9:40
show 2 more comments

1 Answer

up vote 3 down vote accepted

It looks like that the identity column on the subscriber side is out of sync. Run DBCC CHECKIDENT(TableA, RESEED) (or just DBCC CHECKIDENT(TableA)) and your problem will be gone. You can check the documentation here.

share|improve this answer
    
For the record, DBCC CHECKIDENT(TableA) didn't do the trick, but DBCC CHECKIDENT(TableA, RESEED) did. I thought RESEED would be the default? Could this be related to my issue? Oh well, question resolved. –  David Hedlund May 24 '12 at 10:18
    
@DavidHedlund - it is interesting, the doc says the two being equivalent. –  dezso May 24 '12 at 10:22
    
They are different. Without RESEED, it just tells you what the current seed is. With, it resets it to match actual data –  gbn May 24 '12 at 10:48
    
@gbn - edited the answer, thanks for the input. Is it possible that the linked doc page is wrong? –  dezso May 24 '12 at 10:51
    
Actually, I could be wrong now. I thought the default was NORESEED which matches what I said. Never assume, check!! –  gbn May 24 '12 at 11:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.