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.

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.

Answer 1

IDENTITY ranges must be managed explicitly in a replication scenario.

• http://msdn.microsoft.com/en-us/library/ms152543.aspx
• http://msdn.microsoft.com/en-us/library/ms151736.aspx

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.

Answer 2

I ran into the same problem - the CheckIdent method is also what saved me, here's a quick and dirty script that got me going.

If you send the output of this query to text (not grid results, it's a button in the SSMS toolbar) you can copy/paste it into a new query window and get another set of output that has all the checkIdent commands for every table in the database with the right seed value (assuming you always increment by one on every table that has an identity). You can then take those results (if you see some nulls in the second query window you may need to omit those manually) and copy/paste into a third query window and execute it.

Not a glamorous solution, but you can get a DB back up and running really quickly if you're in a bind (your main DB server went away and you need a replica (maybe the only replica?) to become the primary server.

SELECT 'select ''[' + s.name + '].[' + t.name + ']'' as TableName, ''DBCC CHECKIDENT(''''[' + s.name + '].[' + t.name + ']'''', ''''RESEED'''','' + cast(max ([' + s.name + '].[' + t.name + '].[' + i.name + '])' + '+1 AS varchar(50)) + '')'' as reseed from [' + s.name + '].[' + t.name + ']'
+ char(13) + char (10) + 'union all' 
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.identity_columns i
on i.[object_id] = t.[object_id]
order by t.name