Sql-server – transactional Replication of millions of updates

I tested it out, and yes, this works, even under Peer-to-Peer. (My original comment was incorrect as that only applies to physical columns.)

Given that you don't control the publication, I would recommend naming the column(s) you add very carefully so as to not interfere with any future schema changes. Also, if this database backs a 3rd-party ISV application, for example, I would be extremely cautious about making any schema changes to the base tables.

Examine the output of sp_helpsubscriptionerrors. One issue I have seen is with DDL commands; they are treated differently in replication so if manual schema changes are being made to the Publisher, they can sometimes fail. There may also be specific DML commands failing on the subscriber due to transactional inconsistencies. If this is the case, you can use the distribution agent profile called "Continue On Data Consistency Errors" which will ignore duplicate key and other errors.

Latency could be causing the problem (according to MS, latency and throughput are the most important factors in transactional replication); you can track that with sp_posttracertoken. Both the Log Reader and Distribution agents have parameters which can be tweaked to compensate for that. Try lowering these values, particularly on the Log Reader.

Distribution Agent (from MSDN):

MaxDeliveredTransactions Is the maximum number of push or pull transactions applied to Subscribers in one synchronization. A value of 0 indicates that the maximum is an infinite number of transactions. Other values can be used by Subscribers to shorten the duration of a synchronization being pulled from a Publisher.

CommitBatchThreshold Is the number of replication commands to be issued to the Subscriber before a COMMIT statement is issued. The default is 1000.

Log Reader Agent (from MSDN):

MaxCmdsInTran Specifies the maximum number of statements grouped into a transaction as the Log Reader writes commands to the distribution database. Using this parameter allows the Log Reader Agent and Distribution Agent to divide large transactions (consisting of many commands) at the Publisher into several smaller transactions when applied at the Subscriber. Specifying this parameter can reduce contention at the Distributor and reduce latency between the Publisher and Subscriber. Because the original transaction is applied in smaller units, the Subscriber can access rows of a large logical Publisher transaction prior to the end of the original transaction, breaking strict transactional atomicity. The default is 0, which preserves the transaction boundaries of the Publisher.