SQL Server – Maintenance Plan on Replication Subscriber

SQL Server Replication isn't a good way to handle a database upgrade. A much better approach would be to take a full backup and restore then do your testing. After completing your testing you do a full backup and restore, then perform log restores until you are ready to cut over the database. After you take the production system offline you finish the log restores then bring the database online.

After all it seems that the answer to my question was #4 (Setup a transactional replication from db2 to db1 with table b as the only article)

There is a not-so-advertised option for the subscriptions (at least not to me) called @loopback_detection, which makes sure that transaction are not replicated back to their originator! This means that you can set up a two-way transactional replication, plain and simple, and accomplish what I was asking for: a subscriber that can update specific tables, so that these changes are replicated to the publisher (and never come back to the subscriber).

You have to pay attention to some options however, to avoid messing things up when setting up a two-way transactional replication: - When adding articles to the "reverse" publication: @pre_creation_cmd = N'none' -- so that the original tables are not dropped/recreated - When setting up the "reverse" subscription you should set: @sync_type = N'replication support only' -- so that no snapshot is required to initiate the subascriber @loopback_detection = 'true' (which is set by default)

These are the steps that need special attention. Other than that, it is a straight forward process.

When you have everything set up, you can test the reverse replication by executing an update on the replica db, while you have the "View synchronization status" windows for both replications open. You will notice that only the reverse replication will log a "1 transaction(s) with 1 command(s) were delivered." message.

PS. I will provide a detailed description of the setup process, shortly.