Hi,

I need replication with ACID requirements and changes made to both databases,
but it will be done over VPN in production and can sometimes loose connectivity.
So I use transactional replication with updatable queued subscriptions.

What I cannot understand (and I'm not SQL expert at all), is why single row
insert into replicated table takes several minutes to replicate? Replication
is continious, test servers are in the same LAN and they experience no connectivity
problems. What's really cause me to ask this question is the fact, that enclosing
this single insert into explicit transaction (BEGIN TRAN, COMMIT TRAN) causes
data to be replicated immediately. Could anyone please explain the reason?

Both servers are as follows:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )

Sincerely,
Ilya Ryzhenkov

Hello Paul,

PI> a single row insert is logged as a transaction and treated as such
PI> by replication, so logically there shouldn't be any distinction.
That is what I thought. And that's why I ask.

PI> Is this reproducible?
In my system it is totally reproducable, however it's fairly large and cannot
be shared for inspection. As far as I remember, it only occurs when applying
changes from subscriber to publisher.
Scenario is as follows:
1. SP is executed on subscriber to perform business-rule on data. This is
done in explicit transaction.
2. When SP is done with its tasks, either successfully, or failed, it executes
another SP (outside of transaction, i.e. either commited or rolled back)
which logs result in the journal table. This is used for auditing purposes.
3. Results of SP execution within transaction is almost immediately visible
on the other publisher
4. It takes a lot of time for journalling item to appear on publisher.
5. If I execute another transcated action on subscriber, journal row from
previous activity and result of next transaction is almost immediately visible
on publisher. It seems like it 'pushes' data...

Thanks for the link.

/Ily

IIya,
a single row insert is logged as a transaction and treated as such by
replication, so logically there shouldn't be any distinction. Is this
reproducible? To optimise the replication setup - profiles, agent
positioning, separate distributors etc, please refer to this article:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)