Consolidating Data from multiple servers
-
Hello all. I need to build a consoliidated table to query on and update it frequently from 19 disparate servers I can connect to. The obvious way is to set up DTS packages to import and append to the tables, but it's so painful to set these up .. and I'm thinking they will be obsolete when we go to 2005.. so I'm wondering, is there a means using TSQL or some other way that I can do this? Bottom line.. trying to take 18 tables and append them into 1 to produce statistical data.
Thanks all!
P.S. ... if this went in the wrong forum.. please advise!
Thanks again.
-
why you don't you output results from each server into a file and then append into your table?
-
We need a little more info to give a good solution.
1. Where are the servers located ? local or remote?
2. How do you connect to them ... over the internet or WAN via VPN?
3. You say you need to "update". Does that mean the derived table needs to be updated? If so, does the source table also need to be updated and worse yet, if you update the derived table can the updated data be discarded when you bring in new data ?
4. Do you want to recreate the derived table every time or just add the new records and what if data changes on the source server .. do you need that change also moved to your derived table
-
The servers are remote on a T1 WAN. No VPN involved. By Update, I mean overwrite the contents of the derived table with the most recent data... replace.. not append.. because we do demographic counts, etc. So, for example, when I go to school X and re-populate their data to the derived table, I want all current records in the derived table to go away and be replaced by the new. In all cases the source table stays untouched and intact.
-
Presumably your "derived table" has a column that indicates which source system the data came from ?
There is a pretty generic pattern that can solve this, and it can be accomplished via linked servers versus DTS. You need a "staging area", which may or may not be a separate physical database on your server.
eg:
-- Prepare the staging table for new records from the remote source
Truncate Table staging.dbo.[SomeTableName]
-- Get all records from the remote source using a linked server and
-- 4-part object naming
Insert Into staging.dbo.[SomeTableName]
Select * From [LinkName].[DBName].[Owner].[SourceTableName]
-- Empty the derived table of all records from the source that's
-- being refreshed
Delete From dbo.[YourDerivedTable]
Where DataSource = [SomeSourceIdentifier]
-- Repopulate the 'derived table' from the staged remote data
Insert Into [YourDerivedTable]
Select [WhateverColumns] From staging.dbo.[SomeTableName]
Everything in sqare braces needs to be customized for each remote source, but the pattern is the same - truncate staging, repopulate staging, delete existing, repopulate
Why a staging area ? It is not strictly needed if the data is clean and maps well, you could just delete the derived table data and insert directly form the linked server. However, you usually find at some point that you need to do some data cleansing and manipulation before bringin the dta in, and the staging area is where you'd do that step.
-
Check out SQL Farm Combine at http://www.sqlfarms.com.
Using this tool you can query all 19 servers in parallel into a single grid results, and dump the results into a table (for example).
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
-
Tom,
If you don't want to use DTS . Here is the approach i can think of
- BCP out the data from all your 19 disparate servers into flat files.
- BCP in the data from the flat files into a stage table.
- Build ur consolidated table using the stage table.
HTH,
Sudheer.
Regards,
Sudheer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply