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