asked
How can I extract data from multiple SQL databases(50+) and access them on one centralized database.
I'm new to the forums so please forgive me if this is not in the right location. I have 50+ databases that are all stand alone and either have SQLExpress SQL 2005, 2008, 2008rs. The data that is in these databases is used everyday and is a pain because we need to log into each individual database to check on it. How can I set up my databases to send its data to one centralized database. I would also like to query the individual databases via a centralized one. Is any of this possible? If so, can someone please walk me through the steps, or lead me in the right direction please. This would be so much more efficient for my company, and I would like to bring it to the table. =) Thanks in advance for any help.
There are a number of ways to get your data from multiple servers into one central system. A few things need to be cleared up before making your decision: 1. Are you wanting to centralise the data for reporting/analysis (read-only)? 2. Are you wanting to make changes to the data centrally and push that out to the disparate systems? 3. Are any data changes going out to the systems as a whole or to certain targeted systems? 4. Are the systems all within one active directory domain? 5. How current does the data have to be in the central system? (Real-time/timed delay) 6. What network connections are available between the stand-alone systems and the planned central system? (Fast/slow and constant/intermittent) 7. Could you not just consolidate the stand-alone systems onto a centralised system? 8. Are you just new to this forum, or new to SQL Server in general? (If not new to SQL Server, are you experienced in SSIS, Replication, Service Broker, AlwaysOn?) These questions should help narrow down your options and give you an idea of what else to consider.
1. Yes, read only. 2. No, no changes necessary at this time, but maybe in the future. 3. No, I strictly need to see the data (read-only) in the central location...my goal is to be able to troubleshoot the systems that the data relates to. So once I have the data in one location...then compile it and be able to manipulate it into charts/graphs or other visual aids that will help me see a problem as soon as it happens. Rather than having to remote into 50+ locations and view the system. 4. No, they are ALL on different domains. 5. I'd like the data to be refreshed (pulled) daily or maybe even twice a day. But if real time is as easy/quick than real time would be great. 6. Most of the stand-alones are on a fast lan connections which are constant with maybe a handful of them using GPRS which may be intermitten. The central will be on a fast Lan connection which is constant 7. No, each of the stand-alones are at different customer sites spread across the US 8. I hate to admit it but new to the site as well as SQL Server. I've picked up minimal things as far as changing settings within the SQL Server configuration manager. I hope these answers help...I'm willing to try any number of options. I also though of another option but wanted to finish up on this idea before moving forward with the next idea. Create a .bat file to run a script for the query and then send it to my FTP and then go from there. But again I want to try my first option before moving forward with this. Thanks for your reply and any further help you can provide.
Being new to SQL Server is not a bad thing, finding this place for help is definitely a good step. By the sounds of things, I would suggest looking into what you already partially mentioned; batching up exports from the different sites and passing those to the central system for import/analysis. A good tool to get this done would be Integration Services (SSIS). This is a tool designed to accommodate moving data into and out of SQL Server along with a pretty decent GUI which helps you design the flow and shaping of the data you wish to move. The most important thing will be what @Grant Fritchey mentioned - mapping your data from the external systems into a sensible format for reporting/analysis. If you are lucky, then all the systems have identical schema designs and it is just a task of splitting/identifying each system once they are consolidated in the reporting system. Start small and get one part of one location working and go from there. Feel free to ask for help here along the way, there are a few SSIS experts on here who will be able to help.
All the access issues are going to be challenging, getting data from all over the country from disparate networks, etc., but I think the bigger challenge might be at the data structure on your reporting system. Do all the databases have uniqueid or natural keys for their primary keys? If not, you're going to have to build a mechanism that allows you both maintain the keys from the disparate systems so you can match their data into your central warehouse, but also so that you can still uniquely identify rows in order to maintain relationships in places where two different systems might have the same key but different values. This means your structure will have to be different from theirs and your data load process will have to take this into account when you do the imports.
10 People are following this question.
