642
Part V: Enterprise Data Management
When a user reads from the database snapshot, SQL Server accesses the in-memory
bitmap and finds out if the data it needs exists on the source database or database
snapshot. If the data page was not updated, it exists on the source database, and SQL
Server reads it from the source database. Figure 24-5 shows the read operation accessing
the updated page from the database snapshot and the remaining pages from the source
database.FIGURE 24-5
Users querying the database snapshot, accessing the updated pages from the data-
base snapshot, and unchanged pages from the source database.Database Snapshot
Data PageSource DatabaseData Page at the time
snapshot was created
Updated Data PageUnallocated PageData PageAs discussed earlier, you cannot backup or restore database snapshots, nor can you
attach or detach database snapshots. You can run reports against the database snap-
shot and can drop it when you no longer need it, it becomes too big, or the disk on
which the database snapshot is located runs out of space and the database snapshot
becomes suspect.One of the great things about the implementation of database snapshots within Microsoft
SQL Server is that even though a mirrored database cannot be read, a database snapshot
can be taken of the mirrored database, and that snapshot can be read. This leads to some
great options when it comes to moving all nightly and point-in-time reporting from a
production database to another server. Simply set up database mirroring between the two
servers (see Chapter 27, “Database Mirroring,” for more information about database mir-
roring); then create a database snapshot on the mirrored database. Users can connect to
the snapshot of the mirrored database and run reports from a point-in-time copy. You can
refresh a snapshot of a mirrored database by deleting the original snapshot and creating
a new one.c24.indd 642c24.indd 642 7/31/2012 9:25:49 AM7/31/2012 9:25:49 AM
http://www.it-ebooks.info