Hi,
I have a merge replication (one publisher/distributor and one subscriber) and I am experiencing latency problems. When I modify some data in one of this servers, it takes about 40 secs to replicate this data. Is it possible decrease this time? I mean, 2 o
r 3 secs would be good.
Thank you
Marcos,
you can decrease the -PollingInterval if you are running continuously, which
makes a big difference. Other ways to optimise are listed in these articles:
http://www.microsoft.com/technet/pro.../mergperf.mspx
http://www.sql-server-performance.co...ion_tuning.asp
HTH,
Paul Ibison
"Marcos Federicce" <Marcos Federicce@.discussions.microsoft.com> wrote in
message news:AAFE1AE8-732F-4E5C-8EA6-24E1AB33BB18@.microsoft.com...
> Hi,
> I have a merge replication (one publisher/distributor and one subscriber)
and I am experiencing latency problems. When I modify some data in one of
this servers, it takes about 40 secs to replicate this data. Is it possible
decrease this time? I mean, 2 or 3 secs would be good.
> Thank you
|||right click on your merge agent, select properties, go to steps, click run
step and a the end of the string type
-PollingInterval 1
This should bring it down considerably, probably within 4s. YMMV
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Marcos Federicce" <Marcos Federicce@.discussions.microsoft.com> wrote in
message news:AAFE1AE8-732F-4E5C-8EA6-24E1AB33BB18@.microsoft.com...
> Hi,
> I have a merge replication (one publisher/distributor and one subscriber)
and I am experiencing latency problems. When I modify some data in one of
this servers, it takes about 40 secs to replicate this data. Is it possible
decrease this time? I mean, 2 or 3 secs would be good.
> Thank you
|||Paul,
The PollingInterval solved my problem. Thank you.
Marcos
"Paul Ibison" wrote:
> Marcos,
> you can decrease the -PollingInterval if you are running continuously, which
> makes a big difference. Other ways to optimise are listed in these articles:
> http://www.microsoft.com/technet/pro.../mergperf.mspx
> http://www.sql-server-performance.co...ion_tuning.asp
> HTH,
> Paul Ibison
>
> "Marcos Federicce" <Marcos Federicce@.discussions.microsoft.com> wrote in
> message news:AAFE1AE8-732F-4E5C-8EA6-24E1AB33BB18@.microsoft.com...
> and I am experiencing latency problems. When I modify some data in one of
> this servers, it takes about 40 secs to replicate this data. Is it possible
> decrease this time? I mean, 2 or 3 secs would be good.
>
>
|||Hilary,
It was better than I expected. Data is now merged no more than 2s.
Thank you
Marcos
"Hilary Cotter" wrote:
> right click on your merge agent, select properties, go to steps, click run
> step and a the end of the string type
> -PollingInterval 1
> This should bring it down considerably, probably within 4s. YMMV
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Marcos Federicce" <Marcos Federicce@.discussions.microsoft.com> wrote in
> message news:AAFE1AE8-732F-4E5C-8EA6-24E1AB33BB18@.microsoft.com...
> and I am experiencing latency problems. When I modify some data in one of
> this servers, it takes about 40 secs to replicate this data. Is it possible
> decrease this time? I mean, 2 or 3 secs would be good.
>
>
Showing posts with label merge. Show all posts
Showing posts with label merge. Show all posts
Monday, March 26, 2012
Latency in merge replication
Labels:
database,
distributor,
experiencing,
latency,
merge,
microsoft,
modify,
mysql,
oracle,
publisher,
replication,
server,
sql,
subscriber
Friday, March 23, 2012
Last time synchronized
I use SQLCE2.0 and merge replication.
Is there a way to query SQLCE to find the time of the last synchronization without manually tracking the time myself.
Thank you.
The following query works with SQL Mobile 2005 and later:
SELECT LastSuccessfulSync FROM __sysMergeSubscriptions WHERE Publication = " + strPublicationName
- not sure it works with SQL CE 2.0, though.
Wednesday, March 21, 2012
Last synchronisation date for a (merge)-publication
Hi!
Is there any table where I can find the last updated (last synced) date
and time for a subscription?
I am trying to warn a user if his/her subscription did not synchronise
since an xxx number of days but I can't locate the date/time for the
last successfull synchronisation.
Thx,
Ferry
you can query the history tables in the distribution database.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Ferry" <ferryswi A@.T ferryswi D.O.T tmfweb D.O.T nl> wrote in message
news:mn.a56e7d63bfca7a9f.23024@.TferryswiD.O.Ttmfwe bD.O.Tnl...
> Hi!
> Is there any table where I can find the last updated (last synced) date
> and time for a subscription?
> I am trying to warn a user if his/her subscription did not synchronise
> since an xxx number of days but I can't locate the date/time for the last
> successfull synchronisation.
> Thx,
> Ferry
>
Is there any table where I can find the last updated (last synced) date
and time for a subscription?
I am trying to warn a user if his/her subscription did not synchronise
since an xxx number of days but I can't locate the date/time for the
last successfull synchronisation.
Thx,
Ferry
you can query the history tables in the distribution database.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Ferry" <ferryswi A@.T ferryswi D.O.T tmfweb D.O.T nl> wrote in message
news:mn.a56e7d63bfca7a9f.23024@.TferryswiD.O.Ttmfwe bD.O.Tnl...
> Hi!
> Is there any table where I can find the last updated (last synced) date
> and time for a subscription?
> I am trying to warn a user if his/her subscription did not synchronise
> since an xxx number of days but I can't locate the date/time for the last
> successfull synchronisation.
> Thx,
> Ferry
>
Wednesday, March 7, 2012
Large Table & Merge
I added a large table to our merge pub, 3.4 million rows. Snapshot ran
rather fast, merge agents pushed rows out to 7 subscribers quickly. All
looking ok. Begin testing. Inserts at subscriber not being replicated. Look
at merge agents, change profile to Verbose. They are all displaying 0
inserts, 100 updates, 0 deletes, 0 conflicts.
Run profiler at subscriber and see the following being executed by merge
agent:
exec sp_MSproxiedmetadata 1708016, 'EACC4DE0-9FAC-420A-A4C4-1569768E01CC',
0x3CE7F05C01000000FF, 0x3CE7F05C010000003CE7F05C01...
Is this part of the synch process? Is a 3.4 mill row table WAY TOO BIG to
add to merge?
Any help much appreciated.
Thanks.
Chris
Yes it is. Basically this is the option to minimize network traffic by using
additional storage at the publisher. The publisher tracks where each row has
come from and has to tag the msmerge_contents table with this info.
However, I think you need to use the conflict viewer to understand what has
happened to your inserts.
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
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E058091C-50EB-4D09-8758-2F6A6E7D9785@.microsoft.com...
> I added a large table to our merge pub, 3.4 million rows. Snapshot ran
> rather fast, merge agents pushed rows out to 7 subscribers quickly. All
> looking ok. Begin testing. Inserts at subscriber not being replicated.
> Look
> at merge agents, change profile to Verbose. They are all displaying 0
> inserts, 100 updates, 0 deletes, 0 conflicts.
> Run profiler at subscriber and see the following being executed by merge
> agent:
> exec sp_MSproxiedmetadata 1708016, 'EACC4DE0-9FAC-420A-A4C4-1569768E01CC',
> 0x3CE7F05C01000000FF, 0x3CE7F05C010000003CE7F05C01...
> Is this part of the synch process? Is a 3.4 mill row table WAY TOO BIG to
> add to merge?
> Any help much appreciated.
> Thanks.
> Chris
rather fast, merge agents pushed rows out to 7 subscribers quickly. All
looking ok. Begin testing. Inserts at subscriber not being replicated. Look
at merge agents, change profile to Verbose. They are all displaying 0
inserts, 100 updates, 0 deletes, 0 conflicts.
Run profiler at subscriber and see the following being executed by merge
agent:
exec sp_MSproxiedmetadata 1708016, 'EACC4DE0-9FAC-420A-A4C4-1569768E01CC',
0x3CE7F05C01000000FF, 0x3CE7F05C010000003CE7F05C01...
Is this part of the synch process? Is a 3.4 mill row table WAY TOO BIG to
add to merge?
Any help much appreciated.
Thanks.
Chris
Yes it is. Basically this is the option to minimize network traffic by using
additional storage at the publisher. The publisher tracks where each row has
come from and has to tag the msmerge_contents table with this info.
However, I think you need to use the conflict viewer to understand what has
happened to your inserts.
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
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E058091C-50EB-4D09-8758-2F6A6E7D9785@.microsoft.com...
> I added a large table to our merge pub, 3.4 million rows. Snapshot ran
> rather fast, merge agents pushed rows out to 7 subscribers quickly. All
> looking ok. Begin testing. Inserts at subscriber not being replicated.
> Look
> at merge agents, change profile to Verbose. They are all displaying 0
> inserts, 100 updates, 0 deletes, 0 conflicts.
> Run profiler at subscriber and see the following being executed by merge
> agent:
> exec sp_MSproxiedmetadata 1708016, 'EACC4DE0-9FAC-420A-A4C4-1569768E01CC',
> 0x3CE7F05C01000000FF, 0x3CE7F05C010000003CE7F05C01...
> Is this part of the synch process? Is a 3.4 mill row table WAY TOO BIG to
> add to merge?
> Any help much appreciated.
> Thanks.
> Chris
Friday, February 24, 2012
Large number of records in MSMerge_GenHistory
My MsMerge_GenHistory has too many records (currently 1.6 million rows) and I
can't get it to go down.
One of my Merge publications accidentally had Subscription expire in XX days
set to 70 days. My MsMerge_Contents and GenHistory filled up to 1.6 Million
and 1.9 Million records before I caught the error. I have corrected the
Parameter and have been able to get my msmerge_contents down to 28 K rows. I
can't get MsMerge_GenHistory to come down.
I don't want to set every publication that I have to reinitialize. The
publication that had the expiration days parameter problem is a remote
subscriber with a laptop in the field and I don't have access to it on a
daily basis. It is causing me problems with other publications with
subscribers getting unable to process GenHistory messages. I have all of my
remote subscribers set with a QueryTimeout of 4000.
What can I do?
Thank You
Steve
One additional thing.
I noticed that most all of the records in msmerge_genhistory have a pubid of
NULL. A few have a with what looks like a valid guid. Are these records the
problem and can I simply delete them?
Thanks
"Steve" wrote:
> My MsMerge_GenHistory has too many records (currently 1.6 million rows) and I
> can't get it to go down.
> One of my Merge publications accidentally had Subscription expire in XX days
> set to 70 days. My MsMerge_Contents and GenHistory filled up to 1.6 Million
> and 1.9 Million records before I caught the error. I have corrected the
> Parameter and have been able to get my msmerge_contents down to 28 K rows. I
> can't get MsMerge_GenHistory to come down.
> I don't want to set every publication that I have to reinitialize. The
> publication that had the expiration days parameter problem is a remote
> subscriber with a laptop in the field and I don't have access to it on a
> daily basis. It is causing me problems with other publications with
> subscribers getting unable to process GenHistory messages. I have all of my
> remote subscribers set with a QueryTimeout of 4000.
> What can I do?
>
> Thank You
> Steve
|||What will happen if I run this with the Reinitialize Subscribers set to
False? I am trying to not reinitialize all of my subscribers.
I have found that most of my genhistory records, the pubid field is NULL.
Are these the problems and can I simply delete these?
I have a second question about a recommended max number of publications. I
have a database with 40 different publications . Each publication has 138
articles. Between all of these articles, there are in the neighborhgood of 1
million total records published. Some publications have multiple subscribers,
most publications have a single subscriber with all columns published and
static row filters. Most of these subscribers are remote users traveling. Is
this too publications? This topology has saved us from a system wide
reinitialize a couple of times when a particular subscriber has trouble or
simply will not sync in a reasonable timeframe (we have most of our
publications set to expire in 20 days).
Thanks for the help!
Steve
"Paul Ibison" wrote:
> Steve,
> have a look in BOL at sp_mergecleanupmetadata - you could run this to do a
> manual cleanup of the metedata.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
can't get it to go down.
One of my Merge publications accidentally had Subscription expire in XX days
set to 70 days. My MsMerge_Contents and GenHistory filled up to 1.6 Million
and 1.9 Million records before I caught the error. I have corrected the
Parameter and have been able to get my msmerge_contents down to 28 K rows. I
can't get MsMerge_GenHistory to come down.
I don't want to set every publication that I have to reinitialize. The
publication that had the expiration days parameter problem is a remote
subscriber with a laptop in the field and I don't have access to it on a
daily basis. It is causing me problems with other publications with
subscribers getting unable to process GenHistory messages. I have all of my
remote subscribers set with a QueryTimeout of 4000.
What can I do?
Thank You
Steve
One additional thing.
I noticed that most all of the records in msmerge_genhistory have a pubid of
NULL. A few have a with what looks like a valid guid. Are these records the
problem and can I simply delete them?
Thanks
"Steve" wrote:
> My MsMerge_GenHistory has too many records (currently 1.6 million rows) and I
> can't get it to go down.
> One of my Merge publications accidentally had Subscription expire in XX days
> set to 70 days. My MsMerge_Contents and GenHistory filled up to 1.6 Million
> and 1.9 Million records before I caught the error. I have corrected the
> Parameter and have been able to get my msmerge_contents down to 28 K rows. I
> can't get MsMerge_GenHistory to come down.
> I don't want to set every publication that I have to reinitialize. The
> publication that had the expiration days parameter problem is a remote
> subscriber with a laptop in the field and I don't have access to it on a
> daily basis. It is causing me problems with other publications with
> subscribers getting unable to process GenHistory messages. I have all of my
> remote subscribers set with a QueryTimeout of 4000.
> What can I do?
>
> Thank You
> Steve
|||What will happen if I run this with the Reinitialize Subscribers set to
False? I am trying to not reinitialize all of my subscribers.
I have found that most of my genhistory records, the pubid field is NULL.
Are these the problems and can I simply delete these?
I have a second question about a recommended max number of publications. I
have a database with 40 different publications . Each publication has 138
articles. Between all of these articles, there are in the neighborhgood of 1
million total records published. Some publications have multiple subscribers,
most publications have a single subscriber with all columns published and
static row filters. Most of these subscribers are remote users traveling. Is
this too publications? This topology has saved us from a system wide
reinitialize a couple of times when a particular subscriber has trouble or
simply will not sync in a reasonable timeframe (we have most of our
publications set to expire in 20 days).
Thanks for the help!
Steve
"Paul Ibison" wrote:
> Steve,
> have a look in BOL at sp_mergecleanupmetadata - you could run this to do a
> manual cleanup of the metedata.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
Labels:
accidentally,
database,
icant,
merge,
microsoft,
million,
msmerge_genhistory,
mysql,
number,
oracle,
publications,
records,
rows,
server,
sql
Subscribe to:
Posts (Atom)