Best way to transfer a few tables from reserve server to production

Question

We have two MS SQL Servers (2012 Standard Edition on Windows 2012 Server) which are located in the same subnet. One is production server. Another is reserve server.

Currently there is a transactional replication between two databases on these servers where production server is Publisher and Distributor and reserve server is Subscriber.

On production server every night runs some tasks which inserts to (or updates) about of 5 tables one of them is about 6 Gb.

We would like to move this tasks to reserve server and create on one another database with the same scheme. Then when tasks upload data to another database we need to synchronize one with production database on production server.

One moment : For supporting actual data we must to include another database on reserve in replication as Subscriber from production server.

As one way we consider linked server:

1. Truncate table on production (in one table there is FKs, so we can't)
2. Insert data from another database

Edit: How about linked server and merge functionality?

How do you think are there the better way?

Answer 1

I think linked server and MERGE would be the best way to do it. Set it to run at the appropriate interval and go for it. Take care with the merge statement so that it properly detects when to do an update or not. But since they are on the same subnet, it should perform adequately.

But linked servers have their own share of risks as well, make sure you set them up correctly (pass through security, etc.).

If you already have SSIS configured in your environment, that may be the more manageable approach.