Identity columns

Question

Identity columns

ryan_xh

SSC-Addicted

Points: 493
More actions
January 18, 2013 at 9:26 am

#393966

Hello all,
I have set up one way pull transactional replication with sql 2008 r2 and the subscription was initialized from a backup. The publication database has many identity columns and I think for this type of replication, identity range management should be manual. After the subscription was initialized I haven't change or update identity management type or idenity ranges in either publisher and subscriber. I am assuming that these changes for identiy column options will be managed by the replication agents. Could you please let me know if I am wrong to assume this and if I need to do anything for the replication of the identity columns to work properly?
Thanks for your time.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Feivel SSCertifiable Points: 7524 More actions · Answer 1

I believe your topology suggests that you should go with manual identity management.

Since, you are trying to manage identity values manually, first & foremost you should be sure enough that your publisher and each of your subscribers use different identity ranges.

Let's say for e.g: consider a published table with an identity column defined as identity(1, 1), what will happen in this case is the identity column will start out at 1 and will keep on incrementing by 1 on every insert. So, if this table has on an average 5,000 rows, and you expect some further growth in the table over the life of the application, then it's possible this could use up the all the range from 1-10,000. Now if there are more than one subscribers, say for e.g 2 subscribers then subscriber "a" could use range from 10,001-20,000, and subscriber "b" could use a range from 20,001-30,000.

Now, what I mean to say here is, once your subscriber gets initialized either by means of a snapshot or backup (u r case), then execute dbcc checkident to assign a starting point for it's identity range. In the above example

it should be checkident('<tablename>', 'reseed', 10001) for subscriber a, and dbcc checkident('<tablename>', 'reseed', 20001) for subscriber b.

As per technet article, to assign new ranges to the publisher or subscribers, you should execute dbcc checkident and specify a new value to reseed the table.

I believe there should be some way in which your application could have a mechanism that detects when it is about to use up all of it's range, and then you could run dbcc checkident to provide new ranges. You can also add a check constraint on the identity column ensuring that a row cannot be added if it would cause an out of range identity value to be used.

I hope this helps.

Regards,

Faisal

ryan_xh SSC-Addicted Points: 493 More actions · Answer 2

Many thanks for your reply.

We only have one subscriber.

What I do not understand is that since our transactional replication is one way (no updating at the subscriber), then why we need to have different identity ranges for an identity column in publisher and subscriber? The identity values are being generated at the publisher and these sames values are being inserted in the tables at the subscriber (identity columns at the subscriber has NOT FOR REPLICATION property set to YES).

Could you please let me know why we need separate identity ranges at pubsliher and subscriber when the identity values are only being generated at the publsiher?

Thanks in advance.

Feivel SSCertifiable Points: 7524 More actions · Answer 3

How can you be so sure that the values which are generated at the publisher could be same at the subscriber as well. Did you check what's being replicated at the subscriber end. I never told that there will be updates on the subscriber, pls. read the post carefully on what I meant to state over here.

You can check out technet articles for more info on this. This will give you a better idea on the inner working of identity range management.

Regards,

Faisal