title: PK Violation after transactional replication
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <blockquote>
    <p>Violation of PRIMARY KEY constraint 'PK_TableA'. Cannot insert duplicate key in object 'dbo.TableA'.</p>
  </blockquote>
  
  <p>A simple query such as</p>
  
  <pre><code>INSERT INTO TableA (Field1, Field2, TableB_ID) VALUES ('a','b', 6)
  </code></pre>
  
  <p>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 (<code>TableA</code> has an <code>ID int identity(1,1)</code> field), SQL Server will auto-assign an ID that violates a PK constraint. Why would this be?</p>
  
  <hr>
  
  <p><code>TableA</code> has a trigger for <code>INSERT</code> and <code>DELETE</code> that does a simple denormalization job</p>
  
  <pre><code>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
  )
  </code></pre>
  
  <p>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 <code>TableB.Count</code> 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.</p>
  
  <hr>
  
  <p>At both Publisher and Subscriber, the ID field that is yielding the violations has the following definition:</p>
  
  <pre><code>[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
  </code></pre>
  
  <p>I suppose the <code>NOT FOR REPLICATION</code> 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.</p>