>>
>>In the case of large tables, one in FoxPro (600K recs) and the other in SQL, and you need to join them in your separate ADO.NET connections wouldn't you pretty much have to pull all the data over from both before you have them in object lists and can use LINQ?
>>
>>And taking this one step further to our real scenario this is all in WCF on one server serving many, many clients. Wouldn't this business of pulling in each table for a join blow out the server and kill performance completely?
>
>Yes, and yes. (although you might be able to filter the results from each side first depending on your join conditions or where clauses to get the amount of data down slightly).
>
>You're REALLY going to be much happier if you can get all of the data into SQL Server. What I've done in similar situations is to either just schedule a job to drop/recreate the data in SQL nightly (in cases where having yesterday's data was OK), or to do an initial load of the data into SQL and then modify the VFP side to update both the VFP tables and the SQL table at the same time to keep them in sync.

Thanks, Paul.

We're exploring, right now, the possibility of replicating the FoxPro data to SQL in as close to real time as possible.

We're thinking of adding Insert, Update and Delete triggers on every critical table and that would insert a record into a queue table with type of activity (I, U, or D), table name, record number and time inserted. Then we'll create a separate VFP application that moves this queued data to SQL in the order it arrived. Of course, we'll have to go to the original table and locate the record by, unfortunately, recno, instead of a PK since these tables really don't have a PK.

The source FoxPro db is a third party application and we want to minimize our footprint by having the trigger business as simple as possible to avoid crashing it in the event of some strange unhandled error. Having the queue table will also allow us to resend records that didn't go for reasons unknown or known like the SQL server was down.

But, I guess I have to do an initial load first.