0

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.

come2searcher72
  • 1
  • 1
  • 1

1 Answers1

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.

steoleary
  • 8,968
  • 2
  • 33
  • 47
  • Thanks for the reply. At the moment the total records in all tables combined is 3.5 million, but at the moment the SSIS looks at the delta and updates it. If we use replication, do you think it will take longer? – come2searcher72 Jan 21 '14 at 14:45
  • With replication you create an initial snapshot of all the current records then apply this to the subscriber database (creating the initial snapshot may take a while) once this is done, records are sent immediately to the subscriber as they are written to the log on the publisher, so it will take a bit of time to set up, but then updates will be applied in near real time record by record. – steoleary Jan 21 '14 at 14:47
  • Thanks for the reply again. One last question, do you thing replication will have performance impact on production server? Basically, will it be worse or better in terms of resource consumption compared to SSIS – come2searcher72 Jan 21 '14 at 15:22
  • 1
    I would imagine that unless you have a very high transaction rate that it would be a lower impact on the prod server because it is simply reading the transaction log, whereas the SSIS package will have to evaluate the whole dataset to work out the delta (unless you use Change Data Capture I suppose) – steoleary Jan 21 '14 at 17:01