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


Replicating Identity Columns


Replicating Identity Columns

Author
Message
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11361 Visits: 2730
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
Norman Rasmussen
Norman Rasmussen
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 55
"Not for replication" identity columns exist in SQL 2000 too, the article makes it sound like they're new in 2005.
Tim Moss
Tim Moss
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20

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?


Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11361 Visits: 2730

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
noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9508 Visits: 2048
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
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11361 Visits: 2730

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
John Fager
John Fager
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 43

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?


Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11361 Visits: 2730
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
Bharatvip
Bharatvip
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 144
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.



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