2

With merge replication, if the subscriber runs out of identities for a range when inserts occur into it, the subscriber will throw an error on the insert into the table, because it is the publication must grant more identities - and the insert is happening on the subscriber. A better practice would be to make the publisher be the receiver of inserts only, but in some environments or situations, inserts can occur to both the publisher and subscriber, thus many people extend the range.

I am curious how sequences compare to identities here, as I can't find any indication that sequences are limited by merge replication setups like identity fields are.

I believe Aaron Bertrand or another MVP has been really big on sequences and I've been trying to push my organization to use them over identities, but it's a long journey.

3

With merge replication, identity ranges must be allocated to the publisher and each subscriber. Identity range management can be either manual or automatic.

As you already have found out, when a range is exhausted, a new range must be allocated. With automatic range management, the merge agent takes care of allocating a new range, with manual range management you decide what should happen.

With sequences, you will still have the same problem, with the difference that there is no such thing as automatic sequence range management, so your only option is manual range allocation. (FWIW, identities are implemented with sequences in SQL Server 2012 and above).

My suggestion is to avoid identities and sequences altogether: they do more harm than good. Did you know that in order to let identity range allocation happen at the publisher, the users that insert data in the database need to be members of the db_owner role? Yeah, that's awful.

With merge replication you need a uniqueidentifier ROWGUIDCOL anyway, so why don't you use that column as your surrogate key instead? If your key is really a surrogate, nobody should be able to see it or use it and it should be completely transparent to the application.

  • Really excellent point on the last paragraph. It has to happen anyway, so yeah, use it! Thanks for the awesome answer. – user541852587 Feb 6 '16 at 0:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.