Merge replication: subscriber jobs missing.

I didn't actually see this but my customer swears:
Merge replication subscriber sql 2000 sp3a had the jobs disappear from the
agent for about 10 hours and the publisher said it could not connect. This
morning the jobs are back and replication was able to be started manually.
Help.
There was a set of conditions which would cause the replication agents to
disappear. Basically if you restored a published database, and then create a
publication with the same name as the old one, everything would disappear.
By chance, where are the agents running? On the subscriber or publisher?
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
"SQLdba" <burt_king@.yahoo.com> wrote in message
news:770297FF-ADE0-46FA-A235-93D0007C62BC@.microsoft.com...
>I didn't actually see this but my customer swears:
> Merge replication subscriber sql 2000 sp3a had the jobs disappear from the
> agent for about 10 hours and the publisher said it could not connect.
> This
> morning the jobs are back and replication was able to be started manually.
> Help.
|||They run on the publisher. When you say everything would disappear, did it
ever come back?
"Hilary Cotter" wrote:

> There was a set of conditions which would cause the replication agents to
> disappear. Basically if you restored a published database, and then create a
> publication with the same name as the old one, everything would disappear.
> By chance, where are the agents running? On the subscriber or publisher?
> --
> 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
>
> "SQLdba" <burt_king@.yahoo.com> wrote in message
> news:770297FF-ADE0-46FA-A235-93D0007C62BC@.microsoft.com...
>
>
|||Not until I removed some publications.
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
"SQLdba" <burt_king@.yahoo.com> wrote in message
news:E20EC89D-A27E-4C0A-9B2F-4311220DFDDF@.microsoft.com...[vbcol=seagreen]
> They run on the publisher. When you say everything would disappear, did
> it
> ever come back?
>
> "Hilary Cotter" wrote:
|||Two things. First, it's transactional replication, not merge. Sorry for
that mistake.
Second, I was able to see the jobs when I looked at the server and was able
to restart the replication manually.
"Hilary Cotter" wrote:

> Not until I removed some publications.
> --
> 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
>
> "SQLdba" <burt_king@.yahoo.com> wrote in message
> news:E20EC89D-A27E-4C0A-9B2F-4311220DFDDF@.microsoft.com...
>
>

Merge Replication: Missing rows at Subscriber with no conflicts

Hi all,

We are using a mix of SQL 2005 and 2000 servers and our "main" database server is running SQL 2005 x64 (SP2 ver. 3042).

Our system has run perfectly for months, then subsequent to an SP2 update we are seeing several instances where the data record counts are different for several tables among all the servers.

We are using Merge Replication, with no filters and published every 2 minutes.

Any ideas?

TIA,

Michael

I have seen this before with SQL 2000 replication. It not uncommon especially for very high volumn data transfer. Unfortunately I cannot tell you what the issue is or why it happens.

Although I would advise that a 2 minute sync interval is probibly not useful for you at all. You should run the merge agent continuiously with the -continuious flag if you want it to sync this often.

I would change to continuious, reconcile the data then hopefully it doesnt happen again. If you can you would be better moving to an all SQL 2005 scenario.

Martin

sql

Merge Replication Problem Missing rows on subscriber

I have set up Mege replication where a publishing subscriber
subscribes to a central publisher. The publishing subscriber publishes
to other regional subscribers. All Subscriptions are set up as pull
subscriptions using dynamic snapshots. The problem however is that
when the associated application is used and merge replication running
across all machines at 1 minute intervals occurrs randomly some rows
within some of the articles are not recieved at the leaf node
subscribers (No errors are received or conflicts generated) . The
changes always make it out to the publishing subscriber. This
inconsistant state can be corrected by inititaing a reinitialisation of
the subscription for all subscrptions relating to the publishing
subscriber. When this occurrs the original shapshot is reapplied and
all the changes since the last snap shot are applied. The result is
all the records are now transferred. In addition if i use associated
web application to create records within the database and then manually
run each merge agent the data is successfully transferred.
I have tried a lot of differnet things to correct this problem.
(Monitored locks / deadlocks etc). Has anyone else experienced this
sort of behavior?
Look to see if these missing rows show up in the conflict viewer. If so you
may be able to roll these deletes back.
Are you using join filters? This could be a factor. If a parent row falls
out of the filter on the subscriber, it will take all rows in the child
tables out of the subscriber if keep_partition_changes is set to false.
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
"Davec" <dcowie@.ballsolutions.com> wrote in message
news:1138756934.548687.176410@.g47g2000cwa.googlegr oups.com...
>I have set up Mege replication where a publishing subscriber
> subscribes to a central publisher. The publishing subscriber publishes
> to other regional subscribers. All Subscriptions are set up as pull
> subscriptions using dynamic snapshots. The problem however is that
> when the associated application is used and merge replication running
> across all machines at 1 minute intervals occurrs randomly some rows
> within some of the articles are not recieved at the leaf node
> subscribers (No errors are received or conflicts generated) . The
> changes always make it out to the publishing subscriber. This
> inconsistant state can be corrected by inititaing a reinitialisation of
> the subscription for all subscrptions relating to the publishing
> subscriber. When this occurrs the original shapshot is reapplied and
> all the changes since the last snap shot are applied. The result is
> all the records are now transferred. In addition if i use associated
> web application to create records within the database and then manually
> run each merge agent the data is successfully transferred.
> I have tried a lot of differnet things to correct this problem.
> (Monitored locks / deadlocks etc). Has anyone else experienced this
> sort of behavior?
>
|||Thanks for the reply Hilary I really appreciate your help,
I am using Join filters with @.keep_partition_changes = N'true'.
There are no conflict tables
The problem seems to be that when synchronization bewteen my lowest
level leafnode subsciber and regional publisher occurr
the merge agent when enumerating pending changes does not pick up the
full set of data that should be delivered to the subscriber based on
the join filter.
I have managed to narrow down the sequence of events that cause the
problem i am experiencing
This is the Replcation topology i have used
CentralPublication ->Regional Publisher 1 (Global Subscription) ->
Subscriber 1 (Local Subscription)
In Step 1 My web application runs against the Central Publisher
database effectively creating rows of data in a number of tables (all
articles of the publication)
Step 2 - Replication takes place between central publisher and the
regional publisher via a global pull subscription. Rows are filtered
via Join filter to the regional Publisher. This seems to be operating
as i would expect
Step 3 - Replication then takes place between the regional Publisher
and the Regional Subscriber. (The regional Publisher republishes a
filtered set to the regional subscriber) Investigation of the
replicated data at the Central publisher is also correct at this stage
Step 4 - Perform mode transactions via web application such that when
synchronisation between central publisher and regional publisher take
place the join filter now includes some of the rows created in step 1
but not part of the filtred data in step 2
Step 5 - Merge Agent executes synchronizing between central publisher
and Regional Publisher. Inspection of the regional Publisher database
also is shown to be correct
Step 6 - Merge Agent executes on regional Subscriber to sync Regional
subscriber with Regional Publisher. And not all rows are transferred
Interestingly the missing rows seem to be the ones that were created in
step 1 and only satisfy the join filters at step 4 and 5.
Actually this problem i have just noticed seems very similar to one you
have been discussing on Developers Dex
http://www.codecomments.com/sql/mess...4712070&Page=1
with regards to Join Filters and GenerationIDs.
|||As part of my ivestigation i have noticed that the msMerge_GenHistory
table at the leafnode subscriber is populated with entries that have
not been necessaily delivered to the the subscriber. Does this sound
like expected operation. Could this account for the missing records in
that the merge process believes they have already been delivered.

Merge replication missing transactions

I have 2 servers, one running SQL 2000 and one running SQL 7. On the 2000 box I have 2 databases which are replicating between each other fine using Transactional replication. (One way replication only)

I also wish to use replication to keep a database on the SQL 7 box up to date. (Again the dataflow is ALL one way between the 3 databases)

I have set up Merge replication between these two due to transactional replication not working correctly between the different versions of SQL Server. (I belive this is due to SQL 7 using a date stamp as the unique ID on the table columns and 2000 using a GUID.)

Now whenever I issue an insert/update/delete to the first database in the chain, transactional replication updates the 2nd ok, but merge replication then fails to update the database on the 3rd server.

If I issue an insert/update/delete to the 2nd database in the chain the change is replicated correctly.

Any ideas?...Upgrade SQL2k to SP3. Microsoft have updated this but didn't publicise it at all!

If anyone has any problems with this email me at my username @.hotmail.com.

Cheers,|||Upgrade SQL2k to SP3. Microsoft have updated this but didn't publicise it at all!

If anyone has any problems with this email me at my username @.hotmail.com.

Cheers,

Merge Replication Missing Some Data

Hi All,
I have a curious problem. I have a replication to my branch that has been running over 8 months. Everything works fine but once in a blue moon I get this problem.
Replication Agent reports all replication is successful.
however, 1-2 rows of data in the branch data does not match with the publisher ( we know this because some reports in the branch that pulls this data do not tally)
no matter how many time I re-run the merge.. the data will not be merge down to the branch. The only way to get it to merge down to branch is to go to EM select that rows of data and basically re-key in the same value and save. Then the merge will pull
the data down.
Any ideas? like I say, this does not happen very often. I think only 2-3 times over the 8 months. I am not able to reproduce it mannually, I tried many ways like inserting/updating/deleting rows during a merge.. etc etc... .. but not able to reproduce
the same problem.
The replication is a standard merge replication. Server is SQL2000 SP2, branch is MSDE 2000 SP3. Nothing fancy, just pulling the data down to branch for some crystal reports.
any ideas, or ways to check? The only thing I can think of is that we are using some Chinese character... but then why only 1-2 rows.. and why not consistancely happen...
Jonathan
Jonathan,
could be a collation issue.
Are the column level collations identical on publisher and subscriber?
(this is possible because if you don't check the collation box for the
article, then when the table is created on the subscriber it picks up the
collation of the subscriber's database.)
Regards,
Paul Ibison
|||Paul,
How do I check the collation of the publisher and
subscriber?
Thanks
Emma

>--Original Message--
>Jonathan,
>could be a collation issue.
>Are the column level collations identical on publisher
and subscriber?
>(this is possible because if you don't check the
collation box for the
>article, then when the table is created on the
subscriber it picks up the
>collation of the subscriber's database.)
>Regards,
>Paul Ibison
>
>.
>
|||The default column collation is that of the database. In the database you can run sp_helpdb - eg sp_helpdb 'pubs'.
For the column, you run sp_help 'columnname' eg sp_help 'region'.
Regards,
Paul Ibison
|||I've had this a few times as well, if the collation idea fails, just change
some peice of the info and then immediatly change it back and it'll be
flagged for replication.
"Jonathan Lim" <jonathan@.jamminc.com.my> wrote in message
news:6B8034A7-B3E4-4135-B6D4-8438E7534DB1@.microsoft.com...
> Hi All,
> I have a curious problem. I have a replication to my branch that has been
running over 8 months. Everything works fine but once in a blue moon I get
this problem.
> Replication Agent reports all replication is successful.
> however, 1-2 rows of data in the branch data does not match with the
publisher ( we know this because some reports in the branch that pulls this
data do not tally)
> no matter how many time I re-run the merge.. the data will not be merge
down to the branch. The only way to get it to merge down to branch is to go
to EM select that rows of data and basically re-key in the same value and
save. Then the merge will pull the data down.
> Any ideas? like I say, this does not happen very often. I think only
2-3 times over the 8 months. I am not able to reproduce it mannually, I
tried many ways like inserting/updating/deleting rows during a merge.. etc
etc... .. but not able to reproduce the same problem.
> The replication is a standard merge replication. Server is SQL2000 SP2,
branch is MSDE 2000 SP3. Nothing fancy, just pulling the data down to
branch for some crystal reports.
> any ideas, or ways to check? The only thing I can think of is that we are
using some Chinese character... but then why only 1-2 rows.. and why not
consistancely happen...
> Jonathan