Locking question
-
Believe me, Plenty. and with a very high transaction rate.
Now, how many have you replicated using IDENTITY columns ?
* Noel -
Seq. numbers dictate RBAR approach by definition.
Not high transaction rate really.
And simple "NOT FOR REPLICATION" resolves all problems with IDENTITY.
_____________
Code for TallyGenerator -
Your answer is an indication of the "Lack of knowledge" in these issues.
When you need to fail over to the replicated site you need to have there the *same* keys. There are *a lot* of kludges to deal with the propblem. Identity partitions, range handling, etc. ALL of them pose problems one way or another.
Sequence TABLES ( which are different from SEQUENCE Numbers) are there for a purpose.
* Noel -
Not gonna tell I'm an expert in replications.
I did replications for couple of databases (nobody requested more yet
) and IDENTITY was not a problem.Probably I was not experiencing all possible features in that, but I did not have any problems with copying keys from IDENTITY columns.
Sequence TABLES are not there. Probably for a purpose.
It's you who create those tables.
_____________
Code for TallyGenerator -
Once again IDENTITY columns *are* problematic in replicated environment with failover scenarios and there are *a lot* of tricks, kludges and workarounds to deal with the problem. Sequence TABLES is nothing but one of those "tricks" and when I said that are there I meant in common DBA Knowledge which apparently ... is not that common
* Noel -
From my experience I can tell that Sequence TABLES is common VB developers knowledge, not DBA.
_____________
Code for TallyGenerator -
Noel,
but seriously, what's the problem with IDENTITY NOT FOR REPLICATION?
It worked for me without any complications.
Where is a pitfall?
_____________
Code for TallyGenerator -
None... we don't use replication for replication
Anyway, wasn't meant to be a challenge, Noel... was just curious.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
If memory serves, the subscriber doesn't increment the seed when you do this, so in a failover, it would be out of synch.
-
Subscriber does no have any seed.
IDENTITY is not replicated if it's NOT FOR REPLICATION.
Subscriber just gets whatever value appears in replicated database.
Including the cases when identity was bypassed (IDENTITY_INSERT ON).
_____________
Code for TallyGenerator -
David was spot on. When you fail over the software that you use must be able to function exactly as it was when connected to the primary.
The "not for replication" conception Sergiy has, is not correct. IT *IS* replicated it is simply bypassed when the replication agent is inserting. Same thing for Trigger, Foreign Keys, etc.
Again, Sergiy you are showing your inexperience in this subject.
* Noel -
Jeff, I am sorry if I missinterpreted your intentions.
There are many cases in which may things that seemed "normal" in non-replicated databases must be changed when replication is in place. This IDENTITY thing happens to be one of them.
I have had to deal with *a lot* of replicated databases latetly therefore most of these issues are very fresh on my mind and thus I thought I could provide some value to this thread
* Noel -
Then you're wasting your time performing replication, as failover won't be pretty when the subscriber-turned-publisher doesn't have a seed.
My earlier suggestion for using blocks of identity values on different tables will also work here, with a slight twist (the twist being that it can technically be considered the same table, depending on how you look at it). You can place a seed on the subscriber that is far outside the range of potential values on the publisher. While failovers will cause the identity column values to be out of sequence, as long as you make the blocks sufficiently large they should retain uniqueness without overlap. So put a seed of 1 on the original publisher, a seed of 1 trillion on the original subscriber, and unless you exceed a trillion records, you won't have issues with inserts. You can even increase that seed if a trillion isn't enough.
-
You probably mixing mirroring with replication.
Replica should not be populated from anywhere but original database.
If you use right thing in a wrong way it will fail, of course.
In my experience we have local mirror and 2 replicas on remote servers. REplicas are just for cutting traffic for remote clients queries, not for replacing failed source database.
If it's failed replicas is just populated from mirror instead.
_____________
Code for TallyGenerator -
Your "guess" is wrong again! We are *not* using mirroring. If our Primary site goes down we move our clients to the replicas! Simple as that. Meanwhile we use replicas for reporting.
Mirroring was not an option for us. We use multiple databases per app.
* Noel
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply