Using SQLDataCompare to merge multiple tables

Burkead

Hello,

My apologies in advance for the long topic.

I have an enterprise environment where we run hundreds of remote SQL databases, all of them identical in structure. I was able to use the SQL Data Comparison SDK to write a Windows Service that goes out to each remote database and sync's specific tables back to a local (central) database. It works very well, and has allowed me to essentially create a simple "polling" strategy.

The result, however, are hundreds of local databases being sync'd with hundreds of remote databases. When I want to then aggregate data from all of these local database copies together I have to use dynamic SQL that basically unions all of tables from the various local database copies together to be able to see the whole picture. Something like this:

SELECT 1 as LocationID, *
FROM [LocalDB1].dbo.tblSomeTable1
UNION ALL
SELECT 2 as LocationID, *
FROM [LocalDB2].dbo.tblSomeTable1
UNION ALL
SELECT 3 as LocationID, *
FROM [LocalDB3].dbo.tblSomeTable1

(Please no reminders about using *, this is just for simplicity in the forum.)

So, as you can see, with hundreds of databases - this query isn't the greatest.

Here is what I would like to do, and have been told it is possible thru the API:

I would like to have one local database and have all of the remote tables sync in to that one local database table. I would like to just add a single 'LocationID' field to the beginning of the central database table, and have the API sync the remote table to it while specifying all of the data sync with that LocationID. Kind of a pseudo multi-tenant local DB. I am not sure how to go about doing that, however.

Here is the database sync code I have right now:

//Register for data compare
dbRemote.RegisterForDataCompare(RemoteCnn, Options.Default);
dbLocal.RegisterForDataCompare(localCnn, Options.Default);

//Create a data compare mapping
SchemaMappings mappings = new SchemaMappings();
mappings.CreateMappings(dbRemote, dbLocal);

//Create a data compare session
session.CompareDatabases(dbRemote, dbLocal, mappings);

//Execute the changes
block = sql.GetMigrationSQL(session, true);
BlockExecutor executor = new BlockExecutor();
executor.ExecuteBlock(block, server, database);

Very straight forward.

Could someone point me in the right direction - how I can make all of these remote db tables sync themselves to a single central db table?

I would be very grateful..

Chris.Allen

keeping it simple- I don't yet see why doing something obvious isn't the way forward; meaning cycle through all the names of the databases and execute the same synchronisation code (pointing at the one and only local DB).

Put the names of the databases into an enumerable structure and then use a foreach on this structure.

Good to go?