Home > IT Answers > SQL Server > Merge Replication Identity Ranges
Help

Question

  Asked: Mar 11 2008   4:41 PM GMT
  Asked by: Ramapa

Merge Replication Identity Ranges


Merge replication, Replication, SQL Server administration, SQL Server 2005

What should the publication range and subscription range be set to for each table with identity ranges, bearing in mind thousands of changes can go into some tables each day. I have 1 Publisher and 9 Subscriptions (Merge) and have started getting "The Publisher failed to allocate a new set of identity ranges for the subscription.""Error number: MSSQL_REPL-214719941"
Is there a formula or Rule-of-thumb that we can use?

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Mar 11 2008  7:34 PM GMT  by Mrdenny




Basically you want to make sure that the ranges are large enough that you can't run out of IDs between merges. If you do a merge every 1 hour and you add in 10000 new records per server per hour at peak you should have at least 12000 IDs in the range so that you don't ever run out.
  • AddThis Social Bookmark Button

Browse more Questions and Answers on SQL Server and Database.

Looking for relevant SQL Server Whitepapers? Visit the SearchSQLServer.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Mrdenny  |   Mar 11 2008  7:34PM GMT

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.

 

Ramapa  |   Mar 11 2008  8:09PM GMT

So it sounds like if we have the schedule set to every 1 minute, we could set the ranges to say 1000? Does this value take in to consideration the 9 subscriptions ?

Thanks for your response !!

 

Ramapa  |   Mar 11 2008  8:16PM GMT

Additional question is then sub 1 starts at 1000 and sub 2 starts at 2000, what happens when it hits 1999? Does the range automatically get changed? There will be significant changes on both sides, Publisher and subscriber…

Thanks again !