Keep remote SQL data tables updated

Hello, I need some guidance in the best method to accomplish this task. I have a network with a SBS 2003 server and a SQL 2000 member server in the SBS domain. I have a remotely hosted website at discountasp.net with a SQL 2000 database. The website will host a modified e-commerce kit where corporate clients can order parts. I need to keep the products table on the remotely hosted site as updated as possible. The website orders are placed via email from the commerce kit and the fulfillment department proccesses the order against the SQL server in the SBS domain.

Any ideas on how to keep the data updated on the website?

How updated do you want?|||Within an hour perhaps. I have a remotely hosted sql server at discountasp.net and a sql server in my domain. The hosting provider does not allow dts or replication.|||

I would say create a web service at your hosting provider. Then create a service you can run on your local sql 2000 machine that does the pull/push at intervals. Just make sure that all your important tables have auto-incremented id fields so you know what you are missing since last pull/push.

It sounds like the hosting provider is the master table for orders, and your local sql server is the master for products. Create a trigger on the ISP's order table that records all insert/delete/updates to that table to an auditOrders table that contains an id field (Autoincrement int), a changetype field (char) constrained to 'I','U', or 'D', a ChangeTime field (datetime with default of GetUTCDate()), and a field for every field in Orders. Make a similiar trigger on your local products table going into auditProducts. Then create a webservice at the hosting provider that has 3 methods:

public function GetNewOrders(LastId as integer) As Dataset

public function GetLatestProductChangeID() as integer

public sub SaveProductChanges(ds as dataset)

Make a copy of auditProducts and auditOrders at the other location. To pull changes to orders, select the highest id from your local auditOrders table. Then call GetNewOrders with that id. Take the dataset and record by record (It should be in id order) start a transaction, perform the Insert/Update/Delete on the local table, and then insert the record into the local audit table, and commit the transaction. To push Products, call the GetLatestProductChangeID, then create a dataset containing all the records from the local auditProducts table where the id is larger than the id you got from the webmethod, and call SaveProductChanges with the dataset.

Alternatively from having to do the transaction, and manually record the record into the audit table, you can also copy the triggers from the other side, and it (SHOULD) recreate the record, all except for the ChangeTime which will be different obviously.

|||

Take a look at my Remote SQL data provider, WebSql Data Provider, for secure, efficent access to SQL Server over HTTP, as simple as using the native SQL provider in System.Data.SqlClient namespace.