Data Not Showing on Subscriber

Question

Data Not Showing on Subscriber

PABLOTOSH

SSC Veteran

Points: 262
More actions
August 25, 2008 at 4:12 am

#191478

I have a small publication in which there are two related tables Offers and OffersApplications with a join filter on the field OfferID which is INTEGER.
When I look at the OffersApplications table for one particular OfferID there are 8 records on the subscriber but 98 on the publisher.
If I regenerate the snapshot and reinitialisethe subscription all the data s present at the subscriber, bu after several days it all goes missng again.
What is happening here??

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

When you say "after several days it all goes missing", do you mean that new data is not being replicated to the subscriber, or that previously replicated data is no longer visible on the subscriber?

I assume you are using Transactional Replication - correct?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

PABLOTOSH SSC Veteran Points: 262 More actions · Answer 2

Hello John

Previously replicated data is no longer visible on the subscriber.

I'm not using Transactional Replication but Merge Replication using Web Synchronisation.

Any help you can give would be much appreciated as it's driving me bonkers!!

Paul

John H Marsh SSChampion Points: 11671 More actions · Answer 3

Hello Paul,

Got to admit that I'm more familiar with Transactional and Snapshot replication, but below is my best guess, for what it's worth:-

As you're using Merge Replication I wonder if the issue is related to Conflict Resolution e.g. in your example, may be on one of the Servers there are only eight OffersApplications records and it is this version of the data that wins, and becomes the version to be published?

Did you set up the replication yourself i.e. do you know the Conflict Resolution Rules being used for this publication?

Regards,

John

www.sql.lu
SQL Server Luxembourg User Group

PABLOTOSH SSC Veteran Points: 262 More actions · Answer 4

Hi John

Yes I did set up the replication myself and it was the first time I have done this so clearly I have not configured something correctly.

I don't recall specifying any conflict resolution rules so I assume the replication is using the defaults, whatever they are.

Where are the conflict resolution rules defined and ow can I edit them?

I don't know if this has any bearing but the OffersApplications table contains another join filter called CrewID and the CrewID and OfferID columns form a UNIQUE CONSTRAINT thus preventing duplicate entries.

It was this that alerted me to the problem as INSERTS from the subscriber could not be downloaded because they already existed at the publisher.

Thanks

Paul

John H Marsh SSChampion Points: 11671 More actions · Answer 5

Hello Paul,

You can view the Conflict Resolvers on the Article's Properties pages (there's a separate Tab for Resolvers), but I guess now that it will just show Default in your case.

You could try sp_helpmergearticleconflicts to see if it returns any data for your Publication. It would be particularly interesting if the result set relates to data that you know has disappeared from the Subscriber. (There's a section in BOL on "Detecting and Resolving Merge Replication Conflicts").

I can’t instantly think of why the Unique Constraint might cause this issue, but If anything (useful) pops into my head I'll post it.

Regards,

John

www.sql.lu
SQL Server Luxembourg User Group

PABLOTOSH SSC Veteran Points: 262 More actions · Answer 6

Hi John

Just found the Resolvers tab on the article properties and they all do say Default.

One thing I did discover is that the Merge Agent Profile set at the distributer was 'High Volume Server to Server'. As we don't have a high volume of transactions I set this to the Default profile.

I've just regenerated the snapshot and reinitialised the subscriber and have all the data present and correct. I'll monitor this and see what happens. If the data 'disappears' again I'll run sp_helpmergearticleconflicts and see what it comes up with.

Thanks very much for your time and effort. You've given me a couple of starting points.

Regards

Paul

steve smith-401573 SSChampion Points: 10889 More actions · Answer 7

Do you have any system generated ID fields in the tables? IF so, did you mark them 'not for replication' or set up different ranges for each of the servers with merge replication? That might be causing the problem. IF you don't have the unique IDs set up correctly, the merge process may cause your reporting server's copies to lose in a conflict and then be removed? Just a guess.... Because that would not explain why the server would not repopulate again, with the 'winning' records.

PABLOTOSH SSC Veteran Points: 262 More actions · Answer 8

Hi Steve

I see your point! Yes there are system generated ID fields in all the tables and these form the backbone of the foreign keys.

The ID ranges are managed by SQL Server 2005 and periodically the publisher issues a new range of ID's for each of the subscriber tables.

I'll keep an eye on it and see if the 'disappearing' data anomaly occurs after a new ID range is issued.

Thanks for the idea!