Efficient Way to Query Multiple DBs

Dear Experts,
Can you please give me some advice in the direction I should take?

The scenario that I have is 150 different SQL databases that all have data in them.  I would like to run reports and display the data in one database and in this process we are going to need to add primary key when the data is dumped into that 1 master database.  All the procedures are going to be done on server side can you please suggest which direction to take ex. Stored procedures, Views or something else.  The environment that we are running is SQL 2014 Standard. All the tables are the same.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>All the tables are the same.
Ok.  Is your intent to just create the same schema but with a PK 'database' column, and pump data from 150 locations into one?  That can be handled in an SSIS package with a ForEach loop that loops through all 150 datasets, using the same SQL as a source but with a variable to handle what gets inserted into the database column.
Vikas GargAssociate Principal EngineerCommented:

I would suggest your should prepare SSIS package for the same if you need to transfer data only.

SSIS provides you good and structured solution to move data from one database to other.

Its simple and easy to manage is well.
sj77Author Commented:
So if I am understanding correctly, you guys are suggesting to us SSIS to import the data from all the separate databases and make one bulk database? Is that the most efficient way?

Jim: to answer your question, I would like to do that, but I have read that it is a daunting and complex process. Would you agree? Do you know of any tutorials that show how to do what you are talking about?DB model
USE CASE: We know that we have 150 site locations, but to simplify, I will use 2 site locations called location A and location B.

I will create a master DB with a locations table only that will use the PK in that table to link a FK on the Location A and B DBs to display queried data to the end user.

I have enclosed a picture of my initial thought process, but, would not mind any additional suggestions.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

sj77Author Commented:
To clarify, in the image, the End User queries master DB and the flow continues as normal to output to end user. Sorry for the honest oversight, gents.
Jim HornMicrosoft SQL Server Data DudeCommented:
>So if I am understanding correctly, you guys are suggesting to us SSIS to import the data from all the separate databases and make one bulk database?

>Is that the most efficient way?
Yes for now, as there's not a world of details in this question.  Other options would be other ETL tools, PowerShell, an all-T:SQL Stored Procedure solution with a big cursor / FOR loop.

>I would like to do that, but I have read that it is a daunting and complex process
All ETL applications have a steep learning curve, and SSIS is no different, although I've heard that Informatica is easier to use but there is a > $100k price tag with it.
Scott PletcherSenior DBACommented:
Some things to consider:

If you need to reflect row changes as well as new rows, I recommend adding a rowversion column to the original tables to allow you to check for changes easily.

I wouldn't have one process that did 150 dbs.  I'd break it into subprocesses, each with 5 to 30, say, or whatever, dbs in it.  [Personally, I'd probably even invoke a stored proc separately for each db, so that any of them could easily be run on demand.  A common proc in master would prevent you from having to create 150 separate procs.]  That would allow multiple imports to run simultaneously if needed.

Also, you'll want to use a staging table(s) rather than inserting directly into the main table.  That will allow better data editing and reduce data transfer time into the final table.

Finally, I recommend looking at SET READ_COMMITTED_SNAPSHOT ON for the reporting db, so that reporting and loading can function simultaneously.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
Whilst this may not be greeted with enthusiasm, I'd question the wisdom of having 150 db's with the same table structure in the first instance, and then ask why you aren't considering a re-design.
Jim HornMicrosoft SQL Server Data DudeCommented:
Paul - I've been at gigs where they ran that way.  Enterprise front-end app, 150 clients, each with a database whose schema was exactly the same but the database name was the client name.   Login to the app included the client name, where after successful login it pointed to the appropriate db.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.