Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Jan 21 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 –  come2searcher72 Feb 5 at 9:55
add comment

1 Answer

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.

share|improve this answer
    
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? –  come2searcher72 Jan 21 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? –  Matan Yungman Jan 21 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 Jan 21 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. –  come2searcher72 Jan 21 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. –  Matan Yungman Jan 21 at 16:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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