http://www.devx.com/codemag/Article/21570
If my understanding of this is correct, then the new isolation level for SQL Server 2005 will be closer to the "normal" one used by PostgreSQL:
http://www.postgresql.org/docs/current/static/applevel-consistency.html
The essential information here is that if you can perform *all* of your reads within the SAME transaction, using the default SQL Server isolation level of READ COMMITTED, you will get a consistent snapshot of the database, since no other transaction will be able to change the information you are trying to read until the transaction finishes.
If the reads are in different transactions, then you will lose consistency. Consistency is also not guaranteed with some of the other isolation levels, but if you don't know what this is, then just don't change it -- the default will work fine.
On Sep 20, 2004, at 3:49 PM, Jay Madren wrote:
Mark,
I haven't investigated SQL Server snapshot backups before, but a quick look
through the docs and their web site seems to indicate that there's probably
some API or DMO call used to "freeze" the database at the time of the
snapshot. The MSDN site refers to some VC++ examples, but I couldn't locate
them. If you could find them, then you should be able to determine how they
are accomplishing it.
I'm curious, why do you want to do a snapshot backup instead of the normal
backup method. I understand that it's intended for high availability
scenarios and supposed to have minimal impact on the server, but you have to
have the right equipment to pull it off (3 way mirror or something like
that). Is this what you're working with?
Jay Madren
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Mark D. Clark Sent: Monday, September 20, 2004 14:59 To: [EMAIL PROTECTED] Subject: sql server redux
"Won't a transaction with appropriate serialization give you the same result, but without interrupting other users' ability to access the database during the snapshot process?"
Okay, this is where my nearly perfect ignorance comes to play--I don't really understand how a transaction with appropriate serialization would work for a snapshot. If other users are able to access the database, then the files are open, things are in a cache, and the snapshot won't be good for backup purposes--unless there is something here I'm missing (entirely possible!)
I thought that the actual server itself needs to be paused to do a good snap--much the same as in a backup scenario, just not taking quite so long (less than one second).
---
_______________________________________________ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
_______________________________________________ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
----------------------------------------------------------- Frank D. Engel, Jr. <[EMAIL PROTECTED]>
___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com
_______________________________________________ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
