Update data warehouse over web

[franklin1232]

We have 36 stores each with their own SQL 2000 server. I want to send data back to the coporate office every night. I wanted to modify the information slightly before sending it in and want it to be secure. I have considered a few different options, but was looking for advice. What is the most stable: DTS, Replication, Backup restore, Custom app? Looking for experiences.

Thanks

 

[nomadicalloy]

replication would be the best method. what kind of pre process do you need before sending the data?

SQL Help Blog

http://www.xpherion.com/blog.asp

 

[franklin1232]

Some of the imformation needs to be coded for accounting. Other things need to be checked to ensure the manager properly closed out the store for that day. I would prefer not to send information until the store manager has completed all transactions. That is suppose to happen before store close, but sometimes isn't completed until morning. Can I control replication? If I write the a custom end of day procedure how would I force replication after it has successfully completed?

 

[mrdenny]

You can force replicaiton to start by starting the distribution job using the sp_start_job procedure. Replication is controlled via a regular job.

In order to secure the data, you'll need to do one of two things.

1. Setup VPN connections from the stores to the corporate office.
2. Setup the SQL Servers to use SSL to send the data.

I would setup the VPN connections my self, as this will not place any additional load on the SQL Servers.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)