Hidden Snapshots
In SQL Server 2000, transactions that hold locks can also block DBCC validation commands (e.g., CHECKTABLE, CHECKDB, CHECKALLOC). SQL Server 2000 DBCC commands can't finish running until the locks are released.
In SQL Server 2005, however, all the DBCC validation commands use database snapshot technology to prevent the validation operation from interfering with ongoing database operations. The snapshot technology also lets the validation operation see the data as it was when the DBCC command was initiated, no matter how many changes are made to the data while the operation is under way. The SQL Server Database Engine creates a snapshot of the database when you initiate the CHECK command, and no locks are acquired on the objects being checked. The actual CHECK operation runs against the snapshot.
Unlike typical database snapshots, the "snapshot file" that contains the original page images isn't visible in the file system and its location is unconfigurable—it always uses space on the same volume as the database being checked. You can use internal database snapshots with DBCC validation commands only if your data directory is on an NTFS partition. If you don't use NTFS or don't want to use the space that the snapshot requires, you can prevent snapshot creation by using the WITH TABLOCK option in the DBCC command. In addition, when you use one of the REPAIR options to DBCC, no snapshot is created.
If you don't use the TABLOCK option, the DBCC validation commands don't interfere with other work taking place in a database and so are considered online operations. With TABLOCK, however, a Shared Table lock is acquired for each table as it's processed and blocks concurrent modification operations. Similarly, if modification operations are in progress on one or more tables, a DBCC validation command that uses the TABLOCK option is blocked until the modification transaction is completed.
Because snapshots are usually created very quickly and need minimal disk space, you might be able to create a new snapshot every time you run certain reports. However, there are always exceptions. For example, snapshots of a very heavily updated database might grow quite large. As updates occur on the source database, the original version of the page is written to the snapshot, so lots of updates could not only take lots of space, but could also incur noticeable overhead due to the extra write operation that takes place for every write in the source.
In a heavily updated database, the snapshot-might not be created instantaneously. The first thing SQL Server does when creating a snapshot is to run recovery on the source database, in case transactions are in progress when the snapshot is created. Because a snapshot comprises only committed data, in-progress transactions are effectively rolled back in the snapshot database to their earlier committed state. Not only could this recovery take time on a very busy system, but your snapshot might start out with a substantial amount of used space instead of being almost empty, as it would be if the database weren't so heavily updated.
Mirror, Mirror
An upcoming new technology in SQL Server 2005 that will increase availability is database mirroring. Although the initial release of SQL Server 2005 doesn't support mirroring, the capability should be available in the first service pack, which might be out by the time you read this.
Mirroring keeps a hot standby of a production database ready and available by duplicating all write operations to a mirror location. Every time SQL Server writes to the log of the source database, which is called the principal, it writes the same information to the mirror copy. Mirrors are meant to be used as a standby only if the principal fails and can't be accessed directly, even for reading. However, you can create and read from a snapshot of the mirror, basically turning the mirror into a reporting server. Because the principal server always maintains the mirror, creating a snapshot of the mirror puts no extra load on the principal. An extra load is put on the mirror, but because the mirror isn't being used for any other purpose, the impact might be barely noticeable.
Database snapshots aren't a solution for every read-only database need. You can't refresh the data in the snapshot, and if you want a snapshot to reflect recent changes, you must create a new snapshot. However, because snapshots are usually quick to create, they're often a nice solution when you have an immediate need for data access, can't wait for online activities to stop, and don't want to interfere with online activities.