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
Distributor and reserve server is
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
- Truncate table on production (in one table there is FKs, so we can't)
- Insert data from another database
Edit: How about
linked server and
How do you think are there the better way?