Synchronizing in SQL Replication works when manually syncing, but not automatically
- by Dominic Zukiewicz
I'm using SQL Server 2005 to create a replication copy of the main databases, so that the reports can point to the replication copy instead of locking out our main databases.
I have set up the 3 databases as publications and then 3 subscribers moving the transactions over to the subscribers, instantaneously I hope!
What seems to be happening is that when using the "Insert Tracer" function, replication take publisher to distributor < 2 seconds, but to replicate to the subscribers can take over 7 minutes (and these are local databases on a SAN). This could be for 2 reasons:
The SQL statements used to query the database are obtaining locks which are stopping the transactions updating the subscribers.
The subscribers are just too busy for the replication to apply the changes.
What seems to trouble me more, is that although the Replication Monitor / Insert Tracer are showing these statistics, if you use the "View Subscription Details" and then click Start, it will sync within seconds.
My goal would be to have the data syncing (ideally) continuously, or every minute, perhaps I should reduce the batch size of the transactions?
What am I doing wrong?
[Note that the -Continuous flag is set!]
