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.

  • couple of questions - a) will the schema of the tables be changed e.g. adding new columns,dropping, etc. b) Where is the destination server - same datacenter, same region or cross country ? c) how much data is being replicated and how busy is the current PROD server ? Based on these question, I will try to answer your question.
    – Kin Shah
    Commented Jan 21, 2014 at 16:29
  • Hi Kin, sorry missed this post. Answers are a) No schema wont be changed, very very rarely it may change b) Its in the same data centre c)Prod server is quite busy with reads and writes happening 24/7 and the delta of the data is small, but the complete dataset will be quite large in records terms around 5 mil and increasing Commented Feb 5, 2014 at 9:55

1 Answer 1


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.

  • Hi Matan thanks for the information SSIS is running, but it takes 15 mins for the data to transfer and also puts load on the production server. If we use replication, do you think we will have the same performance hit on the production server? Commented Jan 21, 2014 at 11:55
  • Replication will put load on your transaction log and additional load if you also put the distributor there. Generally speaking replication works well, but I recommend testing it before. Do you know how much data needs to be transferred in, let's say, 5 minutes? Commented Jan 21, 2014 at 12:23
  • The SSIS package should execute SQL code that only brings in data that has been changed/added since the last update.
    – datagod
    Commented Jan 21, 2014 at 13:18
  • Hi Matan, the total records in all tables combined is 3.5 million, but at the moment the SSIS looks at the delta. Commented Jan 21, 2014 at 13:33
  • If I were you, I'd take some time to try to optimize the SSIS job, and if it doesn't go well, go to one of the other technologies. Replication is great and should do the job, but it needs to be learned and needs occasional maintenance. Commented Jan 21, 2014 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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