Sql-server – Transactional Replication hangs when huge transactions flow

Caution:

Since you will be using SQL Server 2012 express edition (and sql 2008R2), it has a limit of 10GB per database size (for previous versions, it is still 4GB). So if your table is not going to grow that big with all the archive data for many years, you are good uptill it hits 10GB limit.

My first suggestion would be to use Transactional replication. It wont have that much of overhead as you are just replicating 1 table. If you set the agent to run continuously or every 5 mins, then you will have up-to-date data on the destination (subscriber). You just have to remember to not replicate delete statements.

Secondly, you can use SSIS and schedule it using SQL Agent Job to incrementally load the data into the destination server.

Basically the problem is your subscriber going offline. If it remained online then the replicated data would be committed on the subscriber and there would be no problem.

The effect on transactional replication is described in the following link: https://technet.microsoft.com/en-us/library/ms151254(v=sql.105).aspx

For each database that will be published using transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database might require more space than the log of an identical unpublished database, because the log records are not truncated until they have been moved to the distribution database.

The simplest answer is to get more disk space to support the potential log size.

EDIT: Clarification from your reply: The subscriber gets disconnected for a long period due to a failure of some sort.

If disk space cannot be made available to handle the unpublished data, then you basically need to delete the subscription. Once the outage is resolved, then you can restore and re-establish the replication subscription.