SQL Server 2000 & 2005 Clustering

Uses of Database Snapshot

Using database snapshots with database mirroring permits you to make the data on the mirror server accessible for reporting with improved performance. In some cases, running queries on the mirror database might free up resources on the principal.

Access to historical data for report generation, a database snapshot provides a static view of a database. A snapshot can be useful to extend access to “historical” data from a particular point in time.

Creating a snapshot on the database would help protecting the administrative errors, like doing major updates, bulk updates, or dropping a table.

Spars Files

SnapShots are stored in spars files. Initially, these spars files are empty shells that do not contain any data, but as data updates happen on the database, these spars files fill-up with snapshot pages (Original data pages). If a database snapshot runs out of space, it is marked as SUSPECT. In this situation, the snapshot becomes unusable and must be dropped.

Create a SnapShot

CREATE DATABASE PUBS_SS ON
( NAME = PUBS_Data,
FILENAME = ‘D:MSSQLMSSQLDATAPUBS_DATA_1000.SS’)
AS SNAPSHOT OF PUBS;
GO

How SnapShot Works

When there is an update on the database, before it writes to the original page, the original page is copied to the SnapShot and then the database page is updated. The size of snapshot spars file depends on what amount of pages are updated in the database during the life of the SnapShot.

If database updates are confined to a small subset of pages that are updated very often, then snapshot growth slows down over a period of time. As once the original page is copied to snapshot, any updates to that specific page will not affect the snapshot anymore. But in the reverse case, if most of the original pages are being updated then snapshots size grows at fast rate.

Restoring from a SnapShot

RESTORE DATABASE PUBS from
DATABASE_SNAPSHOT = ‘ PUBS_SS’
GO



Copyright 2004 by the author. Published with the express permission of the autho




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |