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

Merge replication scenario - how to have inventory working properly

Hi,
I'm using merge replication to replicate the Customers, Orders,
OrderDetails and Stock tables from server A to server B.
Everythings works as expected except the stockage level for a product.
Think about this scenario:
1) Initially the stockage level of product 1 is 20 units.
2) Server A creates a new order with 5 units of product 1.
Stock table in server A now has 15 units for product 1.
3) Server B creates another order with 3 units of product 1.
Stock table in server B now has 17 units for product 1.
4) Synchronization takes place, and there is an update conflict in the
stock table for product 1. Server A wants to save 15 and server B wants
to save 17.
Either value is incorrect because the stockage level shoud be 12.
Is there any way to have this working as expected? I have thought of
creating a custom resolver, but I think there isn't a way to get the
stockage level after the previous synchronization in the conflict
handler, substract that value from the current stockage level, do the
same with the data from the other server and combine the values to get
the proper result.
Thanks a lot!
Manu,
you could have a table which shows initial stock (20). After that the
remaining stock is a view which is initial stock - sum of orders and in this
case there won't be any conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Inventory is defined as the number of units in stock - the number of units
sold. The application maintains inventory in server a.
When server a and server b sync orders will have to move up from server b to
server a. A trigger off the orderdetails table can fire and update the
inventory table on server a and keep it in sync, this trigger can be
designed to only fire on actions originating from server b.
Then the problem becomes keeping the inventory table in sync in both
locations. This can be done as a download only article, but it will be
updated with the next sync.
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
"Manu" <manunews@.gmail.com> wrote in message
news:1169553887.302039.76620@.m58g2000cwm.googlegro ups.com...
> Hi,
> I'm using merge replication to replicate the Customers, Orders,
> OrderDetails and Stock tables from server A to server B.
> Everythings works as expected except the stockage level for a product.
> Think about this scenario:
> 1) Initially the stockage level of product 1 is 20 units.
> 2) Server A creates a new order with 5 units of product 1.
> Stock table in server A now has 15 units for product 1.
> 3) Server B creates another order with 3 units of product 1.
> Stock table in server B now has 17 units for product 1.
> 4) Synchronization takes place, and there is an update conflict in the
> stock table for product 1. Server A wants to save 15 and server B wants
> to save 17.
> Either value is incorrect because the stockage level shoud be 12.
> Is there any way to have this working as expected? I have thought of
> creating a custom resolver, but I think there isn't a way to get the
> stockage level after the previous synchronization in the conflict
> handler, substract that value from the current stockage level, do the
> same with the data from the other server and combine the values to get
> the proper result.
> Thanks a lot!
>
|||Thanks for the help.
On Jan 23, 2:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> Inventory is defined as the number of units in stock - the number of units
> sold. The application maintains inventory in server a.
> When server a and server b sync orders will have to move up from server b to
> server a. A trigger off the orderdetails table can fire and update the
> inventory table on server a and keep it in sync, this trigger can be
> designed to only fire on actions originating from server b.
> Then the problem becomes keeping the inventory table in sync in both
> locations. This can be done as a download only article, but it will be
> updated with the next sync.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Manu" <manun...@.gmail.com> wrote in messagenews:1169553887.302039.76620@.m58g2000cwm.go oglegroups.com...
>
>
>
>
>
>

Merge Replication Enumerations issues

we are using sql server CE on a pocket pc for our salesforce that syncs orders with a SQL2000 sp1 server via merge replication. we had an issue where one person tried to sync and got the error message

The process could not enumerate changes at the 'Subscriber'.

i understand this probably means something is out of sync. however, i am trying to determine what exactly is out of sync. any idea how to do this? i tried to enable vergose logging with the replmerge.exe utility at the command prompt, but becaase it is a mobile device, it could not find the server connected. (we tried connecting to sync the mobile device and running the utility at the same time, but it still did not find it as it said it was not a 'registered' server.)

any help is greatly appreciated.

thanks,
tammyWow! I didn't even know they made a CE version of SQL SErver?

Sorry no help..