Hi,

I developed a two-way merge replication system between a single server and
multiple clients. It certainly wasn't trivial.

> I'm developing a replication scheme and I need to copy chronologically
> modifications of a database to another one.

I would first question whether you really do need them to be replicated
chronologically, and do you mean chronologically based on when the operation
that caused the audit entry was performed (audit pkey generated) or based on
the times that the transactions were committed?

> pkey | query
> -----------------
> 1 | insert ... COMMITED RECORD( by transaction #1 )
> 2 | update ... COMMITED RECORD( by transaction #1 )
> 3 | insert ... COMMITED RECORD( by transaction #2 )
> 4 | insert ... RECORD INSERTED BY TRANSACTION #3 CURRENTLY
> UNCOMMITED (invisible to my current transaction)
> 5 | update ... COMMITED RECORD( by transaction #2 )
> 6 | update ... COMMITED RECORD( by transaction #2 )
>
> If I connect to the database with transaction #4 I need to know which
> one is the last record, previous to any unncommited transaction.
> My expected answer is pkey = 3.

If you really do need to process them in pkey order:
Consider that transaction #4 has no way to know whether or not transaction
#3 will be successfully committed. You can only detect this by checking
again later after a reasonable amount of time (try to keep transaction
durations as short as possible). That being the case you could store the
server time in each audit entry and process the audit rows for replication
while they are contiguous (no missing pkey) or the time of the row after the
gap is sufficiently old to consider that the rows in the gap will never
appear. It would probably be better to use a selectable stored procedure to
do this else some work on the client, rather than a single SQL statement.

If it is ideal to process them in pkey order but it does not need to be
perfect (such as processing the uncommitted row above in the next
replication):
You just need to determine the missing entries to process since the last
time that you replicated. To do this store the pkey and time of the last
entry processed in the replication. On next replication time process a) all
entries with a higher pk, and b) all entries prior to the last pkey where
the time is within X minutes prior to the last time, skipping those pkeys
that exist in your SENT_AUDIT_RANGES table.

Regards,

Jarrod Hollingworth
Complete Time Tracking
http://www.complete-time-tracking.com/