www.extremeexperts.com connect with life vinod kumar technology evangelist - microsoft

www.ExtremeExperts.com

Connect with life

www.connectwithlife.co.in

Database Snapshot Internals

Vinod KumarTechnology Evangelist - Microsoftwww.ExtremeExperts.comhttp://blogs.sqlxml.org/vinodkumar

www.ExtremeExperts.comwww.ExtremeExperts.com

AgendaHow database snapshots workDatabase snapshot creation stepsPerformance considerations with write-intensive workloadsRestoring from a database snapshot


Row-1

Tran2 (Select)Tran1 (Update)

X-Lock S-Lock BlockedRow-1

Reader Writer Blocking


Database Snapshot – How it really works

1

Pages2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

CreditSS1 – Read-Only Database Snapshot

CREATE DATABASE CreditSS1 (…) AS SNAPSHOT OF Credit

Credit – DatabaseUSE CreditUPDATE… (pages 4, 9, 10)

USE CreditSS1SELECT… (pages 4, 6, 9, 10, 14)

4 9 101 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16


About Database Snapshots (1)

Database snapshots are read onlyYou can not change any dataYou can not create any new object

Transactional consistentConsistent as of a particular point-in-timeIncludes all metadata, tables, indexes, stored procs, etc.

If you create an index on a table in the source database after creating the database snapshot, the index will not be there in the database snapshot

Database snapshots have no transaction logOne data file for each corresponding data file in the source databaseYou can create more than one database snapshot on the same source databaseYou can create a database snapshot on a mirror database (not part of the discussion today)


About Database Snapshots (2)

Enterprise Edition and Developer Edition onlyMust reside on

The same instance as the source dbNTFS filesystem

CannotBackup / RestoreDetach / AttachDrop (or RESTORE WITH REPLACE) the source database if any database snapshot(s) exists on it. Need to drop the database snapshot(s) first.Reside on Fat32 filesystem / Raw PartitionsBe a source for another database snapshotBe source for Scalable Shared Database (SSD)Create db snapshots on model, master, and tempdb databases (but can create on msdb)


Sparse FilesDatabase snapshots use sparse files as data filesSparse files require NTFSLogical size of the sparse file is equal to the size of the corresponding data file of the source database, at the time of creating the database snapshot

But the sparse file doesn’t reserve all the space

Physical size is only the amount of space consumed by the original pages copied from the source databaseTo find the physical size of a sparse file

properties → size on diskselect * from fn_virtualfilestats(db_id(), null); and look for the column BytesOnDisk

You may over-allocate your disk space due to the fact that sparse files not actually reserve all the space they might need


Copy On Write

Database snapshots use the “COW” technology to maintain point-in-time dataCopy on FIRST write ONLYOnly the first change to a page will cause the page to be copied; the next billion changes are ‘free’


Database Snapshots Usage Scenarios

Point-in-time reportingOffloading reporting to mirror serverRestoring in a test environment in order to restart a test from the same initial database stateSafeguard against human / machine errors

Administrative errors: before executing a large batch job, schema change, etc.User errors


Database Snapshot creation steps

demo


Database Snapshot Creation StepsCREATE DATABASE … AS SNAPSHOT

command issuedCheckpointing occursDatabase snapshot files openedDatabase snapshot startupRecovery starts

Analysis phase Redo phaseUndo phase

Recovery finished


How many Database Snapshots can my system handle?

On a given source databaseThere is no built-in limitFactors to consider:

IO load (especially writes)Database sizeDatabase activity (especially writes)RAM


When is the database snapshot data current?

demo


When is the database snapshot data current?

Time

CREATE DATABASE … AS SNAPSHOT command issued

Database Snapshot creation completed

Data in the DB snapshot is current as of this time

Data that is committed in the source database as of the beginning of the analysis phase of the database snapshot recovery will be present in the database snapshot


Restoring from a Database Snapshot

aka “Reverting to a Database Snapshot”RESTORE DATABASE AdventureWorks FROM DATABASE_SNAPSHOT = 'AdvWorks_DBSS1'

Before restoring from a database snapshot, all other database snapshots must be droppedEven though you can restore from a database snapshot, it is not an alternative to regular database backups

Not useful in media failure

The transaction log is rebuilt during restore Can not roll forward using previous transaction log backups after restoring from a database snapshot


Querying a Database Snapshot

Querying a database snapshot requires reading pages from the

Source databaseDatabase snapshot sparse file

The database snapshot is a separate database for all practical purposesThe same physical pages on disk are loaded and stored separately in the buffer pool for the source database and the database snapshot


Querying a DB Snapshot

demo


Feedback / QnA

Your Feedback is Important!Please take a few moments to fill out our

online feedback form at: << Feedback URL – Ask your organizer for this in advance>>

For detailed feedback, use the form at http://www.connectwithlife.co.in/vtd/helpdesk.aspx

Or email us at [email protected]

Use the Question Manager on LiveMeeting to ask your questions now!


© 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the

date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

www.extremeexperts.com connect with life vinod kumar technology evangelist - microsoft

Documents