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. 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.
As always, it depends on a lot of factors, but I'll answer like this:
If the SSIS package takes only the deltas from the last run and performs well, I'd keep using the SSIS, since its something that is already working and I assume you are familiar with.
Log shipping and replication will work, but they have drawbacks. For instance, with log shipping you will have to disconnect all of your users when restoring the log backups on the target server.