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

Replicating Identity Columns Expand / Collapse
Author
Message
Posted Monday, March 12, 2007 8:46 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:36 PM
Points: 6,778, Visits: 1,852
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/awarren/2907.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #350740
Posted Wednesday, March 28, 2007 1:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 16, 2014 3:57 AM
Points: 379, Visits: 55
"Not for replication" identity columns exist in SQL 2000 too, the article makes it sound like they're new in 2005.
Post #354399
Posted Wednesday, March 28, 2007 3:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 10, 2011 3:24 AM
Points: 3, Visits: 5

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?

 

Post #354414
Posted Wednesday, March 28, 2007 8:01 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:36 PM
Points: 6,778, Visits: 1,852

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.



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #354491
Posted Wednesday, March 28, 2007 9:01 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
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
Post #354531
Posted Wednesday, March 28, 2007 9:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:36 PM
Points: 6,778, Visits: 1,852

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.



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #354532
Posted Wednesday, March 28, 2007 4:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:32 AM
Points: 7, Visits: 42

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?

 

Post #354648
Posted Wednesday, March 28, 2007 6:45 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:36 PM
Points: 6,778, Visits: 1,852
Correct, pretty common to see user procs defined as usp_blah rather than sp_blah.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #354663
Posted Wednesday, July 22, 2009 4:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 10:33 PM
Points: 26, Visits: 100

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.

Thanks,
Vip.





Post #757904
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse