I have this problem: two tables with one-to-many relationship between them. When they are synced between different instances of SQL Server, parent goes first, as all children have FK to it and depend on it being present, and after that children come (they may have big payloads and take some time to sync).
Problem is that records are processed in scheduled job that takes them automatically as they come, and if some children are not there it fails, or even worse, in some cases adds missing children, so we end up with duplicates. As I'm no expert on replication, I want to know if merge replication can ensure that parent record is not usable until all of the pending children are synced too? If it is possible using some kind of transaction or even by adding flag to parent record that would be updated when sync is done so that I can check it to make sure it is ready for processing, then answer would be just pointer to resources how is that feature called, and what I need to ask from replication team to configure there.
Otherwise, if you are sure that it is not possible, and I have to take care of it myself in some other way is valuable also, as it helps me find out which path to take.
Thanks!
