Replicating Identity Columns

  • "Not for replication" identity columns exist in SQL 2000 too, the article makes it sound like they're new in 2005.

  • Precisely - at our shop we've got one publisher and 2 subscribers using transactional replication with queued updating.

    We regularly use the subscribers as hot standby machines to allow maintenance on the publisher.

    Updates flow reliably back to the publisher.

    To do this we increment our identities in 10s and have a different seed on each server so that the identities don't clash.  Then we set the "not for replication" option on the identity columns so that the subscribers don't replace identies written ont he publish with ones of their own.

    Easy peasy.  The only caveat is that if you reinitialise the publications and snapshot the tables back to the subscribers then yo need to reset the seeds on the subscriber as the seed from the publisher is carried thorugh with the snapshot.

    Since the updates are queued on the subscriber the publisher can be offline and updates are queued up.  As soon as the publisher is back on line these get pulled through and get all the servers back in sync.

    I'm not entirely clear what is new here in 2005 - can you elaborate?


  • Absolutely the not for replication option existed in SQL 2000, but it was not used if you set up a standard transactional pub - in other words if you had not set up updating subscribers. In the absence of updating subscribers the table gets created as a plain identity col, so if you try to do an insert without providing the ident on the subscriber it would fail. It only matters when you want to use the subscriber as a fail over copy.

    I'm sorry if I wasnt clear. I've got a follow up article coming that tries to accomplish my original goal of looking at repl changes, this one ended up being as much a lesson about how not to look at changes as it was improvements.

  • As part o preparing a database for transactional replication it *is* MS advice to mark FKs, Triggers and Identity columns as NOT FOR REPLICATION *before* you create the publication. This has been the practice on my shop and therefore we don't consider it a "new" feature.

    I have been "dealing" with the "new" functionality on the 2005 product ( I should write an article - or two - about that but have to find the precious time for it )

    * Noel

  • You should definitely write something. All too often those 'new features' end up hurting those of us used to a certain behavior and they kinda forget to put that warning label on the 2005 box.

  • I had heard that one shouldn't us "sp_" as a prefix for stored procedures as this causes the server to go through the system stored procedures first looking for the proc and only then through the non-system sp's; thus causing a performance hit.  Is this true?


  • Correct, pretty common to see user procs defined as usp_blah rather than sp_blah.

  • Andy,

    I am in the process of upgrading our Prod sql Server clustered box from Sql Server 2000 to 2005. We have bought new hardware and are planning to create a new clustered environment and installing Sql 2005 on it.

    We plan to copy all the databases from the old box to the new and on a weekend we would make the application point to the new environment.

    We will also replicate from the new sql 2005 box to the old Sql 2000 box for a month or so to make sure everything is ok. In case of a problem with the sql 2005 box, we want to be able to use the Sql 2000 box again for production.

    We have about 80 tables with the Identity column as the primary key, so if I understand correctly, as we are replicating from 2005 to 2000, if we mark all tables with Identity columns and foreign keys as 'NOT FOR replication' and hopefully that will be a simple thing to figure out. have not looked at it yet. That should be the only thing we need to worry about in our case, so if for some reason we had to revert back to using our 2000 environment we would be ok as all the Identity keys would be ok there and so also the fk constraints. Is that all that we need to do then to take care of this.



Viewing 9 posts - 1 through 8 (of 8 total)

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