SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identity columns


Identity columns

Author
Message
ryan_xh
ryan_xh
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 437
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.
ffarouqi
ffarouqi
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 1314
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
ryan_xh
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 437
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.
ffarouqi
ffarouqi
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 1314
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search