** This was previously asked on Stackoverflow - I removed it from there and recreated here **
I have an issue with SQL Server Transactional Replication. My topology is as follows (and this cannot be changed, so I'm trying to work within the limitations that it has).
I have a publisher database (SourceDB) that is used by a new application. Some of the data entered there needs to be made available to an old application that is being replaced by the new one; the old application uses TargetDB, (the data in the TargetDB will be read only and used for reporting and downstream feeds). Both databases contain broadly similar data, but structured very differently. Data needs to be available as close to real time as possible. We have a remote distributor.
So we have set up a proxy version of SourceDB which acts as a subscriber (SourceProxyDB). This contains the subset of tables required for replication. Hanging off these tables are triggers which will transform the data and enter it into TargetDB.
SourceDB and the remote distributor are both SQL Server 2012 SP1; TargetDB is SQL Server 2008R2 SP2.
So basically:
- data entered in SourceDB
- transactional replication moves it to SourceProxyDB
- triggers transform and enter data in TargetDB
So far, so good. For the most part this has been working well... until we started seeing some issues as follows:
- There is a parent-child table relationship in SourceDB that needs to get into TargetDB
- Typically one parent record is created followed by around 130 child records
- Each child record contains a reference to the parent record's ID, which is an identity column
- Sometimes we see the parent record arrive first in SourceProxyDB, followed by the child records as expected
- Sometimes we see the parent record arrive out of order; either after all the child records arrive, or in the middle of the set of child records
- There is no pattern to this
- When the child records arrive first, they all contain a reference to the parent record which follows them, implying that the records are entered in the correct order in SourceDB but replicated in the wrong order
- All child records always arrive in the correct sequential order; the only record out of order is the parent one
What we know/have tried so far:
- confirmed that the parent and child records are added to SourceDB in a single transaction
- used SQL Profiler to confirm that the parent and child records are added to SourceDB in the correct order
- used SQL Profiler to confirm that the parent and child records are replicated to SourceProxyDB in the wrong order
- added a FK relationship to SourceDB to tie the parent and child records together marked the FK relationship 'False' for 'Not for Replication'
- reinitialized replication for the parent and child tables (note - after doing this, the FK didn't appear in SourceProxyDB, which made me suspicious)
- set SubscriptionStreams to 1
... and I'm still periodically seeing the parent record replicated either after or in the middle of the associated child records at SourceProxyDB.
We're not seeing any FK errors or violations at the subscriber (SourceProxyDB) - as noted above, the FK didn't make it to SourceProxyDB after replication was re-initialized. The nature of the logic in the triggers requires the parent record to be available to the child records however so we are seeing logic errors.
I was under the impression that transactional replication would preserve the order of insertion of records, particularly those with FK relationships.
I'm at a real loss as to where to look next. Any insights as to why this is happening and any possible solution(s) will be gratefully received; I've been tearing my hair out for days over this now...
