October 31, 2007 at 11:10 am
We use merge replication with anonymous subscriptions, SQL Server 2000.
I am running into this problem when I use sp_addmergearticle and the table contains an identity column.
Steps I perform to add the article with identity support.
1. Add table to publisher.
2. Run the following script to add the merge article:
exec sp_addmergearticle @publication = N'publicationName', @article = N'articleName', @source_owner = N'dbo', @source_object = N'articleName', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option = 0x000000000000EFF1, @article_resolver = null, @subset_filterclause = null, @vertical_partition = N'false', @destination_owner = N'dbo', @auto_identity_range = N'true', @pub_identity_range = 100000000, @identity_range = 100000000, @threshold = 98, @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @force_invalidate_snapshot = 1
3. Run a new snapshot and move it to the appropriate location. I then synchronize the subscriber.
The subscriber picks up the new article and adds the identity constraint to the table. I then notice that msrepl_identity_range in the distributor database is not incremented for each subscriber. All subscribers have the same identity range. Once I reinitialize the subscriptions the identity range is then incremented.
Is this the normal behavior when adding an article with auto_Identity_support? Should I look into manual identity management for this article?
Any help is much appreciated.
November 9, 2007 at 6:15 am
In case anybody else experiences this problem.
The issue:
When adding an article that uses automatic identity range management to a publication, after running a snapshot, when subscribers synchronize (without reinitializing) the table is brought down to each subscriber and every subscriber receives the same identity range. Also the next_seed value in the distribution database msrepl_identity_range table for this (identity) table is not incremented.
I had a contact in with Microsoft and received the following response:
The issue we faced is identified as a bug in SQLserver2000 and A Hot fix is developed for the issue.
This hot fix and KB are still internal and will be released very shortly. we have tested the Hot fix (present internally) at our side it resolves the issue which we were facing related to identity range management.
The KB article which briefs about this issue is 939318, but it is still internal and not yet released to public.
I will provide you an update as soon as the KB and fix are available to public. This fix takes SQL Server to 2253 build.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply