Re: Advice Needed: Asynchronous Replication/Mirroring to central o

Inline…

"Hilary Cotter" wrote:

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.

One way for now. In a year or more? Maybe both ways.

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.

Given that we’re essentially mirroring each remote database to its own
database at the central location, there’s no filtering. Volume is still TBD.
I did realize, though, that at least it won’t be all 300 connecting at the
same time due to geographic distribution across time zones.

* 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.

Excellent. Thanks!

* 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.

I was hoping that was the case.

* What about those locations 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.

I’m thinking we may just have to do a manual backup at remote/restore at
central.

* 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).

From remote (MSDE) to central (SQL) so transactional is right out. Our
choices appear to be merge replication or log shipping at the moment and that
doesn’t allow for the future requirement of potentially sending data back.

* 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


Thanks again. You’ve validated some assumptions and answered some of my
questions. It seems that the biggest challenge remaining is the offline
servers. If I understand correctly, it would seem that there is no way to
extract some sort of merge file/data to a hardware medium and then upload it
to the central server separately, correct?

.

Relevant Pages

  • Re: DNS Forward lookup problem - now having problems with a period
    ... How did you set the replication scopes in the zone's properties in DNS on ... > each DNS server? ... to the remote 10.0.2.3 server, which runs on cable (we are working on ...
    (microsoft.public.windows.server.dns)
  • Re: Advice Needed: Asynchronous Replication/Mirroring to central o
    ... Looking for a SQL Server replication book? ... geographically-dispersed remote locations. ...
    (microsoft.public.sqlserver.replication)
  • Re: what new 2008 AD features may I want to consider for this?
    ... If you have large numbers of users at remote sites or if your vpn connection is unreliable, then this is a perfect candidate for a dc at the remote location. ... If you have remote dc's setup then you need to ensure the bandwidth is available for replication and while setting this up the DS replication doesn't consume the bandwidth available. ... this active directory deployment is only to support a deployment system, which is using WDS and DFS-R, both of which require active directory. ... There is a 'central' server where all edits to anything will be done from, each other location will have a deployment server with a replica of this file share. ...
    (microsoft.public.windows.server.active_directory)
  • Re: Remote site not replicating after ISA upgrade
    ... Both ISA and AD are configured in hub and spoke layout. ... server here in our main office. ... I can also remote desktop from the home into ... replication traffic that is being blocked by ISA. ...
    (microsoft.public.windows.server.active_directory)
  • RE: 3 sites + VPN + AD servers + exchange 2003
    ... separate sites for your Central Office and each Remote Office. ... do the remote sites have their own internet connection? ... Mail Connector on each Exchange server to facilitate that. ...
    (microsoft.public.windows.server.general)