How is a BULK insert replicated

  • 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

  • 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...

  • 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