January 3, 2008 at 3:56 am
Does anyone have a solution which will allow them to query a number of databases on a number of remote servers (assuming identical schema on each), and pump the results into either a new or existing table on another server/database?
We have the requirement to pull data from many (150+) remote SQL Servers and store it locally for reporting purposes.
Any thoughts?
January 4, 2008 at 7:50 am
One method would be to build 150+ Linked Servers from your centralized SQL Server machine, that uses dynamic SQL to run the same query/stored procedure against all of your 150+ databases. The dynamic sql would run the same query, just changing the FROM clause to "point" to each database as it is executed.
Depending on your needs you can write the results to one large table or split up the writes to many tables however you need.
Happy T-SQLing!
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 4, 2008 at 9:24 am
Another method is to use a lookup table. This is helpful especially if you have a multi-customer environment where you have to be selective in the databases to be included in queries. The lookup table contains server and database names, along with additional flag columns to determine query selectivity. Maintenance is the only drawback to this.
I think the challenging part will be performance and how fast you can query data and pull it into your reporting table. I was faced with this same issue and found performance came to a crawl when trying to insert directly from source database to reporting table. I ended up creating a staging table, identical to reporting table, in each source database, inserted data into staging table, then used SSIS (or DTS if on 2000) to pump data into reporting table. It wasn't pretty, but was a lot faster and got the job done.
January 22, 2008 at 12:38 pm
If you have the option of using replication, create a "Data Mart" as Microsoft and others call them. Your local server will consist of a database that is the subscriber of all of the servers and the distributer. It will pull all the data into the one local database presuming all the other systems are publishers.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply