I have a situation in which I have tables with identical structures on two different SQL databases. The tables contain 5 million plus records. The first database is treated as a production database, and the second database is treated as a work area database.

The production database tables are being updated from many different applications throughout the day.

The work area database is where most of the data is loaded and updated. There are many cleaning/data manipulation processes that take place on these tables which requires this data to be offline, or out of production.

The process is to "checkout" certain tables from production, which disallows any updates to occur while the tables are checked out. Then synchronize the data to the workarea database, and update that data with the cleaned/manipulated data. After the data has been verified, it is then replicated back to the production database during off-line hours, and the tables are "checked in" allowing updates to occur once again.

I'm looking for suggestions on the best way to synchronize the data from the production database to the work area database during operational hours. All of the cleaning/Manipulation is performed via MM.Net C# business objects. So far I have came up with 2 solutions.

1. I'm limited to SQL Server 2000, so I'm using the DTS object integrated through COM interop. This requires a DTS package be built for each table, and executed from code. Which has the drawback of having to maintain the DTS packages along with the tables in the event there are structure changes. This is not uncommon for these tables.

2. I pull the data from the production tables into a data table in C#. Then switch the connection string to the work area and commit the data back to those tables. I may run into memory issues here with the size of the tables. Also, I'm not sure how to tell the data table that all records need to be committed, since it will view the records as unchanged.

Solution 1 will be the most effecient as far as exectuion, but will a lot more to maintain. Also, this is an offline process so, within reason, speed is not the most important concern.

Solution 2 will be alot easier to maintain, since all data access is controled via dynamic SQL, but may to slow to implement even in the offline process.

Any thoughts/suggestions are appreciated.

Josh