Hi experts,
This db has over 100 foreign key constraints. Would it even be possible to use Transactional Replication on such a db? How would SQL server know which tables to copy first to endure referential integrity?
Thanks
Based on the documentation:
Foreign keys are disabled automatically by replication agents, as the constraints are only checked on the Publisher. Therefore any subscribers would have them disabled, and INSERT/UPDATE/DELETE activity on the subscriber wouldn't cause a constraint violation.
Unless you're making writes on the subscriber(s), or you're using filtered publications, I can't think of any scenario where your replication traffic would cause an FK violation in the subscribers, unless you disable them on the publisher.
Worst case scenario, you can use DBCC CHECKCONSTRAINTS on the subscribers and see if it finds violations, and then take steps to correct them. Suggest you remove replication though before you do that.
2 Likes