Sql-server – Replication stalling on one subscriber

Yes it is possible to have many publishers and only one subscriber. You can also have one publisher and many subscribers. So in your case you will be able to publish from City1 and City2 and subscribe only City1 on those publishers. You can use this link to have an overview of the replication engine and how to setup it. http://technet.microsoft.com/en-us/library/ms151198.aspx

Hope this was helpful

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.