Replication

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Phil H - 23 Mar 2007 04:18 GMT

How does replication work is Access ? I think MSsql also have replication,
do they work pretty much the same ? What is a typical scenerio where
replication is helpful or used ?
Thanks

Reply to this Message

John W. Vinson - 23 Mar 2007 06:28 GMT

>How does replication work is Access ? I think MSsql also have replication,
>do they work pretty much the same ? What is a typical scenerio where
>replication is helpful or used ?
>Thanks

Check out the FAQ:

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

JET replication and MS-SQL replication are quite different, because JET is a
file-server system and SQL is a true client-server system. The end result is
similar but the details are quite different.

Typically one would use replication if a database must be shared - and kept
synchronized - on two or more computers which are not (or not always)
connected by a network; for example, a user might have a database on a laptop
while travelling, but the data on the laptop must be kept in synch with the
home-office data.

It's a pretty complex subject; there's a whole newsgroup -
microsoft.public.access.replication - devoted to it.

John W. Vinson [MVP]

Reply to this Message

David W. Fenton - 23 Mar 2007 14:39 GMT

>>How does replication work is Access ? I think MSsql also have
>>replication, do they work pretty much the same ? What is a typical
[quoted text clipped - 3 lines]
>
> http://support.microsoft.com/kb/282977/en-us

You'd also want to look at the white papers, but be careful -- the
white papers have a lot of overly-optimistic claims for
replication's capabilities. Thankfully, the FAQ corrects a lot of
those.

Another problem is that the Jet replication documentation since the
introduction of Jet 4 uses JRO almost exclusively for its example
code. This is a bloody mistake, as JRO was an abortion created
because of the mistaken push by MS to use ADO with Jet data, so the
JRO library had to be created to provide access to Jet-specific
features.

Nowadays, you should use nothing but DAO for interaction with Jet
data.

However, there is one thing that JRO has that DAO does not, and
that's the ability to initiate an indirect synch in code.

But if you're going to use two libraries instead of one (i.e. DAO +
JRO) you might as well choose a second library that's more versatile
than JRO, and that would be Michael Kaplan's TSI Synchronizer:

http://trigeminal.com/replication/synch.asp?1033

The TSI Synchronizer provides access to all the features that you
get with Replication Manager. It is the only way outside of ReplMan
to do a MoveReplica (and the only way to do it in code), for
instance. It provides much more functionality than JRO does, so I
see no reason to use JRO at all.

However, it's been discovered that A2K7 *breaks* DAO synchronize
commands, so if you're running an A2K3 app in A2K7, then you may
have to convert DAO synch commands to JRO until MS fixes this bug
(which they recognize and are considering a hotfix).

[Also, http://trigeminal.com is an essential source for all things
Jet Replication.]

> JET replication and MS-SQL replication are quite different,
> because JET is a file-server system and SQL is a true
> client-server system. The end result is similar but the details
> are quite different.

My understanding is that the SQL Server replication team drew very
heavily on the experience of the team that developed Jet
replication.

> Typically one would use replication if a database must be shared -
> and kept synchronized - on two or more computers which are not (or
> not always) connected by a network;

You should revise that to just "not alwyas connected" because they
have to be connectable at some point for replication with either Jet
or SQL Server to work.

> for example, a user might have a database on a laptop
> while travelling, but the data on the laptop must be kept in synch
> with the home-office data.
>
> It's a pretty complex subject; there's a whole newsgroup -
> microsoft.public.access.replication - devoted to it.

My incomplete Jet Replication wiki is here:

http://dfenton.com/DFA/Replication

I've still not gotten any help from anyone else. Maybe people don't
realize they can edit it if they register?

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message

David W. Fenton - 23 Mar 2007 14:45 GMT

> What is a typical scenerio where
> replication is helpful or used ?

SQL Server and Jet Replication are useful in entirely different
circumstances, and engineered for different applications. SQL Server
replication is mostly there in order to scale large databases, so
you can have muliple SQL Servers serving up the same data.

Jet Replication, on the other hand, is designed mostly for
disconnected users, such as the laptop user who has to edit data in
the field and then come back to the home office and merge here
changes with the office database.

The other use for replication is to provide geographically separate
sites with access to a local copy of the data (when it's not
practical to do it across the Internet or a WAN). However, in the
case of Access and Jet Replication, Windows Terminal Server is a
better solution for these fixed office locations, because then you
don't have to maintain two databases and the process to keep them
synchronized.

Another thing to consider is that with Jet 4 and SQL Server 2000,
something called heterogeneous replication is supported, which
allows a Jet database to be a subscriber to a replicated SQL Server
database. This is more limited than either Jet or SQL Server
replication alone, but allows you to roll out an app to laptops with
an MDB back end that is synchronized with a SQL Server in the main
office.

I don't have any practical experience either with SQL Server or
heterogeneous replication, but I've been doing Jet Replication since
1997. It's an excellent technology, and for the wandering laptop
users, very easy to implement direct replication in code (when the
users return to the main office and connect via LAN). It's much more
complex when users have to synch across the Internet or a wireless
connection. In that case you have to use indirect replication (or
Internet, which I don't do), and that requires lots of specialized
setup on both ends. There are lots of posts in the
microsoft.public.access.replication newsgroup outlining how to do
this, either with or without Replication Manager (which is no longer
available in the developer tools for Office 2003).

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message