Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

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

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.
2 Solutions
Tyler LaczkoCommented:
You could do separate selects on each db and append the results together on the software side where you are controlling the data.
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
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.
agoodaleAuthor Commented:
@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?

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
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.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
agoodaleAuthor Commented:
no comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now