5

I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting and analysis purposes (a one way transfer).

Table structures are the same in all databases. Note that all my databases reside on the same server, except the central database, which is on a separate server. I have evaluated a couple of options. My current thinking and related questions are below:

  1. SSIS

    With SSIS I can do data transfer from the source to the destination databases; however, there is no change tracking. I have to track the changes myself and then extract and push the data manually. Apart from this I will end up creating a number of control flows and related data flows as the data sources are different which would be hard to manage in future.

  2. Replication

    Replication has change tracking, so the data transfer would be quick enough. However, I have to create those many publishers against each database. I am afraid server performance would be impacted due to this. I am also not sure which replication type to use, or if any performance issues could be handled with a better configuration. I am okay with a nightly update and data transfer need not be real time.

How should I proceed from here?

  • 1
    What version of SQL Server are the databases hosted on? – Shawn Melton Sep 4 '15 at 12:12
  • Are you planning on copying all of the data from all 8 databases into the one database, just a small part of each one, or just aggregate data? – Ste Bov Sep 4 '15 at 12:58
  • @ShawnMelton I am using SQL Server 2008 R2 – user3530778 Sep 4 '15 at 13:04
  • @SteBov I am going to copy data from specific tables. Most of the case I am copying all the data. However in certain cases I may have to apply filters. – user3530778 Sep 4 '15 at 13:06
  • What edition of SQL Server 2008 R2 are you using? Standard, Enterprise, other? – Solomon Rutzky Sep 4 '15 at 13:53
3

I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting and analysis purposes (a one way transfer).

Table structures are the same in all databases.

REPLICATION : If the objects (tables) you are replicating are not undergoing frequent schema changes then you can use many-to-one replication using T-Rep (this is a very thorough answer that I wrote with scripts and end result), provided you can slightly modify your table schema. This will give you a near-to-realtime data transfer to your central server.

SSIS : If you dont need a near-to-realtime data transfer, then you can look into SSIS - Incremental load using Lookup transform as described in Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services. ALso, refer to this excellent series : Stairway to Integration Services for more details. Alternatively, you can test out Change Data Capture (SSIS) (as @srutzky suggested).

0

Use SSIS. Use BIML to create your SSIS packages. Turn on CDC or CT, whichever is appropriate for your case.

Replication is wonderful in concept but introduces complications in practice. The other is a more resilient approach.

  • Perhaps you should also mention Change Data Capture (SSIS) ? And also any restrictions on the features that you are suggesting. – Solomon Rutzky Sep 4 '15 at 13:54
  • 1
    As srutzky notes, I think adding pros/cons to both approaches (specifically the complications introduced by replication) would be very helpful for the OP to make a decision. I tend to agree with the SSIS approach, but if it were something that I was questioning I would not take the answer without some examples and reasons for the suggestion. – mskinner Sep 4 '15 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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