MSrepl_identity_range in publisher and distribution DBs

Can anyone shed any light on why this table exists in both the publication DB AND the distribution DB. The reference material just says:

The MSrepl_identity_range table provides identity range management support. This table is stored in the publication, distribution and subscription databases

However,doesn't shed any light on WHY it is in each database.

For merge replication which one should I be looking at to determine the next seed value?

Assuming I am looking at the appropriate records in the distribution db (where publication_db = my publication db) should the values be identical to the values in the MSrepl_identity_range table in the publication db?

Ok.

It looks to me as if the table in the publication database serves no useful purpose.

The next_seed value in the distribution table is updated to reflect the starting point for the next range (regardless of whether it is the publisher or a subscriber that is being allocated a new range) While the next_seed value in the publication database never changes...

|||

In the publication database it is for the range assigned to table which could be in other publications.

The one in the distribution database is used as a point of reference when assigning new ranges to subscribers. This is done to ensure that the range is never assigned to two nodes in your replication topology.

Did you review this article?

http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/

|||

Thanks.

I'm still not clear on what purpose the table in the publication database serves since it doesn't appear to ever be updated. The table in the distribution database keeps track of the last_seed value. I'm assuming that the tabvle in the publication db would be used for a different type of replication (transactional replication perhaps?)

As a work-around I created a script that re-seeds the identity in the publication database (to a value that should be greater than any record created on any existing subscriber) and adjusts the next_seed value to reflect this (by calling sp_adjustpublisheridentityrange). When each subscriber next synchronises they will follow that up with a re-initialisation (so they get assigned a new range starting from my new next_seed value. Once all subscribers have been re-initialised we shouldn't get any more clashes.

This workaround is purely to avoid having to bring all of the remote subscribers back in at the same time : the 'ideal' fix I think would be to synch all subscribers, drop the publication, reseed the identity on the publisher, re-create the publication and initialise all subscribers. It's just that this is a major disruption to the subscribers (who are spread out over a large geographic region).

sql

msmerge_contents doesn't get deleted

Hi all,
I'm having a problem with my sql server. I've an application where
several subscribers synchronize with a sql server publisher for some
tables of a database and a batch process also interacts with those
tables (basically updates data as a batch process from a SAP)
The problem I have is that data of actualizations made to the table
from the batch process (in msmerge_contents) is never cleaned up and
the table keeps growing without limit. In a couple of weeks the
synchronization process gives timeout errors to devices.
I've the property -MetaDataRetentionCleanUp set to 1 and data from
subscriber's synchronization gets cleaned up after retention period
expires. This aparently works well but how is data actualizations
coming from the batch process supposed to get deleted if it doesn't
have a Merge Agent ?
Actually I'm running the stored procedure sp_mergecleanupmetadata
after some days to clean up the merge tables but this is giving a lot
of synchronization problems with subscribers.
Could anybody give an advice on how should this problem be solved ?
any help will be appreciated.
thx in advance,
ori
Ok, I've accepted that this behaviour is by design. If you have an
external source updating a publicated table, that will generate
tracking rows in the msmerge_contents table and that rows will never
be cleaned up because there's no Merge Agent associated with the
source.
Now, what I'd like to do is to find a way to remove manually that
rows. I thought it would be possible to generate a script that would
delete all the rows inserted by the external source in the table
msmerge_contents with a date prior to two times the retention date set
on the publication. This way, I can assure that all the subscribers
are synchronizated or will be marked for reinitialization so they'll
be up to date.
Could anybody help me with that script ? Is it a good solution ? can
it be done ?
Any help will be appreciated, thx in advance.
ori