Merge or Transaction Replication?

I have a branch office I would like to connect to our database, which is
fronted by an Access ADP project. I'm having problems with security (the old
runtime 17), but in other small tests it seems the system is very slow. I
guess I'm not really that surprised.
I am considering using replication to set up a second server. Our workflow
is fairly simple. There are many tables that are effectively read-only, and
could be replicated using any method. There is one table that will see
updates from the remote site, as well as a trigger that fires when that table
is updated (keeping an audit log).
Can anyone suggest the best method for this case? It would seem that Merge
replication would be fine, but the documentation suggests I will have
problems with ACID on merge, and little else. Transactional sounds fine,
there's only about 5 tables that see updates on any sort of daily basis, and
only the one orders table that really gets hit.
However that table uses a auto-incrementing pkey. Will that even work?
Maury
For uni-directional data flow transactional replication will work best.
Identity values will be assigned on the publisher/data source. If you are
replicating to a table which has the identity property on this column you
will need to use the not for replication switch. By default Transactional
replication will not put the identity property on columns on the
subscriber/data destination.
Merge is intended for clients which are frequently offline and when you need
bi-directional replication. With careful partitioning you will avoid the
collisions which will break the d part of ACID.
If you need bi-directional replication you can use bi=-directional
transactional replication.
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
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6016EF86-8F9A-4D0D-9C83-CF08AB06107A@.microsoft.com...
> I have a branch office I would like to connect to our database, which is
> fronted by an Access ADP project. I'm having problems with security (the
old
> runtime 17), but in other small tests it seems the system is very slow. I
> guess I'm not really that surprised.
> I am considering using replication to set up a second server. Our workflow
> is fairly simple. There are many tables that are effectively read-only,
and
> could be replicated using any method. There is one table that will see
> updates from the remote site, as well as a trigger that fires when that
table
> is updated (keeping an audit log).
> Can anyone suggest the best method for this case? It would seem that Merge
> replication would be fine, but the documentation suggests I will have
> problems with ACID on merge, and little else. Transactional sounds fine,
> there's only about 5 tables that see updates on any sort of daily basis,
and
> only the one orders table that really gets hit.
> However that table uses a auto-incrementing pkey. Will that even work?
> Maury
|||"Hilary Cotter" wrote:

> For uni-directional data flow transactional replication will work best.
No good here, one of the tables WILL be bi-directional.

> Identity values will be assigned on the publisher/data source.
This is a bit confusing. In my case I am sharing one updating table. If I
turn this off, does this mean that a new row will always be assigned a
"local" identity, one that might be different on the remote machine?
Maury