Transfer Data From Remote SQL Server

Hi All:

I'm looking for some advice. We have multiple remote SQL Server 2005 systems at various clients that all contain the same WorkTrack database. We need to send this data on a daily basis from all of these remote servers to our main server (ChemWeb/Spindle) in our corporate office. The thing is that we need to do this over standard HTTP - no VPN connection, etc.

So I'm thinking of using SQL 2005 Web services to do such. Here's my plan (the name of the Database on the client side and server is WorkTrack, and SDP is the acronym I am using for a Spindle Data Packet which contains the data to be updated from the client)

1. Every day update a WorkTrack_SDP database on the client server that will contain all of the data from WorkTrack (name of Spindle Client Database) that has changed since the last SDP transfer. This WorkTrack_SDP database will only contain the tables from the WorkTrack database that are necessary for transfer to the ChemWeb/Spindle server

2. The WorkTrack_SDP database will have the same schema as the WorkTrack database with one additional Customer_No column in each table.

3. All of the tables in WorkTrack_SDP database will be truncated before insertion of updated data.

4. An acknowledgement of the previous SDP transfer and ChemWeb/Spindle Server update will be required before this process will proceed.

5. There will be an additional table in the Client WorkTrack database to track SDP transfers.
a. Date/Time range of updated data of each transfer
b. When the transfer was sent
c. When the transfer was completed.

6. Create SPROC’s on the ChemWeb/Spindle server to serve as SQL Endpoints (Web Services) to update/insert data from client systems to WorkTrack_SDP database on the ChemWeb/Spindle Server.

7. Create SPROC on ChemWeb/Spindle server that will transfer data from WorkTrack_SDP into WorkTrack


Any thoughts & comments are greatly appreciated.

TIA,

Ken Blum