What is the most efficient way to combine multiple MS SQL databases into 1 database?

Posted on 2011-10-11
Last Modified: 2012-05-12
I have a client who's software vendor is switching from a single, multi-company, database to a single company database configuration.  The end result for my client is going to be 60+ separate DBs with the same schema.  They need a solution to combine these 60 DBs back into 1 DB for reporting.  Their software vendor is not providing this solution for them.

Building UNION views is not an option.  MS SQL doesn't like queries that UNION 60+ DBs.  Performance with UNION views is also a problem.

In the past I have written queries that would build a multi-company DB on a nightly schedule.  I'm curious if there is a more elegant solution using replication, or bulk-copy, etc...

Thank you.
Question by:agoodale
    LVL 10

    Expert Comment

    by:Tyler Laczko
    You could do separate selects on each db and append the results together on the software side where you are controlling the data.
    LVL 17

    Accepted Solution

    Replication to a staging Database where that data is then fed into your reporting database would be a path that I would heavily consider.

    Step 1) Define your Reporting database entities, etc.
    Step 2) Create the data feeds/ replication to get the data to a staging area.
    Step 3) Create the data feeds from the staging area into your reporting database.

    If you do it properly, it could be a nearly real-time process.

    Author Comment

    @xDJR1875 -

    The client needs all the tables replicated.  They have over 1000 Crystal Reports written to the multi-company schema.  I can create views in each of the 60+ DBs that would add the company key to make it look like the previous schema.  The question is how to combine those views into the single DB?

    Can you explain a little further how that replication might work?

    For example let's look at just 1 view: "VIEW_A".  I would have 60+ copies of VIEW_A across the DBs.  How would I replicate those into the staging DB?  Wouldn't replication create 60+ objects there?

    Once I have 60+ copies of VIEW_A in the staging DB how would replication combine those into 1 VIEW_A in the final reporting DB?

    LVL 17

    Assisted Solution

    No, in merge-replication, you can have multiple tables feeding into 1 table.
    So you would create the replicated table and the 60+ tables would publish to it. (an example of how this is used is when a hand-held device is used to capture data and then syncronized with the master database. Multiple hand-held devices can sync to the same database using merge replication. )

    This one table that contains your merge-replicated data could then be used as part of your reporting database or you could feed that into the shape you want into another database.

    Review replication in the documentation, especially merge-replication and I think you will get a clearer picture of some of the things that are possible.
    LVL 100

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Author Comment

    no comment

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now