Advice Needed: Asynchronous Replication/Mirroring to central offic

I am a VB/.NET/SQL developer that has been tasked with restructuring how our
field offices return data to the central office so some of this "advanced"
SQL Server functionality - like replication - is relatively new to me... and
I need your help, please.
The scenario:
We have installations of our MSDE-powered application installed at over 300
geographically-dispersed remote locations. The server at these remote
locations is on its own internal mini-LAN and not connected to the Internet
full-time but (in most cases, anyway) has the ability to "phone home" nightly
via dial-up. In some cases where dial-up is not an option, our technicians
will occassionally (approx. every 2 weeks) visit the site and retrieve data
using a custom applet to extract a subset of the full database that can then
be sent to the central office from a different location (i.e. the tech's home
or branch office). Now, due to increased reporting requirements, we want to
have a copy of all of the data in the database instead of just a subset.
Hence, the new project...
The variables (distilled):
* MSDE (2000) at the 300+ remote sites
* SQL Server 2000 at the central office
* Central office should, as much as feasible, have a full mirror of the
remote site
* After 8 months of running our application, the MSDE databases are 10 to 20
MB each and will only get larger.
* Remote sites are offline during business hours.
* Data may be uploaded automatically via an on-demand dial-up Internet
connection or manually via copy to external media and upload from a third
location
* As much as possible, the data should be secured as it travels across the
wire. A VPN solution is being reviewed but, as of right now, is not
guaranteed.
The gist of it:
After a solid 10 or so days of research, experimentation and prototyping, I
think that the best solution would be merge replication using the MSDE
servers as the publisher/distributer with a push subscription to the central
office, which will maintain an online copy of each remote database on a
single installation of SQL Server. If dial-up is available, the remote
location would connect nightly and merge changes to the central office.
* In general, will this work (300 publishers connecting nightly to a push
subscription)?
* What is the best way to get the remote server to initiate the dial-up
connection? What about hanging up?
* What happens if the connection gets dropped in the middle of replication?
* What about those schools that can't phone home? How will the "tech grabs a
copy and uploads from elsewhere" scenario work?
* Due to size of the data, I don't think snapshot replication will work and
MSDE can't be a publisher of transactional replication. What about log
shipping? (At the moment, it's a purely one-way transaction.)
* Is there something else that I'm not thinking of? I am open to any
suggestions and happy to answer any question and not afraid to read any
links/documents that might help.
Thanks,
Erik
Do you need data to flow from the remote locations to the head office and
vice versa? Its not clear to me from your description you do. If data only
goes one way you should use transactional replication, if it goes both ways
you will need merge.
The rest of the answers are inline.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Erik with a K" <Erik with a K@.discussions.microsoft.com> wrote in message
news:16416D59-C495-4002-9ECB-94903199298E@.microsoft.com...
>I am a VB/.NET/SQL developer that has been tasked with restructuring how
>our
> field offices return data to the central office so some of this "advanced"
> SQL Server functionality - like replication - is relatively new to me...
> and
> I need your help, please.
> The scenario:
> We have installations of our MSDE-powered application installed at over
> 300
> geographically-dispersed remote locations. The server at these remote
> locations is on its own internal mini-LAN and not connected to the
> Internet
> full-time but (in most cases, anyway) has the ability to "phone home"
> nightly
> via dial-up. In some cases where dial-up is not an option, our technicians
> will occassionally (approx. every 2 weeks) visit the site and retrieve
> data
> using a custom applet to extract a subset of the full database that can
> then
> be sent to the central office from a different location (i.e. the tech's
> home
> or branch office). Now, due to increased reporting requirements, we want
> to
> have a copy of all of the data in the database instead of just a subset.
> Hence, the new project...
> The variables (distilled):
> * MSDE (2000) at the 300+ remote sites
> * SQL Server 2000 at the central office
> * Central office should, as much as feasible, have a full mirror of the
> remote site
> * After 8 months of running our application, the MSDE databases are 10 to
> 20
> MB each and will only get larger.
> * Remote sites are offline during business hours.
> * Data may be uploaded automatically via an on-demand dial-up Internet
> connection or manually via copy to external media and upload from a third
> location
> * As much as possible, the data should be secured as it travels across the
> wire. A VPN solution is being reviewed but, as of right now, is not
> guaranteed.
> The gist of it:
> After a solid 10 or so days of research, experimentation and prototyping,
> I
> think that the best solution would be merge replication using the MSDE
> servers as the publisher/distributer with a push subscription to the
> central
> office, which will maintain an online copy of each remote database on a
> single installation of SQL Server. If dial-up is available, the remote
> location would connect nightly and merge changes to the central office.
> * In general, will this work (300 publishers connecting nightly to a push
> subscription)?
Yes, but it really depends on the volume of data and the amount of filtering
you are doing.
> * What is the best way to get the remote server to initiate the dial-up
> connection? What about hanging up?
http://support.microsoft.com/kb/241149/en-us
It hangs up/disconnects when it is complete.
> * What happens if the connection gets dropped in the middle of
> replication?
Merge replication will pick up where it left off when it connects again.

> * What about those schools that can't phone home? How will the "tech grabs
> a
> copy and uploads from elsewhere" scenario work?
This is a hard one. I think the best thing to do is to stop your sql server
copy the files for the db to a dvd, move them to a server which is connected
to the internet, stop the server, copy them to the location there, start it
up and hope for the best.

> * Due to size of the data, I don't think snapshot replication will work
> and
> MSDE can't be a publisher of transactional replication. What about log
> shipping? (At the moment, it's a purely one-way transaction.)
Log shipping will work, but only in one direction. MSDE can be subscribers
to transactional publication, but not publishers - so which way is the data
moving to the remotes (which will work) or from the remotes (which won't
work).
> * Is there something else that I'm not thinking of? I am open to any
> suggestions and happy to answer any question and not afraid to read any
> links/documents that might help.
> Thanks,
> Erik
|||I would agree that merge replication seems the most suitable.
I'd probably set it up differently to the way you explained. I'd have the HO
as the publisher and each remote site as a pull subscriber. This is easier
to maintain, although you'll need dynamic filters if you want to partition
the data. You can use alternative synchronization partners for those
occasions where the remote site can't see HO. I believe Hilary has posted
the link for the dialup question.
On the other hand, if you can get Standard Edition on each remote site, then
I'd use Transactional Publishers with a central subscriber which performs
much better.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)