Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Merge Replication - Automatic Identity Management

Answered Merge Replication - Automatic Identity Management

  • Friday, November 30, 2012 5:54 PM
     
     
    Sign In to Vote
    0

    I have always handled identity management on merge replications manually. I have read over the papers on setting up automatic range management but I am not sure if I want to head in that direction or stay with the manual management.

    My scenario:

    I have two 2008 R2 servers in two seperate geographical locations. Server A is the primary server and Server B is the fail-over server. The only time that Server B is active and receiving data from a client is when there is a problem connecting to server A (Windows update reboot, connectivity issues, hardware issue, etc.). The vast majority of transactions will be done on Server A (roughly 300,000 per year)

    With that in mind, I am trying to establish what my ranges should look like. I feel like the Publisher range size should be a high number (i.e. 10,000,000) mainly because I like to keep the incrementing key in a range that tells me where the insert was done (Server A vs. Server B). I then thought I should have the Subscriber set to 300,000 which should handle and failover events for some time.

    Questions/Comment request

    1. If I need to make changes to a table and have to stop replication, how would I handle the ranges? How does SQL Server handle the identities that have already been used on server A? Will it jump the the next 10,000,000 and start using ids in the 12,000,000 range?

    2. If my ideas on the ranges above can be improved, please comment.

    3. If I have an extended outage on Server A, what happens if I run out of IDs in the subscriber assigned range. I used a large number above to make sure I would not run out, is that a good way to handle it?

    Thank you

     

All Replies

  • Saturday, December 01, 2012 3:48 AM
    Moderator
     
     Proposed Answer
    Sign In to Vote
    1

    The subscriber range size, or @identity_range parameter, controls the identity range size allocated to both the Publisher and to Subscribers with client subscription types.

    The publisher range size, or @pub_identity_range parameter, controls the identity range size for republishing to Subscribers with server subscription types.

    The @pub_identity_range parameter name in Merge is misleading since it does not control the Publisher's range, @identity_range does.

    With that said, you will want to allocate range sizes large enough to accommodate for the number of inserts that may occur between Merge Agent synchronizations.  Some might argue that you should keep the range size at the lower end of the accommodation, however, I am an advocate of allocating very large range sizes, just in case.

    I hope this helps.

    Brandon Williams (blog | linkedin)

    • Proposed As Answer by John Sansom Saturday, December 01, 2012 7:51 AM
    •  
     
  • Monday, December 03, 2012 2:21 PM
     
     
    Sign In to Vote
    1

    Adding to Brandon's; If you use automatic identity ranges then there is no need to worry about the ranges between the servers.

    A good practice though is to monitor the ranges so that you know a head if you are about to hit the limit. You may find this post helpful

    http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2010/09/18/monitor-identity-ranges-in-merge-replication.aspx

    This post shows examples for manual identity ranegs in Transactional replication but it is also valid in Merge

    http://www.sql-server-performance.com/2011/identity-ranges-transactional-replication/

    Yaniv Etrogi
    site | blog | linked in | mail
    Please click the Mark as Answer button if a post solves your problem! or Vote As Helpful

     
  • Monday, December 03, 2012 8:25 PM
     
     
    Sign In to Vote
    0

    Thank you for the response.

    Just so I am clear:

    1. Publisher range size is not applicable to my situation above as I am not republishing. So it would be OK to leave at the default value.

    2. I should set the subscriber to a number that will accommodate a worst case scenario outage. In my example, I used 300,000 as that would theoretically last me for a one year outage (assuming no previous inserts had been made).

    One last question, if I needed to make changes to a table and had to republish the database, how would SQL server handle the existing keys? Would it look for the max value and then adjust the range?

    Thanks

     
  • Monday, December 03, 2012 9:00 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    1)  Correct, unless you eventually plan to republish.

    2)  I would agree with this range size as long as your application will tolerate it.  For example, if identities are exposed in your application(s) in some manner there will be a gap in identity values when a new range is assigned.  Accountants might be perturbed by this.

    SQL Server would end up using the next available publisher identity ranges from the republisher to allocate to republishing subscribers.

    Brandon Williams (blog | linkedin)