sql server redux

[Frank D. Engel, Jr.]

I just found this article, which you might find interesting with regard 
to this topic:

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: use-revolution-bounces at lists.runrev.com
> [mailto:use-revolution-bounces at lists.runrev.com]On Behalf Of Mark D.
> Clark
> Sent: Monday, September 20, 2004 14:59
> To: use-revolution at lists.runrev.com
> 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
> use-revolution at lists.runrev.com
> http://lists.runrev.com/mailman/listinfo/use-revolution
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> http://lists.runrev.com/mailman/listinfo/use-revolution
>
>
-----------------------------------------------------------
Frank D. Engel, Jr.  <fde101 at fjrhome.net>



___________________________________________________________
$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