none
Merge Replication with Parameterized Filters and Republishing - Automatic Identity Management

    Question

  • Here is my scenario:

    I have a Merge publisher with articles and parameterized filter. All identity management is set to automatic and I have allocated within range of 10000000. I have 5 subscribers subscribing from this publisher, each with their own subset of data depending on the parameterized filters I have set. On initial setup everything is OK. When setting up a new subscriber to receive their own subset of data, I am faced with the following error:

    The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/mssql-repl-2147199417?view=sql-server-2017

    I am using SQL Standard Ed 2014 for all publisher and subscriber nodes. 

    I checked back to the publisher and the identity col for the articles are not sync with the last subscriber i setup so that means I have to manually manage it and run the merge agent. 

    Has anyone been faced with this error? How do you handle identity management if subscriber are getting their own subset of data with their own identity ids? 

    Thank you in advance!

    Friday, May 04, 2018 4:06 PM

Answers

  • That is a large range you are using to begin with. You should hand out a range which could be exhausted within the maximum amount of time that your subscriber may be not able to sync for.

    For example if you insert 1000 rows between syncs and sync every 15 minutes and expect to be offline a maxiumn of 10 days - this would mean that you will size your range at 1000*15*4*10 which means 600000.

    It looks like you are using the set it and forget it method which means you are using a range which might be appropriate, but you will need to set it to manual then.

    You also need to check to see what is in your identity range tables in the publication and distirbtuion databases. There could be bogus enteries there.

    Check MSrepl_identity_range in the distribution database, or MSmerge_identity_range in your publication/subscription databases. The entries here will be the last range handed out.

    • Marked as answer by CarlaAbanes Monday, May 07, 2018 7:18 PM
    Friday, May 04, 2018 4:25 PM
    Moderator
  • Make sure the entries in the publisher and distribution database are correct.

    Note that if I insert a million rows on the subscriber and I exhaust the assigned range and the emergency range my checkident value will be 1 million + the original value.

    When you run the sync agent it should correct the range for the new range handed out. So it is auto correcting every time you sync. You may need to sync more frequently but with the range you are staring off with it sounds like you might be doing very high volumes. Is this possible?

    Identity ranges on the publisher are incremented every time you do an insert there. If you exhaust a range there, your next insert will assign you a new range. There is nothing you should have to do on the publisher.

    Its on the subscriber you should sync to correct it.

    All should be working fine as long as the 2 tracking tables have valid values in them.

    I would not try to adjust values myself if I where you. I would open a support incident with Microsoft on their guidance here - or tear everything down clear the values in these two tracking tables and start again. I am not sure if these are options for you.

    • Marked as answer by CarlaAbanes Monday, May 07, 2018 7:18 PM
    Friday, May 04, 2018 6:59 PM
    Moderator

All replies

  • That is a large range you are using to begin with. You should hand out a range which could be exhausted within the maximum amount of time that your subscriber may be not able to sync for.

    For example if you insert 1000 rows between syncs and sync every 15 minutes and expect to be offline a maxiumn of 10 days - this would mean that you will size your range at 1000*15*4*10 which means 600000.

    It looks like you are using the set it and forget it method which means you are using a range which might be appropriate, but you will need to set it to manual then.

    You also need to check to see what is in your identity range tables in the publication and distirbtuion databases. There could be bogus enteries there.

    Check MSrepl_identity_range in the distribution database, or MSmerge_identity_range in your publication/subscription databases. The entries here will be the last range handed out.

    • Marked as answer by CarlaAbanes Monday, May 07, 2018 7:18 PM
    Friday, May 04, 2018 4:25 PM
    Moderator
  • Thank you for your reply Hilary!

    "Check MSrepl_identity_range in the distribution database, or MSmerge_identity_range in your publication/subscription databases. The entries here will be the last range handed out." -- in this part will come in as the manually managing the entries for the identity_range used by publisher and subscriber?

    So here are my course of action to correct my problem: Please do correct and help me add if anything else is missing...

    1. I will check this and create an sp to report last identity_range entries for all nodes that can help me manage this. 

    2. Correct the identity values for the articles, I have done manual compare of the identity values using the DBCC CHECKIDENT function for all the nodes and some tables are really way off!

    3. Use the sp_adjustpublisheridentityrange to adjust the ranges (or should I do it manually via the article properties)? 

    3. Sync the subscribers with the publisher

    Am afraid I might face this issue again when I add another subscriber with their own subset of data based on the parameterized filters. 

    Thanks again!

    Friday, May 04, 2018 5:02 PM
  • Make sure the entries in the publisher and distribution database are correct.

    Note that if I insert a million rows on the subscriber and I exhaust the assigned range and the emergency range my checkident value will be 1 million + the original value.

    When you run the sync agent it should correct the range for the new range handed out. So it is auto correcting every time you sync. You may need to sync more frequently but with the range you are staring off with it sounds like you might be doing very high volumes. Is this possible?

    Identity ranges on the publisher are incremented every time you do an insert there. If you exhaust a range there, your next insert will assign you a new range. There is nothing you should have to do on the publisher.

    Its on the subscriber you should sync to correct it.

    All should be working fine as long as the 2 tracking tables have valid values in them.

    I would not try to adjust values myself if I where you. I would open a support incident with Microsoft on their guidance here - or tear everything down clear the values in these two tracking tables and start again. I am not sure if these are options for you.

    • Marked as answer by CarlaAbanes Monday, May 07, 2018 7:18 PM
    Friday, May 04, 2018 6:59 PM
    Moderator
  • Thank you Hilary!

    For the things you pointed out, here are our scenario...

    When you run the sync agent it should correct the range for the new range handed out. So it is auto correcting every time you sync. You may need to sync more frequently but with the range you are staring off with it sounds like you might be doing very high volumes. Is this possible? -- yes, we maybe inserting rows in batches of 10000+ for newly launched clients (which is a weekly thing lately).

    Its on the subscriber you should sync to correct it. -- I'm now checking the identity values and datatypes for when it could max out. Also making sure they are in sync with what's in on the publisher db. Checking with our dev team if the smallint datatype in one of the article can be changed to int. 

    Will update here as I go along with my progress...

    I really appreciate your help!

    • Marked as answer by CarlaAbanes Monday, May 07, 2018 7:18 PM
    • Unmarked as answer by CarlaAbanes Monday, May 07, 2018 7:18 PM
    Friday, May 04, 2018 9:08 PM
  • A quick question - how many subscribers do you think you will have for the life of your replication topology?
    Monday, May 07, 2018 1:21 PM
    Moderator
  • Hi again Hilary,

    My client is looking to add all future clients as subscriber, per client there is 3 databases, so if we launch 10 clients in the next 2 months, we are looking at 30 subscriber databases. 

    Appreciate your advise on our current setup!

    Cheers,


    Monday, May 07, 2018 1:51 PM
  • As you are exhausting your range you might want to set it to 100,000.
    Monday, May 07, 2018 7:33 PM
    Moderator
  • Will take note of that, and thank you!
    Monday, May 07, 2018 8:48 PM