merge replication with msmq

My company supports sales people with handheld devices
used to track and create orders. They are generally
offline and have a client-side copy of sql server on
their machines. Currently, we are using merge
replication and they log in each night to update our
corporate servers and download pertinent changes.
We will be moving to .NET and I have been looking into
MSMQ and most info seems to be stand-alone (ie not much
info on MSMQ in relation to Replication). My question
is: is it possible to integrate MSMQ with replication
and if so, how? What would the benefits/drawbacks be?
Also, not knowing much about replication, would MSMQ be a
better solution than replication in my situation?
thanks,
Greg
Greg,
you can use MSMQ for queued updating subscribers, as an alternative to a SQL
table (MSreplication_queue). I don't know of any performance/functionality
advantages in using MSMQ, and personally don't use it as it is another
potential point of failure - also MS discourage its use
(http://support.microsoft.com/default.aspx?kbid=830839).
As a general point, queued updating subscribers are recommended to be used
in preference to merge replication when:
Replicated data is mostly read-only at the Subscriber.
Conflicts caused by multiple updates to the same data are infrequent.
You need updates to be propagated on a transaction basis, and conflicts to
be evaluated and resolved on a transaction basis (the entire transaction is
either committed or rolled back).
HTH,
Paul Ibison
|||Queued Updating is good when your number of subscribers is less than 10.
Merge replication was designed from the ground up for mobile subscribers who frequently go off line. MSMQ was designed from the ground up to provide asynchronous messaging. Out of the box it doesn't really do bi-directional replication.
You can use MSMQ to provide a subset of the functionality or merge replication, but it really is the wrong tool for bi-directional asynchronous replication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks To both of you for your replies.
As MSMQ provides asynchronous messaging, would it be the
appropriate solution in the following situation: the
sales force is online most of the time and must send in
orders. Also, they run our app and must receive updates
which are personalized for their application. For
example, our menus are dynamic and each rep's menu
structure is stored in our db; but on a rep's local db,
only their own menu structure is stored. Therefore, a
change to a particular rep's menu structure would only be
pushed down to that particular rep, not all reps. So
basically, 1 rep might be able to get to a particular
function by selecting it in their menu, but another rep
might not see that functionality at all.
thanks,
Greg

>--Original Message--
>Queued Updating is good when your number of subscribers
is less than 10.
>Merge replication was designed from the ground up for
mobile subscribers who frequently go off line. MSMQ was
designed from the ground up to provide asynchronous
messaging. Out of the box it doesn't really do bi-
directional replication.
>You can use MSMQ to provide a subset of the
functionality or merge replication, but it really is the
wrong tool for bi-directional asynchronous replication.
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>.
>
|||Greg,
I'd recommend merge replication as it performs this type of partitioning
well and is vastly simpler than involving MSMQ.
Basically, each member of the salesforce would have their own SQL Server
(MSDE?) installed against which your client application functions. Each of
these nodes exists as a subscriber to the central publisher. Data would be
partitioned according to salesperson based on the merge agent's -HOSTNAME
parameter.
HTH,
Paul Ibison
|||Paul,
Wouldn't this require reps to manually run the merge
replication process? (BTW, this is our implementation
now). As I understand, MSMQ works automatically, so
messages are sent and received real-time without any
explicit action by the user, as long as there is a
connection to the server and the server has resources to
process the sent message.
thanks,
Greg

>--Original Message--
>Greg,
>I'd recommend merge replication as it performs this type
of partitioning
>well and is vastly simpler than involving MSMQ.
>Basically, each member of the salesforce would have
their own SQL Server
>(MSDE?) installed against which your client application
functions. Each of
>these nodes exists as a subscriber to the central
publisher. Data would be
>partitioned according to salesperson based on the merge
agent's -HOSTNAME
>parameter.
>HTH,
>Paul Ibison
>
>.
>
|||Greg,
The merge agent can be initiated by them manually, by your program, using
windows synchronization manager or if the reps are always connected to the
network, you can have the merge agent running in continuous mode.
HTH,
Paul Ibison
|||Thanks for all the help Paul.
I guess my last question would be ... would MSMQ only be
preferrable if having a local db on a rep's machine was
impossible for some reason or if the messages/changes
were really only 1 way?
thanks!
Greg

>--Original Message--
>Greg,
>The merge agent can be initiated by them manually, by
your program, using
>windows synchronization manager or if the reps are
always connected to the
>network, you can have the merge agent running in
continuous mode.
>HTH,
>Paul Ibison
>
>.
>
|||Greg,
I agree, although MSDE is essentially freely distributable.
If message changes are only one way, merge can be configured this way (using
the -EXCHANGETYPE parameter).
Cheers,
Paul Ibison