Locking question

Question

Locking question

Viewing 15 posts - 31 through 45 (of 48 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

So... someone wanna ask the OP what's up?

--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)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Eamon,

I was a bit out of line... I should have asked why you need to do this so we can come up with the best solution for your circumstance.

So, why do you need to do this? And, will the database ever need to use "replication" that's built into SQL Server?

--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)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

No problem, Noel... it's easy to see how I might have been taken because I was pretty adamant about the whole IDENTITY thing.

So, let me ask the question (looking to learn something because I have ZERO knowledge of the "built in" replication of SQL Server)... if you need numeric "autonumbering" ID's as what an IDENTITY column offers, but don't want to use IDENTITY because of whatever problems may occur with replication and you really want to use replication, do you use a sequence table as a substitute? Or something else?

To me, anyway, using a sequence table means that you have to know how many rows you want to insert, get the NextID, increment the NextID for the number of rows you figure will be inserted, and add the NextID you got early to the "row number"-1 (a bit of a bugger in SQL Server 2000 and 7) of the result set you want to insert... seems like a heck of a lot of overhead and extra code. Sure, you could build a "NextSeq" function similar to what Oracle has (if it's properly written with UPDATE @var=NextID+@Increment as we've both stated), but, even with that, it sure seems like it'll make a hotspot and it sure seems like you'd be in a world of hurt if any GetNextID attempt done in a long winded transaction because of the blocking it would cause on the sequence table.

I suppose you could even write a trigger to do it whether 1 or multiple rows were inserted... but then how would you feedback an ID to a GUI that requires it? And, wouldn't that force RBAR on everything including batch inserts?

I can see another work around that uses a sequence table that has an IDENTITY column and a nightly cleanup that deletes all but the last row, but it sure does seem like a lot of kludge work...

Again, I'm not asking all these questions as a challenge to anyone's knowledge or experience... I really want to know how folks handle the problem (substitution for) with IDENTITY columns in replication because, when it comes to replication, I have ZERO experience.

I'm going to go back and study everyone's responses (especially why folks have problems with the "Not for Replication" thing) on this thread whilst perusing BOL, but I'll sure appreciate a bit of knowledge on what others do to make "autonumbering" columns to get around replication problems.

Thanks, folks...

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 4

You are trying to do mirroring using replica functionality.

Don't think it's right.

Generally speaking replica should not be a copy of publication. By default it does not include anything but tables. Selected tables.

And replication is asynchronous. It depends on jobs running under another application - SQL Server Agent. And job can run on hell knows which schedule.

Not sure about Transactional Log - does it copy it at all?

> Meanwhile we use replicas for reporting.

Exactly as we do.

Customers on another side of the Globe use replica of our database to access data they need. We don't copy whole database to them, just the part they need.

> Mirroring was not an option for us. We use multiple databases per app.

Why?

We use 16 databases per application (hate it!) And whole thing is mirrored on another server having good local network connection to the first one.

When one of servers went down everything was switched to a second one and nobody even noticed at the time.

Only after 2 days monitoring tools reported big load on CPU (second server has its own things to do) and guys realised that one of servers is down.

Recommend - it's nice feature.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

I found this in Books Online...

When the IDENTITY property is used with CREATE TABLE, Microsoft® SQL Server™ uses the NOT FOR REPLICATION option of CREATE TABLE to override the automatic incrementing of an identity column. Usually, SQL Server assigns each new row inserted in a table a value that is some increment greater than the previous highest value. However, if the new rows are replicated from another data source, the identity values must remain exactly as they were at the data source.

...and, keep in mind that I know squat about the built in replication... but doesn't that suggest that if you want a hot swapable DB that will operate as if it were the original DB, that replication won't do it right because the IDENTITY columns will not be auto-incrementing? Doesn't that suggest that some form of mirroring would be more effective for hot swapable DB's than replication... especially since mirroring would allow the use of IDENTITY columns?

Shouldn't replication just be used for publishing data remotely and mirroring should be used for hot swapable DB's/Servers?

Or, am I just not getting it?

--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)

noeld SSC Guru Points: 96590 More actions · Answer 6

Jeff,

The First problem with mirroring is that the destination DB is *Not* usable ( unless you create snapshots which is another kludge )

Mirroring *in my opinion* is a good choice for single databases that need to have Clustering functionality without the expensive hardware.

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 7

Sergiy

>>Why not use mirroring with multiple databases<<

It could potentially failover a single database out of a group and that is a problem!!!

*NOT* recomemeded.

Replication, for your information can run a job "continuosly". True that the latency is higher and that not all kinds of changes are replicated but the replica is "usable" ( see my answer to Jeff above )

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 8

>>.. it sure seems like it'll make a hotspot and it sure seems like you'd be in a world of hurt if any GetNextID attempt done in a long winded transaction because of the blocking it would cause on the sequence table. <>I suppose you could even write a trigger to do it whether 1 or multiple rows were inserted... but then how would you feedback an ID to a GUI that requires it? And, wouldn't that force RBAR on everything including batch inserts?<>Again, I'm not asking all these questions as a challenge to anyone's knowledge or experience... I really want to know how folks handle the problem (substitution for) with IDENTITY columns in replication because, when it comes to replication, I have ZERO experience.<<

For some people Identity partitioning suffice. For some others Automatic ranges suffice too but for others Like in my case it is not a good compromise so we have to resort to this kind of things.

The Hotspot issue is controlable and so far I have found that following the above rules do not create such a problem.

* Noel

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Noel, here is your "lack of knowledge"

We experienced failure of subset of databases, not all of them.

When functionality of primary server was restored databases where switched back without any interruption.

We were not experiencing any problem with it.

Worked as charm.

Why it's not recommended?

What is the problem here you're talking about?

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

For batch inserts you Call GetNextID with a @count parameter *once*

Yep, I'm aware of that, but then you must calculate the count from NextID to NextID+@count during a single insert for a batch... not fun in SQL Server 2000. What are you doing there?

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 11

Noel, I wonder, what if database schema is changed?

New column added, datatype for the column is changed, new table(s)/view(s)/procedure(s) created.

As far as I know this will not be picked up automatically and will require manual changes in replica and in replication process.

Is there any lack of knowledge from my side?

_____________
Code for TallyGenerator

noeld SSC Guru Points: 96590 More actions · Answer 12

Once again you are showing inexperience. If a single database fails over and you have a "view" pointing to another of the set what would you think happens ?

Please refer to the best practices before trying to type again.

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 13

Once againg you lack of knowledge is revealed.

DDL changes are supported in SQL Server 2005 replication.

Please can you just stop "trying" to guess stuff.

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 14

What *ususally* happens for batch inserts is that client side gets the limits and prepare the inserts there.

If the need arises to do it on the server side we bite the bullet using the well known tally table technique.

I understand your point of view and I am very well aware of it. The multiple insert problems is also difficult to solve in 2000 with identity columns. 2005 comes to the rescue though with the OUTPUT clause of the DML statement. I am sure many "Triggers" out there will be replaced by that.

Just my $0.02

* Noel

Sergiy SSC Guru Points: 110208 More actions · Answer 15

Sorry, it's SQL Server 2000 forum.

Pay more attention to your words.

You post your suggestions to SQL 2000 users, and they don't work for them.

And what's so wrong about askung for your reasons?

I revealed mine and asked what I'm missing.

Why you are so frustrated about it?

I hope you are not like those jerks which start pollution and then blame you for their sins.

Thank you.

_____________
Code for TallyGenerator