Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Identity columns Expand / Collapse
Author
Message
Posted Friday, January 18, 2013 9:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:18 PM
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.
Post #1408972
Posted Wednesday, January 23, 2013 6:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 170, Visits: 706
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
Post #1410549
Posted Wednesday, January 23, 2013 8:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:18 PM
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.
Post #1410640
Posted Thursday, January 24, 2013 8:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 170, Visits: 706
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
Post #1411168
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse