We are in process of doing a migration from an ancient creaky server to a shiny new VM. Rather than just rebuild it and restore everything, we're taking the (painful) opportunity to clean things up and improve several systems.
As part of this, we're replicating
data from the old server to the new server, so that we can migrate
processes piecemeal, so that rollback is not "OH CRAP TURN IT OFF TURN
IT OFF ROLL BACK TO THE OLD SERVER".
But we ran into a
weird problem. On the target server, we had a many-to-many table that
sits between, let's say, stores and orders. We have a stores table, we
have an orders table, and this one (call it STORE_ORDERS for simplicity)
is just a linking table between the two. ID, stores_id, orders_id.
Everything scripted identically between the two databases (aside from
the NOT FOR REPLICATION flag).
We found, while
double-checking everything, that there were records missing from the
stores_orders table on our new box, that were just fine on the source
table. What. The. Hell. After looking for a few, we determined that it
had to be related to one of our users doing a bunch of changes.... but
just to the stores table.
What could it be? They're updating the END_ASOF field in STORES, which is part of a unique constraint
CREATE UNIQUE NONCLUSTERED INDEX [STORE_NUMBER_ENDASOF_IDX] ON [dbo].[STORE] ([STORE_NUMBER], [END_ASOF])
The table affected does have a FOREIGN KEY constraint on STORES. Because we're trying to migrate over, the DDL is the same.
ALTER TABLE [dbo].[STORES_ORDERS]
ADD CONSTRAINT [FK_STORES_ORDERS_STORES] FOREIGN KEY ([STORES_ID]) REFERENCES [dbo].[STORES] ([ID]) ON DELETE CASCADE
And just to make it more fun, there are some AFTER UPDATE triggers involved. (But those are unrelated).
Any ideas? Replication, UNIQUE constraint, Foreign Keys.
Now, go read this article: https://blogs.msdn.microsoft.com/repltalk/2011/10/04/sql-server-transaction-replication-bounded-update-or-why-my-update-was-transformed-into-an-deleteinsert-pair/
In the end, we figured out what had to be happening, then confirmed via trace. Care to guess?
our source table, when you update that unique constraint (AS PART OF A
SET; doing a singleton update, or "update stores where ID IN (1,2)"
doesn't do it, we tried), replication will push a BOUNDED UPDATE, aka
Which normally wouldn't be a big deal.
Except for that foreign key. Which has a CASCADE DELETE.
to recap: user does an UPDATE on the source table. Because they
updated a field in the unique constraint, the delete becomes a BOUNDED
UPDATE. Then, replication pushes this update, via a DEL/INS pair, to
the target server. That DEL hits the foreign key's CASCADE DELETE,
which then DELETEs the stores_orders row. Voila! We updated Table A,
and Table B loses records!
Moral of the story? Beware complications and interactions, and test even when you know everything matches.