April 18, 2008 at 6:52 am
Hi,
we are inserting a lot of data into a replicated table (transactional replication).
There is a trigger for insert/update on subscriber side which is then fired for every row, which implicates that the bulk insert on publisher side is inserted row by row on subscriber side.
I searched MSDN for that behavior, but only found:
What is the effect of running a bulk insert command on a replicated database?
For transactional replication, bulk inserts are tracked and replicated like other inserts.
Source: http://msdn2.microsoft.com/en-us/library/ms151740.aspx
I always thought transactional replication is working like
1. Read log on publisher
2. Do all changes on subscriber
As a bulk insert should be -one- transaction and not a transaction for every row, replication should bulk insert on subscriber side also.
Or am I totally wrong? And if so, can I change this behavior?
Best Regards,
Jan
April 18, 2008 at 8:17 am
A bulk insert is a single transaction, but transactional replication does it's inserts and updates one row at a time in a single transaction:
BEGIN TRAN
EXEC spInsertRow...
EXEC spInsertRow...
EXEC spInsertRow...
EXEC spInsertRow...
COMMIT TRAN
Don't confuse a transaction with a batch. You can do lots of 1 row at a time inserts in a single transaction.
The distributor keeps track of which inserts and updates are part of a single transaction. So if you update every row in a table there will be a single update added into the distribution database for EACH row updated, but they will all get the same transaction ID. The distribution agent will begin a transaction, do the updates, and then commit.
Also make sure your bulk inserts are being logged...
April 18, 2008 at 9:45 am
Hi,
thanks for post, it is exactly the information I was looking for.
Best Regards,
Jan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply