Re: Advice Needed: Asynchronous Replication/Mirroring to central offic
- From: "Hilary Cotter" <hilary.cotter@xxxxxxxxx>
- Date: Thu, 9 Feb 2006 22:26:39 -0500
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:16416D59-C495-4002-9ECB-94903199298E@xxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: Advice Needed: Asynchronous Replication/Mirroring to central o
- From: Erik with a K
- Re: Advice Needed: Asynchronous Replication/Mirroring to central o
- Prev by Date: Re: 10 times more commands than transactions since adding fields
- Next by Date: RE: Snapshot Replication
- Previous by thread: Re: Replication Conflict Notification
- Next by thread: Re: Advice Needed: Asynchronous Replication/Mirroring to central o
- Index(es):
Relevant Pages
|
