Is replication the right tool in these circumstances?

[Guest]

I am developing an Access 2000 application to handle all the admin for an
international photographic competition. The process of registering all the
entries as they come in over a period of a month or so will be handled by
four or five volunteers using their own PCs at home.

For initial distribution, I am planning to use a combination of the Access
2000 Package & Deployment Wizard and WinZip software to create a
self-extracting MDE version of my application. This file will then be made
available for download on my website. This process has been tested and
appears to work fine.

I now need to find the best way to merge the data from each user at regular
intervals and then redistribute the merged tables back to the users (it would
not be a big problem if data entry had to be frozen during this merging
process). The data volumes are not enormous. There are about 6000 separate
entries each year from about 1000 entrants and we would want to retain data
in the live system for about 5 years. The only available methods of data
communication are via the Internet and email. I will probably split the
database into a front end and a back end.

Is replication a valid tool in such circumstances for the back end database?
I've seen comments suggesting that replication only works if the PCs are
interconnected via a high-bandwidth link such as a LAN. Is this correct?

If replication is not a suitable tool, what are the best ways to handle the
merging of 4 or 5 versions of about a dozen tables?

David

 

[John W. Vinson]

Is replication a valid tool in such circumstances for the back end database?

Yes. Distribute the frontend, unreplicated, separately - ONLY tables should be
replicated.

I've seen comments suggesting that replication only works if the PCs are
interconnected via a high-bandwidth link such as a LAN. Is this correct?

No. You can replicate over the internet, or even by mailing CD's.

Get a copy of the Replication FAQ written by MichKa (Michael Kaplan) - I think
you can find it at www.trigeminal.com and/or at the Downloads page on
http://support.microsoft.com; they keep moving things around on the MS site so
you'll do best to search for it there.

John W. Vinson [MVP]

 

[David W. Fenton]

Yes. Distribute the frontend, unreplicated, separately - ONLY
tables should be replicated.


No. You can replicate over the internet, or even by mailing CD's.

No, you can't do the latter. You can use a CD as the initial setup
for a new PC, but from then on, the synchronization must happen in
place, with no copying or moving of replicas at all.

Get a copy of the Replication FAQ written by MichKa (Michael
Kaplan)

There are lots more authors than MichKa.

- I think
you can find it at www.trigeminal.com and/or at the Downloads page
on http://support.microsoft.com; they keep moving things around on
the MS site so you'll do best to search for it there.

My Jet Replication Wiki is a good place to start:

http://dfenton.com/DFA/Replication/

 

[David W. Fenton]

=?Utf-8?B?RGF2aWQgQW5kZXJzb24=?=

Is replication a valid tool in such circumstances for the back end
database? I've seen comments suggesting that replication only
works if the PCs are interconnected via a high-bandwidth link such
as a LAN. Is this correct?

No, that's not even close to being a full answer.

If you use DIRECT replication, then the statement is true.

But if you're using indirect or Internet replication, then it's
false.

I suggest you spend time working through the replication
documentation. There's a link to resources for learning about
replication on the front page of my Jet Replication Wiki:

http://dfenton.com/DFA/Replication/

 

[David Anderson]

David,
Thanks. I know I'm still very near the bottom of a steep learning curve. I
was just looking for an indication that it was worth climbing the hill...

Since Internet-based communication is the only real option open to me, I'm
glad to hear that Internet replication is a viable process.

David

 

[David W. Fenton]

Since Internet-based communication is the only real option open to
me, I'm glad to hear that Internet replication is a viable
process.

I would never use Internet replication. I always use indirect over a
VPN.

 

[David Anderson]

Hmmm. I know very little about VPNs, but I suspect that it might be overkill
for my particular situation. My users are working from home and we are on a
tight budget. What exactly is the problem with Internet replication?

David

 

[David W. Fenton]

Hmmm. I know very little about VPNs, but I suspect that it might
be overkill for my particular situation. My users are working from
home and we are on a tight budget. What exactly is the problem
with Internet replication?

It imposes an outside dependency on the app, that is, IIS has to be
running on your central server. Changes to the IIS configuration can
easily break your Internet replication scheme.

Now, a VPN is also an outside dependency, but it is one that is very
likely to be in place already in many businesses, and is easily
implemented for small businesses using consumer-level router
products and the built-in Windows VPN client.

The difference to me is that it is a dependency much less subject to
changes by someone else, since a VPN has to be reliable and operate
the same way all the time for all users. IIS on a server is subject
to the whims of the server administrator, who may not have your
replicated app as one of his main concerns.

I also am not a fan of IIS. It is too insecure (despite improvements
since the vast outbreak of exploits a few years ago), and I just
don't think it's safe to be running it (particular in a small
business environment where you won't necessarily have an experienced
sysadmin to keep it running properly).

 

[David Anderson]

David,
I've not yet had the chance to make a serious study of replication (I only
started to think about it a few days ago), so I was not even aware that a
server was required! In my naivity, I was hoping that my users could simply
communicate via the Internet with my PC, where the master copy would reside.
We are just a club, not a business, and our budget certainly does not
stretch to server-class software. Does that mean that replication is a
closed book for us?

David

 

[Tony Toews [MVP]]

Hmmm. I know very little about VPNs, but I suspect that it might be overkill
for my particular situation. My users are working from home and we are on a
tight budget.

FWIW there is a software only version which I've been using for years
for downloading my email. Thus, when working on site, my clients
can't snoop on my email. I was feeling paranoid about one particular
client I've since fired.

http://openvpn.net/

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I also am not a fan of IIS.

FWIW I've been running IIS for three or four years now on my
webserver.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

I've not yet had the chance to make a serious study of replication
(I only started to think about it a few days ago), so I was not
even aware that a server was required! In my naivity, I was hoping
that my users could simply communicate via the Internet with my
PC, where the master copy would reside. We are just a club, not a
business, and our budget certainly does not stretch to
server-class software. Does that mean that replication is a closed
book for us?

Every NT-based workstation is already a server, and you can run IIS
on a workstation.

Whether that is a good idea or not is a different question. I
certainly don't think so, and that's one of the main reasons I won't
use Internet replication.

But VPNs are near-ubiquitous, and very easy to implement for small
businesses -- most Internet routers under $100 include built-in VPN
support that can be used with the Windows VPN client. So, I don't
see it as very difficult to implement in comparison to Internet
replication.

The other problem with Internet replication is that most small
businesses don't have a fixed IP address, and this can lead to
terrible problems if the IP address changes (it's like moving a
replica). Of course, to connect to a VPN, it's helpful to have a
fixed IP address, but because the IP address with indirect
replication over VPN is not known or needed by the replication
subsystem, it doesn't break replication itself if the IP address
changes -- it only breaks your VPN. And, of course, you can
subscribe to a dynamic DNS service that would allow you to connect
to your VPN via a host name instead of IP address, and that would be
constantly updated by the dynamic DNS service. That solution would
not fix the problem in Internet replication, though, so that's
another of the deal-breakers for me.

 

[David W. Fenton]

FWIW there is a software only version which I've been using for
years for downloading my email. Thus, when working on site, my
clients can't snoop on my email. I was feeling paranoid about one
particular client I've since fired.

http://openvpn.net/

Thanks for that, Tony. The built-in dynamic IP support is a great
thing, and I think I may switch to it for one of my clients (though
their IP address is very stable, usually lasting 18 months or
longer, but very annoying when it changes!).

 

[David Anderson]

David,
Your comments are certainly making me take a greater interest in a VPN-based
solution but I struggle to understand what you mean by saying that VPNs are
"near-ubiquitous". It's certainly not the case that every business in the
UK, far less every computer user, makes use of a VPN! For this reason, I am
a VPN rookie. Some initial research suggested to me that setting up a VPN
requires a service to be provided by an ISP, so I have just been talking to
my ISP on this subject to see what they have to offer.

The answer was that I could purchase their VPN service for £15,000 per year.
No other form of VPN solution was on offer. After that, it seemed a little
pointless to ask how much it would cost for a dynamic DNS service...

The difference between your "easy to implement" comments and my ISP's
megabucks response obviously indicates that you are not both talking about
the same animal. Can you please identify the primary building blocks (i.e.
hardware, software & ISP services) of a simple VPN solution for 5 people?
I'm not looking for a step-by-step procedure, just a pointer in the right
direction.

All the VPN guidance sites I have found so far seem to be oriented towards
business users with a proper server infrastructure, e.g. enabling employees
to securely access their company's Exchange server from home.

David

 

[David W. Fenton]

Your comments are certainly making me take a greater interest in a
VPN-based solution but I struggle to understand what you mean by
saying that VPNs are "near-ubiquitous".

Most of my clients who have servers already have VPNs in place.

Most of my small office clients who don't have servers have routers
that support VPNs, whether they use that feature or not.

I also have a client with Win2K, which allows direct PC-to-PC VPN
connections (that feature was removed in WinXP for some reason,
unfortunately).

It's certainly not the case that every business in the
UK, far less every computer user, makes use of a VPN! For this
reason, I am a VPN rookie. Some initial research suggested to me
that setting up a VPN requires a service to be provided by an ISP,
so I have just been talking to my ISP on this subject to see what
they have to offer.

A VPN is a "virtual private network" and the ISP plays no role in
setting it up, any more than the routers your VPN connection goes
through would play a role.

A VPN is a layer that sits on top of the Internet and the ISP is
simply irrelevant.

The answer was that I could purchase their VPN service for œ15,000
per year. No other form of VPN solution was on offer. After that,
it seemed a little pointless to ask how much it would cost for a
dynamic DNS service...

If the router your client has supports a VPN, read the instructions
for setting it up and try it out. If it doesn't, find Linksys's
broadboand router selections -- they have all included VPN support
for several years now.

For a software solution, you should look at the free, open-source
software that Tony recommended:

http://openvpn.net/

It includes dynamic DNS support out of the box.

The difference between your "easy to implement" comments and my
ISP's megabucks response obviously indicates that you are not both
talking about the same animal.

Your ISP is unethical, I'd say.

Can you please identify the primary building blocks (i.e.
hardware, software & ISP services) of a simple VPN solution for 5
people? I'm not looking for a step-by-step procedure, just a
pointer in the right direction.

See above.

All the VPN guidance sites I have found so far seem to be oriented
towards business users with a proper server infrastructure, e.g.
enabling employees to securely access their company's Exchange
server from home.

A VPN is a VPN. It's an encrypted tunnel between two IP addresses.
It does not need your ISP. It does not need one end of the tunnel to
be a Windows Server (though, of course, every Windows NT workstation
is a server and can thus serve as a VPN endpoint if necessary).

 

[David Anderson]

David,
In defence of my ISP, let me hasten to say that their salesman immediately
confessed that he was unfamiliar with his company's VPN offering
(http://www.zen.co.uk/IPVPN/Pricing.aspx). The standard starting price of
15,000GBP, which is only available after phoning them, made it instantly
obvious to both of us that it was NOT the the solution for my requirements!

Whether the cost was 15,000 or 15GBP, I'm glad to hear that there is no need
for ISP involvement. The VPN info site that told me otherwise was perhaps
addressing a specialised requirement that is of no relevance to my
situation.

I've interpreted your post as meaning that VPN enabled routers and software
such as the free OpenVPN are the only building blocks required to construct
a VPN and that, if the relevant router(s) already have VPN capability, then
a VPN can be set up at no additional cost. Is this correct? My own router
certainly supports VPNs and I will make a point of reading the relevant
parts of the user manual asap. I'm assuming that my Windows XP PC will be
the focal point of the VPN connection and that I am the one that creates the
VPN, while my users have only to install some sort of VPN client software.
Do the routers at the user sites also need VPN capability?

I'm not expecting you to offer a VPN tutorial via this newsgroup, so if my
mental picture is all wrong just say so and I'll start studying the subject
in more depth before raising any more questions here. Almost everything I
have read on VPNs over the past day or two has been targeted at people who
already possess considerable technical knowledge of IP network theory. I've
got some practical experience of network configuration, but I'm certainly
not a network specialist, so I've still got plenty to learn!

David

 

[David W. Fenton]

I've interpreted your post as meaning that VPN enabled routers and
software such as the free OpenVPN are the only building blocks
required to construct a VPN and that, if the relevant router(s)
already have VPN capability, then a VPN can be set up at no
additional cost. Is this correct?
Yep.

My own router
certainly supports VPNs and I will make a point of reading the
relevant parts of the user manual asap. I'm assuming that my
Windows XP PC will be the focal point of the VPN connection and
that I am the one that creates the VPN, while my users have only
to install some sort of VPN client software. Do the routers at the
user sites also need VPN capability?

Nope -- you've got it right. It's only the "server" site that needs
to offer the VPN capability, and the clients only need a VPN client.
The Windows VPN client is sufficient (though you'll likely want to
use something other than the default settings, which last I checked,
didn't even encrypt the tunnel!).

 

[David Anderson]

Thanks, David. Your assistance is greatly appreciated. A VPN-based solution
is no longer looking quite as fearsome as I once suspected! I'm unlikely to
make a start on this for a week or so, but when I do I'm guessing that I
could run tests between my laptop PC as the client and my desktop PC as the
server and thus avoid involving my users until I know what I'm doing.

David

 

[David W. Fenton]

I'm guessing that I
could run tests between my laptop PC as the client and my desktop
PC as the server and thus avoid involving my users until I know
what I'm doing.

Well, I think you need to have them on opposite sides of your router
to really be able to test it.

 

[Tony Toews [MVP]]

Well, I think you need to have them on opposite sides of your router
to really be able to test it.

I'd agree with that comment. Although you can do a lot of the setup
and initial testing on your LAN. You will need to open a port on the
server router/firewall to allow the incoming connections once you get
that far along on your testing.

Note that my webserver is a fully patched Windows 2003 Server system
with no firewall/router in place other than what comes with Windows
2003 Server. Also note that I hit it via Terminal Server without
going through a VPN which horrifies some people. We also renamed the
administrator account and the two of use have our own admin accounts
we run to do things on it. The administrator account is our backup in
case we forget the password or lock ourselves out.

Oh yes, and it's a 2 CPU Xeon 500 Mhz system with 768 Mb of RAM and
it's loafing along at about 3% CPU usage.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/