Physically Distributed Database Design

[BenFinkel]

Howdy all,

I'm going to be developing an application that will run at many physically distributed sites. This application will regularly need to commit it's data to a centrally located home office. Data is going to be pushed both ways (from client to home and home to client) and will need to be fairly regularly (once an hour at least).

The sites will have internet access, but not necessarily broadband. I expected the backend database to be SQL Server.

What would you guys recommend for this data transfer? Does SQL Server offer an efficient web-based replication? I'm not looking for an exact answer, just a discussion about possible options.

Thanks for your input!

--Ben

[Ecniv]

Sql server on an internet server. Asp frontend. All data would then be shared from there, and if needed you could use a timer on the page to refresh content (possibly).

Or connect via a vb/vb.Net application in a similar way.


Vince

[BenFinkel]

Problem is, I might not have internet connectivity the entire time. The distributed site may only have dial-up and have to connect twice a shift or something of that nature. That would kill my once an hour I realize, but that's the reality of it.

Also, each site is going to have to sync up with a number of handhelds, so the data needs to be stored at a computer at each site so that they are not dependent upon an internet connection to run. Particularly if the internet goes down at the home office we need everyone to be able to function regardless.

--Ben

[vb_dba]

If you are going to have a SQL Server located centrally, you could utilize MSDE on the client machines and use replication to push/pull changes to/from the SQL Server.

I've never done it before, but I think that by utilizing SQLDMO in you application, you could set this up to do exactly what you want.

[BenFinkel]

We're looking into some form of SQL replication over the Internet right now.

I just want to make sure that it's the right solution. It needs to be bandwidth efficient, allow for multiple locations in both directions (we will be a backup site for our customer in case the net connection at the home office goes down, we can still capture all of the day's transactions), and be intelligent enough to decide which database owns particular data in the event of conflicting changes.


As it stands, the client machines need to be fully licensed versions of SQL Server so that the Handheld devices (SQL Mobile) will sync up to it. We were going to use MSDE until we found out about that little caveat.

Thanks for the advice,

Ben