Replication of Parent/Child Tables

Hi,

Please can somebody offer me some advice re SQL Server 2005? We have a
reservations database hosted by a third party supplier. Because this
database processes many transactions and is very busy, the supplier
has implemented a second database which is kept in sync with the main
database (I'm not sure whether they are using mirroring or
replication, or whether there is a significant difference). The
outcome is that we have access to a synchronised copy of the data,
allowing us to run our own queries without impacting the performance
of the main database. This is all fairly logical.

The issue we are having is in terms of timing. When we run a query, we
believe that it's possible for a record to be part-way through
replication, such that the parent record has been copied across, but
the child records may not have been. For example, we believe that if
we added a new customer to the CUSTOMER table on the main database,
along with a new order (for that customer) in the ORDERS table, then
the replication might be in the process of copying the first record
(lets say the CUSTOMER record) when we run our query on the second
database. In this situation, we believe we would see a customer with
no orders.

Please can somebody tell me if this is true or not? I'm assuming there
must be some kind of referential integrity and/or two-phase commit
applied to replication/mirroring, but I'm not a SQL Server expert. I
realise this is a very high level question (since I don't know the
technical detail of how the third party supplier has configured the
synchronization between the two databases) but any pointers would be
really useful.

Finally, if it turns out to be impossible to ensure the records are
perfectly in sync at any point in time, then is it possible to
determine whether a replication is in progress?

Many thanks for your help

Laurie

If it is replication then this behaviour is possible (and normal) if the
original parent and child records were added in separate transactions.
Otherwise you'd only see it if you were using NOLOCK in your query (read
uncommitted). You could use profiler to see if the original inserts to the
parent and child are done in separate transactions.

I doubt you're using mirroring - for that to be used in a query you'd have
to have database snapshots being produced and therefore you wouldn't be
getting any sort of reasonable latency that you report. It could also be
log-shipping (with standby) but you'd be seeing dropped connections when the
logs are restored which sounds as though it isn't happening.

HTH,

Paul Ibison