Some articles in a publication not updated on some subscribers

Question

I have transactional replication running on SQL Server 2005.  I have a publication containing approximately 20 articles (all tables) coming from a clustered server.  I have a separate distribution server, which is also a cluster.  I then have two (non clustered) servers which both have a pull subscription to the publication.

It has recently come to light that one of the subscriptions is fully up to date (as expected).  The other subscription is up to date apart from three tables which are not replicating - all other tables are replicating as normal.

I have not come across a "partly working" subscription before.  I also cannot realistically determine how long ago the two tables in question stopped replicating.

I'd welcome a pointer to where to start looking! 

Thanks,

Nick

Answer 1

There is one possibility that subscription is not set for these three tables. 

Please check mssubscriptions table in distribution database to find out status of articles in questions.  If not then run sp_addsubscription for these tables. You may run using @article = 'all' or @article = '<individual table name>'

As far as data gap is concern so that you can do in either of following way:

1. Check your primary key column value
2. Run tablediff utility

Answer 2

There is one possibility that subscription is not set for these three tables. 

Please check mssubscriptions table in distribution database to find out status of articles in questions.  If not then run sp_addsubscription for these tables. You may run using @article = 'all' or @article = '<individual table name>'

Thanks Mohan.  What are your thoughts on the fact that I have two subscriptions (from two different servers) to the same publication?  Are you suggesting that, intiially, both subscribers would be seeing all the articles and then, at some future time, a couple of articles can disappear from one of the subscriptions (but not the other).

Obviously, if that can happen, it is not good for the business!  How can I prevent it happening or, at least, be made aware as soon as it does?

Thanks

Answer 3

Hi Nick

If you had data being replicated initially then yes it has some how got decoupled. This can happen in two cases 1) Accidently subscription on that table has been deactivated 2) your subscriptions is expired due to non-activity for few days (days depends on what your threshold is).

please check if status value of these articles is 0 (Zero) in mssubscriptions in distribution database if yes then run following statement to resolve it:

update mssubscriptions
set status = 2
where status = 0

or
update mssubscriptions
set status = 2
where article_id(?,?) -- replace ? with article id

Above statement will resume replication on these tables but it will not back fill data.

If case 2nd applies in your place then it can be avoided by setting up a simple job that will execute following statement in distribution database:

update mssubscriptions
set status = 2
where status = 0

Above statement will ensure that no subscription will expire ever whether that has activity or not.

Answer 4

It looks wierd to me. can you please compaire the two subsribers setting. as you already have one working and other not.

I would do:
1.Validate the setting for both the servers.
2. Check the filter.
3. possiblitiy that it has replicated but at subsriber someone has made the changes/deleted data.

check

select * from distribution.dbo.MSarticles ---- give info about replication  articles
select * from publisher.dbo.sysarticles  ---- give info about replication  articles

http://vinay-thakur.spaces.live.com/blog/cns!645E3FC14D5130F2!590.entry

could be those articles might not replicating from the begining. so as mohan suggested you might have to add them.

how have you created the replication setup... by wizard or by script?

HTH.
Vinay

*I would suggest donot make any major changes if you donot aware of the effect of it.

---

Vinay Thakur http://vinay-thakur.spaces.live.com/ http://twitter.com/ThakurVinay