事务复制后违反PK [英] PK Violation after transactional replication
问题描述
我有一个应用程序，该应用程序将事务复制推送到备用计算机，该备用计算机将用于紧急故障转移。复制似乎可以正常工作，对服务器1所做的任何插入都会自动出现在服务器2上。
但是，我无法完全实现故障转移。在服务器1不可用的情况下（这是唯一将使用服务器2的情况，因此复制是单向的），其想法是工作应该在服务器2上继续进行，并且过渡应该是无缝的因为所有数据已经被复制。
但是当移至服务器2时，在确保已传输服务器1上的所有更新之后，我会不断收到主键冲突异常
违反PRIMARY KEY约束'PK_TableA'。无法在对象'dbo.TableA'中插入重复键。
简单查询，例如
插入到TableA（Field1，Field2，TableB_ID）值（'a'，'b'，6）
将产生上述错误。似乎当我指示表分配其自身的标识时，通过从查询中省略它（ TableA 具有 ID int identity（1 ，1）字段），SQL Server将自动分配一个违反PK约束的ID。为什么？
TableA 会触发 INSERT 和 Delete 可以完成简单的非规范化工作
UPDATE TableB
SET Count =（SELECT COUNT（1）from TableA WHERE TableB.ID = TableA.TableB_ID）
WHERE ID IN（
-受到影响删除或插入的行中的ID
SELECT DISTINCT TableB_ID删除的
UNION
SELECT DISTINCT TableB_ID FROM插入的
）
在复制时，这偶然不属于Server 2数据库的一部分，之后我将其插入。 TableB.Count 字段中的一致性对于手头的任务并不重要。 PK违例发生在服务器2中存在触发器之前，以及触发器创建之后。
在发布者和订阅者处，产生违规的ID字段具有以下定义：
[ID] [int] IDENTITY（1,1）非复制不为空
我认为非复制部分在发布服务器上是多余的，因为任何复制作业都不会写入该文件，但是我也看不到它应该是引起该问题的原因。
在复制方案中必须明确管理IDENTITY范围。
- http://msdn.microsoft.com/en-us/library/ms152543.aspx
- http://msdn.microsoft。 com / en-us / library / ms151736.aspx
对于您而言，您需要为每个IDENTITY查找一个大于所有当前值的值。
然后，您可以将一台服务器配置为仅分配奇数，而仅分配偶数。 JUst将定义更改为发布者上的IDENTITY（MAXPLUS1,2），并更改为订阅者上的IDENTITY（MAXPLUS2,2）。
显然，您可以扩展此方案以支持任何数字
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.
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.
这篇关于事务复制后违反PK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
