Automatic Failover Involving Witness Instance
Mirrored Backups
SQL Server supports Mirrored Backup sets. Typically, mirrored backup sets have two to four mirror sets, each set containing all the volumes of every media family. Each mirror requires a separate backup device for each media copy. Mirrored backups are available on both disk and tape. All backup devices for a single backup or restore operation must be of the same type, disk, or tape. It is a must that you use similar devices that have the same properties and configuration. Insufficiently similar devices generate an error message with error code of 3212. To avoid the possibility of a device mismatch, use devices that are equivalent, such as, only drives with the same model number from the same manufacturer.
Database Snapshots
SQL Server 2005 introduces database snapshots, which are a read-only, static view of any given database. Each database snapshot is transaction-consistent with the source database as it existed at the moment of the snapshot’s creation. A snapshot persists until it is explicitly dropped by the database owner. Multiple snapshots can exist of a single database.
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 author.
]]>