Friday, November 30, 2012 5:54 PM
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.
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.
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?
Saturday, December 01, 2012 3:48 AMModerator
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.
- Proposed As Answer by John Sansom Saturday, December 01, 2012 7:51 AM
Monday, December 03, 2012 2:21 PM
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
This post shows examples for manual identity ranegs in Transactional replication but it is also valid in Merge
Monday, December 03, 2012 8:25 PM
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?
Monday, December 03, 2012 9:00 PMModerator
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.
- Edited by Brandon WilliamsMicrosoft Community Contributor, Moderator Monday, December 03, 2012 9:13 PM
- Edited by Brandon WilliamsMicrosoft Community Contributor, Moderator Monday, December 03, 2012 10:05 PM
- Marked As Answer by hmmmm007 Wednesday, December 05, 2012 5:23 PM