Centralised database and distributed databases

[Guest]

Hi,

I am relatively new in database administration, and if I could get the
answer to these 'doubts', it would be a great helo.


What is the difference between a centralised database and a distributed
database?

current Scenario

I have a database with around 1o tables. they are not normalised , nor is
there any edited relationship between them. ( for starters). Any way. we
would like to make this database centralised , and have the same copy of the
database at ten other locataons. These locations have access to the
internet, but are not connected to any LAN, or WAN.

I would like to periodically update my database with the latest records
form the ten different locations, and also send them a copy of the updated
database regularly.

What is the best way to go about this?

 

[John W. Vinson]

I would like to periodically update my database with the latest records
form the ten different locations, and also send them a copy of the updated
database regularly.

What is the best way to go about this?

Replication.

It's a complex subject, and should be approached with study and caution.
There's a good replication FAQ at

http://support.microsoft.com/kb/282977/en-us

and a newsgroup, microsoft.public.access.replication.

You'll certainly want to split the database into a frontend with just the
forms, reports, queries and code, and a backend with just the tables; only the
backend should be replicated.

John W. Vinson [MVP]

 

[Guest]

Thanks for your prompt reply. i did go through the FAQ on replication , and
have come up with even more doubts.

The ' client computers' in my case connects to the Outlook Web Access Servers
to download their personal emails.

Will this configuration be enough to implement the replication process in
MS Access 2003. I had read that the replication can be used for
disconnected systems also.

 

[John W. Vinson]

Thanks for your prompt reply. i did go through the FAQ on replication , and
have come up with even more doubts.

The ' client computers' in my case connects to the Outlook Web Access Servers
to download their personal emails.

Will this configuration be enough to implement the replication process in
MS Access 2003. I had read that the replication can be used for
disconnected systems also.

I am not an expert with replication - I've used it following instructions
carefully, but haven't done anything original. I have never used Outlook Web
Access Servers so I have no idea how they would interact.

I'd suggest posting in the Replication newsgroup.

John W. Vinson [MVP]

 

[David W. Fenton]

Replication.

I would disagree. With an always-on Internet connection, you'd be
better off hosting everyone on a single Terminal Server.

The thing that worries me about the outlined scenario is that the
schema is quite obviously not finished, and if you make schema
changes in a replica set, you have to be *very* careful (it has to
be done very granularly, with one schema change and one synch, never
more than one schema change at a time. And the data has to be valid
for the new schema *before* the schema change -- it can't be done in
one step. That is, edit the data to be valid under the new schema,
synch around the entire replica set, implement the schema change and
then synch around the whole replica set again).

 

[David W. Fenton]

beginner in table base design

The ' client computers' in my case connects to the Outlook Web
Access Servers to download their personal emails.

Will this configuration be enough to implement the replication
process in MS Access 2003. I had read that the replication can
be used for disconnected systems also.

OWA has nothing to do with Jet replication at all.

Replication can *not* be used with systems that are *always*
disconnected -- there has to be an occasional network connection of
some type across which the two computers with the replicas being
synched can see each other.

I don't think replication is a good idea for your situation.

 

[John W. Vinson]

I would disagree. With an always-on Internet connection, you'd be
better off hosting everyone on a single Terminal Server.

The thing that worries me about the outlined scenario is that the
schema is quite obviously not finished, and if you make schema
changes in a replica set, you have to be *very* careful (it has to
be done very granularly, with one schema change and one synch, never
more than one schema change at a time. And the data has to be valid
for the new schema *before* the schema change -- it can't be done in
one step. That is, edit the data to be valid under the new schema,
synch around the entire replica set, implement the schema change and
then synch around the whole replica set again).

Thanks for the warnings, David. "Beginner", I suggest you follow David's
advice here!

John W. Vinson [MVP]

 

[Guest]

Thanks again everyone on your input. David, putting aside schema changes for
now, if my database structure were complete, whats my next step. How do I
make the computers located at the different locations talk to each other.
Is Indirect synchornization which is one of the utilities in the replication
manager the answer. Does my replication manager have to sit on an Internet
server??

 

[David W. Fenton]

beginner in table base design

putting aside schema changes for
now, if my database structure were complete, whats my next step.
How do I make the computers located at the different locations
talk to each other. Is Indirect synchornization which is one of
the utilities in the replication manager the answer. Does my
replication manager have to sit on an Internet server??

No. In fact, don't even think about that, as even Internet
replication works only on a standard Windows server with IIS running
(as opposed, say, to a generic ISP's web server).

For indirect replication, you need to have an SMB networking
connection to synch. The easiest way to accomplish this over the
Internet is to use the default Windows VPN client, but it can be
done with any VPN software.

Here are instructions on how to set up indirect replication without
using Replication Manger (which you can't buy any longer):

http://groups.google.com/group/microsoft.public.access.replication/ms
g/3b7167c1462b2d2f

(there should be no word wrap nor spaces in that URL)

An explanation by someone else covering the same ground is found
here:

http://www.vsj.co.uk/articles/display.asp?id=560

The key point to understand is that your local machine has to be
able to see the dropbox of the remote machine and vice versa. The
dropboxes are used to hold messaging files that each synchronizer
reads and applies to the local replica.

 

[Guest]

Thanks a lot for your input. It clears up so much of the confusion.
Appreciate it

 

[Guest]

As an alternative to the proposed use of 'replication and synchronization',
would the following strategy be possible ?

I create a duplicate data base on each of the remote location and then
write code in Visual Basic ver 5.oo to insert any changes to records in to
the duplicate database. I might then write code to send this database file
(.mdb) as email, using the Microsoft Front Page.

At the central location, I download the file from the Web site developed
with Mcirosoft Front Page and then programatically updates my tables in the
central location. Will this work

 

[John W. Vinson]

I create a duplicate data base on each of the remote location and then
write code in Visual Basic ver 5.oo to insert any changes to records in to
the duplicate database. I might then write code to send this database file
(.mdb) as email, using the Microsoft Front Page.

At the central location, I download the file from the Web site developed
with Mcirosoft Front Page and then programatically updates my tables in the
central location. Will this work

It will work... with a VERY large amount of work, much more than implementing
replication.

You're basically talking about reinventing the entire replication process.
Handling autonumbers, conflicting updates to the same records, etc. are tricky
and require sophisticated programming.

John W. Vinson [MVP]

 

[David W. Fenton]

beginner in table base design

I create a duplicate data base on each of the remote location and
then write code in Visual Basic ver 5.oo to insert any changes to
records in to the duplicate database.

If you've never attempted that kind of code, it looks really simple.

But it's actually *extremely* complex. You have to handle inserts,
deletes and updates. And then consider what you do if the same
record is operated on in multiple locations -- which change wins?

It's really a very complicated task. I've only done it in
Master/Slave situations, where one database gets all the changes and
the other one just inherits them, and that was hard enough.

 

[Guest]

Ok, I;m back. i decided to try out the indirect synchronization technique.
I downloaded the Jet4 replication files. I went to the trigeminal site, you
had mentioned to download the TSI synchronizer. But all i found was an
excerpt of a help file. Am I not searching well enough, or is it in another
location. I

 

[Guest]

Sorry I found it !!!

Ok, I;m back. i decided to try out the indirect synchronization technique.
I downloaded the Jet4 replication files. I went to the trigeminal site, you
had mentioned to download the TSI synchronizer. But all i found was an
excerpt of a help file. Am I not searching well enough, or is it in another
location. I

 

[Guest]

Will I be able to use tsi synchroniser with Dial Up Networking? If so , how?
At present , our remote sites do not have VPN.

 

[David W. Fenton]

beginner in table base design

Will I be able to use tsi synchroniser with Dial Up Networking? If
so , how? At present , our remote sites do not have VPN.

Yes. That's the way I did it back in 1998.

I don't know if it still works in current versions of Windows, but
back then you could start a dialup networking connection by using
SHELL() to execute RUNDLL [connectoid], where "connectoid" is the
name of the dialup networking connection (and this is one place in
Windows that is case sensitive).

I'd search the MS Knowledge Base for the correct syntaxt. If you
can't find it, I can dig through my ancient apps and see if the code
still works.

 

[Guest]

Thanks a lot , David ,for each step of instruction you give.. Really gives me
the confidence to carry on!!