2

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?

  • 1
    Then when tasks upload data to another database we need to synchronize one with production database on production server if you don't need near to real-time sync, you can use SSIS. It is very flexible and might suit your needs. Also, if the table is 6gb, once you snapshot it, the changes for t-rep won't be that much. – Kin Oct 6 '14 at 12:07
  • Kin, do I understand correctly that snapshot table in t-replication way? Thus which one subscriber and which one publisher databases? And can i do production database as publisher and subscriver at the same time? – Konstantin Oct 6 '14 at 15:52
  • 1
    Let me make it clear - I am recommending that you do not create any additional database only for running and populating tables from your nightly job. Instead, leave the job running on PROD server and let the changes be applied - using SSIS or T-Rep to the reserve server. Also, to answer your question, snapshot is from publisher to subscriber - one way. – Kin Oct 6 '14 at 16:43
  • I agree with Kin about keeping the data in a single prod db. The best solution depends on what are you trying to achieve. What is your reserve server for? What is wrong with the current design? – Sir Swears-a-lot Apr 18 '16 at 10:28
  • If I am not wrong you want to reverse transnational replication. Am I right?? – Rajesh Ranjan Aug 24 '16 at 14:11
0

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.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.