Chris Skorlinski Microsoft SQL Server Escalation Services
Yes you can use both Transactional and Merge Replication to move data inserted using the BCP.exe utility of the Bulk Insert command to Subscribers.
By default, Transactional Replication will replicate data loaded via BCP or Bulk Insert. For performance, you may want to load data using the bcp Utility –b batch_size parameter set from 2,000 to 5,000. This will allow the LogReader to move to the Distribution Database inserted commands in blocks/batches of 2,000 (5,000). The Distribution Agent intern saves changes at the Subscribers in smaller blocks. Smaller blocks/batches of data reduce locking. You’ll also find it easier to track progress in Replication Monitor.
Another option is to use the LogReader -MaxCmdsInTran 2000 to have the LogReader group in batches of 2000 insert cmds. Again you can experiment to see what value provides best performance.
If you are bulk loading millions of rows it may be faster to disable replication, load the data, reload subscriber using backup/restore, then re-enable replication as compared to moving individual inserts via Replication.
Merge Replication requires an additional step to move data to the subscribers. Since Merge picks up data via Table Triggers, you need to either 1) fire the trigger on insert or 2) mark inserted rows to be replicated.
To bulk-load data into tables published by merge replication using the bcp utility
At either the Publisher or Subscriber, execute the bcp Utility or BULK INSERT to insert data into a table published using merge replication.
Use one of the following methods to ensure that replication metadata is generated for the inserted data.