Merge Replication on SQL Server 2000 -- too slow?

I'm setting up a Merge replication over a very high bandwidth line, and I'm
running into many performance problems.
1. I'm publishing the data one-way from the Publisher to one Subscriber at
this time. Once the data are synchronized, it's taking forever to push delta
data since the snapshot. I changed to high volume Merge profile, and it's
still way behind. Millions of transactions are waiting at the Publisher.
2. After the initial data synchronization, MSMerge_contents at the
Subscriber contains millions of rows. Why is that? There's no activity at
the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
UPDATE data pushing back to the Publisher from the Subscriber. I don't know
why what needs to be updated? There's no application or query running at the
Subscriber. This scares me.
3. Because there's millions of entries in MSMerge_contents table, add/drop
articles from replication causes major problem. What's the best way to
handle this data? I'm replicating 200+ tables and some tables have 10+
millions rows.
Thanks,
Hung
Hi. if you have to do ONE way replication, go for transactional replication
instead; it's way faster than merge replication.
Since it's merge replication, the subscriber db's msmerge_contents table
would grow as every single dml would be recorded into it and would remain
depending upon the retention period u have specified.
Also schedule to defrag the msmerge_contents, msmerge_gen_history,
msmerge_tombstone tables as they are likely to get large.
"Hung" wrote:

> I'm setting up a Merge replication over a very high bandwidth line, and I'm
> running into many performance problems.
> 1. I'm publishing the data one-way from the Publisher to one Subscriber at
> this time. Once the data are synchronized, it's taking forever to push delta
> data since the snapshot. I changed to high volume Merge profile, and it's
> still way behind. Millions of transactions are waiting at the Publisher.
> 2. After the initial data synchronization, MSMerge_contents at the
> Subscriber contains millions of rows. Why is that? There's no activity at
> the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
> UPDATE data pushing back to the Publisher from the Subscriber. I don't know
> why what needs to be updated? There's no application or query running at the
> Subscriber. This scares me.
> 3. Because there's millions of entries in MSMerge_contents table, add/drop
> articles from replication causes major problem. What's the best way to
> handle this data? I'm replicating 200+ tables and some tables have 10+
> millions rows.
> Thanks,
> Hung
>
>
|||well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
pushing it one-way from Production server only. Are you saying that
MSMerge_Contents table at the Subscriber contains all rows from the intial
snapshot as well? If so, can I clear it after the intial snapshot synch
because data are pushing one-way right now from the Publisher? I don't want
or expect to see data merging back from the Subscriber at this point. The
one time I saw data propagating back from the Subscriber, I was scared at
the least and didn't understand why that would be possible.
After the inital data synching from the snapshot, ongoing data synching just
can't seem to keep up. We have a big pipe open between the two servers.
Another scare I had was that I got duplicated data at the Publisher itself.
I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
For that one day, for every row from TableB, there were two identical rows
in TableC. How could this be possible? If the trigger fired on TableA fired
twice for some reason, data from TableB should already be cleared from the
first trigger fire. Merge Replication trigger doesn't move data from TableB
to TableC. I couldn't explain that behavior, and I'm scare to turn
replication back on now. Any idea?
Thanks,
Hung
"T" <T@.discussions.microsoft.com> wrote in message
news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...[vbcol=seagreen]
> Hi. if you have to do ONE way replication, go for transactional
> replication
> instead; it's way faster than merge replication.
> Since it's merge replication, the subscriber db's msmerge_contents table
> would grow as every single dml would be recorded into it and would remain
> depending upon the retention period u have specified.
> Also schedule to defrag the msmerge_contents, msmerge_gen_history,
> msmerge_tombstone tables as they are likely to get large.
> "Hung" wrote:
|||Hi.
The subscriber's msmerge_contents should not contain data from the snapshot
file, it contains transactions after the snapshot.
data being pushed back from subscriber is likely the case with merge
replication and u can't stop this beahvious.
for second prob., you would be getting duplicate records in TableC if it
were also published and trigger also exist at subscriber for TableA.
When record gets inserted in publisher tableC through TableA delete, same
event will fire at subscriber's TableA and will cause the trigger at
Subscriber also insert into TableC.
now when u replicate changes, publisher TableC record merges with
Subscriber's TableC record and you get two records at both sides.
Disable that trigger at subscriber, you will get TableC updated at
subscriber if it's included in replication.
"Hung" wrote:

> well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
> pushing it one-way from Production server only. Are you saying that
> MSMerge_Contents table at the Subscriber contains all rows from the intial
> snapshot as well? If so, can I clear it after the intial snapshot synch
> because data are pushing one-way right now from the Publisher? I don't want
> or expect to see data merging back from the Subscriber at this point. The
> one time I saw data propagating back from the Subscriber, I was scared at
> the least and didn't understand why that would be possible.
> After the inital data synching from the snapshot, ongoing data synching just
> can't seem to keep up. We have a big pipe open between the two servers.
> Another scare I had was that I got duplicated data at the Publisher itself.
> I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
> For that one day, for every row from TableB, there were two identical rows
> in TableC. How could this be possible? If the trigger fired on TableA fired
> twice for some reason, data from TableB should already be cleared from the
> first trigger fire. Merge Replication trigger doesn't move data from TableB
> to TableC. I couldn't explain that behavior, and I'm scare to turn
> replication back on now. Any idea?
> Thanks,
> Hung
> "T" <T@.discussions.microsoft.com> wrote in message
> news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...
>
>
|||"T" <T@.discussions.microsoft.com> wrote in message
news:0AAC406D-BD4B-4F8C-A034-3C454A22AFA5@.microsoft.com...
> Hi.
> The subscriber's msmerge_contents should not contain data from the
> snapshot
> file, it contains transactions after the snapshot.
> data being pushed back from subscriber is likely the case with merge
> replication and u can't stop this beahvious.
>
I'd expect the same thing that the subscriber's msmerge_contents table
should be empty since there's no activity at the Subscriber. But I do get
millions of rows in the Subscriber's msmerge_contents table after the first
initialization. I should have queried back to the source tables to figure
out where these rows in msmerge_contents belong.
I understand about data pushing back from the Subscriber is part of
Merge Replication. In my case, the Subscriber isn't taking any live traffic
from the web or query, so I don't expect the Subscriber to have any data
change to push back to the Publisher. Because after the initialization, the
Subscriber's msmerge_contents contains millions, those entries probably get
pushed back? Again, I don't understand why subscriber's msmerge_contents
would have data. I've tried to clear and reinitialize many times. The one
time where I allowed the Subscriber to push data back, it had millions
UPDATE to upload and with 100 entries batch, that seemed to be eternity.

> for second prob., you would be getting duplicate records in TableC if it
> were also published and trigger also exist at subscriber for TableA.
> When record gets inserted in publisher tableC through TableA delete, same
> event will fire at subscriber's TableA and will cause the trigger at
> Subscriber also insert into TableC.
> now when u replicate changes, publisher TableC record merges with
> Subscriber's TableC record and you get two records at both sides.
> Disable that trigger at subscriber, you will get TableC updated at
> subscriber if it's included in replication.
This is a logical explanation. I don't remember if I allowed Subscriber
to push data back during this time or not. I probably did. Otherwise, it
shouldn't happen. Disabling trigger at the Subscriber would create problem
when both Publisher and Subscriber are Live at the same time. For example,
there's web orders inserting into the Subscriber when it's in production,
then I do want to subscriber's trigger on TableA to move data from TableB to
TableC and upload all data to the Publisher. So, if I disable Subscriber's
trigger, this would create a problem. I thought Merge Replication are
triggers-aware and wouldn't fire twice? It knows that data are pushing from
the Publisher to the Subscriber, therefore, Subscriber's trigger won't fire?
or I misunderstand it. How do I avoid this without disabling Subscriber's
triggers because both servers might be in Production at the same time.
Thanks.
[vbcol=seagreen]
> "Hung" wrote: