0

I want to setup replication between to databases. Every morning we do a data dump in a few databases. One database has 27 tables in it. We append records to 3 tables and insert records into a few others. After that no updates or inserts are made. I would like to replicate those changes to the same databases on another server.

Should I use merge or snapshot replication?

Or is mirroring something I should consider?

Kyle Johnson
  • 763
  • 1
  • 13
  • 31

1 Answers1

0

I think it'd be easier to schedule an SSIS job to do this. Do you use the Data Import Export Wizard for this? You could save the package that was created, and then schedule that package to run with a GETDATE() parameter inside the data retrieval query.

Otherwise, as long as it's less than 5 tables, I think it'd be easier to write a new SSIS package. Depending on the size of the table, you could just truncate and reload the entire table daily.

Arthur D
  • 592
  • 4
  • 10
  • That is what we are doing now. I'm not totally against it but why create something when the technology to do it is already there. – Kyle Johnson Aug 02 '16 at 20:00
  • The thing is, replication is a lot more maintenance overhead and can cause other issues. It requires upfront configuration, along with extra work managing during server reboots, patching, schema changes, etc. I would say that if you are working on less than 5 tables, and your data only has to be loaded once per day, SSIS is a better solution. – Arthur D Aug 02 '16 at 20:26
  • 1
    Or… use the SSIS package you already have to do the same thing on multiple servers. If you're feeling fancy, you can multiplex the same file to multiple destinations, but that seems like overkill. – Ben Thul Aug 02 '16 at 23:20
  • It is 3 databases for now. That could change. 1 database has 27 tables. Again we use SSIS now and we will continue to use SSIS for the daily load into the Reporting server but I'm using replication to get the data to the application database server. – Kyle Johnson Aug 04 '16 at 01:15