SSIS v/s Replication v/s logshipping

Question

I have 2 SQL server instances, on different servers. One is Production instance and gets lots of reads and writes daily. Another one is for external applications to use a copy of few of the tables from production sql instance. At the moment we have a SSIS job which does compare and update once in a day from production sql to the external sql server. It takes 15 mins and affects the performance of production sql server. We want to increase the frequency of the data being replicated, so that it is more or less synchronous on both servers. What would be a better approach? increasing the frequency of the SSIS package or creating replication or using log shipping? Any answer is really appreciated. Thanks.

Answer 1

Increasing the frequency of the SSIS package will achieve what you want but at the cost of increasing the impact to the production system (you have already indicated that this is an issue) so this doesn't sound an ideal solution.

Log shipping is probably not a good solution as the secondary database will be unavailable for users while the log backup is being restored, meaning depending on the frequency that you are planning to run this, it would not be a suitable option either.

You could use transactional replication if you are just copying records from a few tables to a reporting database (or similar), it will give you the most up to date data with a pretty minimal impact on performance of the production box. It does introduce a bit of complexity for recovery etc, but if you make sure you read up on and understand the implications it is probably the best solution.