Post reply

replication and identity fields...

  • August 4, 2005 at 3:35 pm

    #90301

    Hoping someone can help me out here...

    I have some databases that need to be co-located off-site.  We'll be replicating data up to the (off-site) co-lo facility.  We have various identity columns, and I need to figure out how to deal with them in the context of this co-location replication.  i.e. I need to make sure the values in these fields stay completely in sync at the co-lo.....however, I need to be able to turn the co-lo into "Primary Production" in the event that our main facility disappears in a fire/flood/etc one day.

    The way it's going to work...

    BOX 1 (primary production box) will replicate to BOX 2 (a "replication staging" box here at our primary facility).  BOX 2 will then replicate everything to the off-site co-lo facility.

    Ideally, I'm trying to avoid the need to have custom scripts developed for the process of "turning the co-lo database into the primary db".

    I want the subscribers (BOX 2 and 3) to accept the replicated transactions from BOX 1…accepting the publisher’s (BOX 1's) identity field values (thereby ensuring things are kept in sync).

    HOWEVER, I’d like for the subscriber’s version(s) of the table(s) to have the identity columns defined and have it “keep track” of where the increment is at...so that when/if we need to start doing direct inserts at the co-lo (which would have to start happening in the event that the primary facility disappears), the co-lo will just pick up the identity value increment with whatever would be next and no manual changes will be required (aside from pointing the data stream to the co-lo facility db).

    As things stand, based on a small test I've run...

    I’ve got my test tables created, with the identity column defined, on three boxes.  Box 1 (simulating primary production) replicates to Box2 ("replication staging"), and Box 2 then replicates the same table to Box 3 (co-lo).  In the repl set-up, I followed the steps to make sure the identity column is preserved on all three boxes:

    1.)     I explicitly created the table on all three boxes, with identity column defined.

    2.)     In the process of setting up the publication, I made sure to stipulate that the version(s) of the table on the subscribers was not to be dropped and re-created.

    3.)     I edited the MSdel/ins/upd procs as you’d stipulated, to add in “identity insert on/off” 

    …And replication works fine – I can insert a row on box 1, and it shows up on box 2 and 3.

    HOWEVER…

    The problem comes when I delete the replication from box 2 to 3 (in an attempt to simulate primary facility “disappearing”).  I then try to insert a record on box 3 (to simulate the scenario where that box then has to become “primary” production)….but I then get a key violation on the identity field (which is also defined as primary key).  The increment on the identity field isn’t being counted by the subscribers for the replicated inserts, so the subscriber thinks it's starting the increment at 1, not taking into account the data that was already fed into the table via repl.

    Anyone have any thoughts?  Is what I'm trying to achieve even possible?

    thanks,

    -Skip

     

  • August 4, 2005 at 3:48 pm

    #579872

    Have you thought about using log shipping?

  • August 4, 2005 at 10:06 pm

    #579902

    There might be a better way of doing this without using replication

    possibly as per previous post...

    what sort of replication you using? snapshot/transactional ???


    ------------------------------
    Life is far too important to be taken seriously

  • August 5, 2005 at 4:47 am

    #579946

    You need to put the schema in place the way you want it on the subscriber and use the not for replication setting for all of your identity columns, triggers and foreign key constraints.

    The best way to do this is to backup your publisher, and then restore it on your subscriber and make all these changes there. Also when you create your publication make sure you use the Keep Existing Table unchanged in the name conflicts section of the specify articles dialog (click on the browse button to the right of your table name, and click on the snapshot tab to see this).

    If you put set identity_insert tablename on and off statments in your procs you will have to do this each time the procs are generated. With luck you will never have to do this, but if you reinitialize you will.

    The second drawback of set_identity_insert tablename on and off is that you are serializing all of your procs. You can only have one of these statements live at a time.

    What you are trying to do can be done, but its a little difficult.

     

    --

    Hilary Cotter

    Looking for a SQL Server replication book?

    http://www.nwsu.com/0974973602.html

    Looking for a FAQ on Indexing Services/SQL FTS

    http://www.indexserverfaq.com

     

     

     

     

  • August 5, 2005 at 7:58 am

    #580005

    Thanks for the responses all.

    To answer some of the questions...

    I'm working with TRANSACTIONAL replication.

    Unfortunately, log shipping isn't an option as we aren't talking about an Enterprise edition setup of SQL Server. That said, we do have a sort of "pseudo log shipping" set up running on some boxes here, where we're manually copying and restoring transactional log backups every X minutes from one box to another (crude, I know).  So, that might have been an option for this co-lo project...However, I'm relatively sure that the requirement I've got to work from involves keeping the co-lo up to the minute in-sync, which log shipping won't give me.

    Re: Hillary's last post...

    I do have things set up with the schema in place the way I want it on the subscriber(s) and I am using the 'not for replication' setting for the identity columns.  I also did make sure to use the 'Keep Existing Table unchanged' option when creating the publication.  So, that's all been done.

    Interesting comments re: the identity_insert settings in the MSdel/upd/ins stored procs.  I've actually tried this thing both ways, i.e. with those statements in, and then with them removed, and it doesn't seem to affect things one way or the other.

    So, to re-cap, I'm still faced with a situation where I can successfully replicate to the subscribers, the identity field IS defined on the subscribers, and the subscribers are accepting the identity values from the publisher (which is what I want)... but the subscriber isn't "counting" the replicated inserts when it comes to keeping track of it's identity increment.  So, when I go to do a direct insert on the subscriber, it thinks the identity value is starting from scratch...

    Thanks,

    -skip

  • August 5, 2005 at 9:09 am

    #580068

    I actually think I've got this working now...

    I had to set the "Not For Replication" value OFF on the identity field on all three boxes.

    I DID have to alter the MS procs to include the 'set identity insert ON' (and then 'OFF' again at the end) on both of the subscribers.

    But now the subscribers are incrementing, yet accepting the identity values from the publisher!!

    The only remaining concerns would seem to be the ones that Hillary raised, in terms of a.) making sure I maintain the MS scripts on the subscribers and b.) the fact that this serializes the procs.  So, the next step is to test with larger data sets and make sure it isn't a dog performance wise...

    thanks all!

     

Viewing 6 posts - 1 through 5 (of 5 total)

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