Performing database consistency checks on snapshots helps ensure the integrity and reliability of the databases on a SQL Server host while offloading the often-lengthy task onto a secondary SQL Server host. Key reasons why using a volume snapshot on a secondary SQL Server host is more efficient include:
-
Offloading the workload: CHECKDB is a resource-intensive operation that can significantly impact CPU and memory resources, thereby degrading performance for end-user workloads on the primary SQL Server host. By offloading the consistency check onto a secondary SQL Server host, these critical resources remain available for handling application requests, ensuring smoother performance for end users.
-
Verifying the database integrity: Consistency checks help prevent data corruption by identifying potential issues. If a consistency check is run on a secondary server and an issue is found, a consistency check can then be run on the primary SQL Server host. If no issues are found on the secondary SQL Server host, then no further action is needed on the primary host, conserving resources for application requests.
-
Finding resolutions to data corruption: If an issue is found on the secondary SQL Server host, a database administrator can experiment with possible solutions to fix the corruption prior to applying the fix to the primary SQL Server host. This provides a better testing platform that avoids potential further damage to the primary host database.
To accomplish this task, a clone of a snapshot must be made, which is then attached to a secondary SQL Server host. These steps can be found in the section on Using Crash-consistent Snapshots to Clone a New SQL Server Instance.
When to Perform Database-consistency Checks on Snapshots
The DBCC CHECKDB command is recommended to be run on a volume snapshot under the following circumstances:
After Restoring the Snapshot to a Test Environment
To ensure the snapshot does not impact the production system, restore the volume snapshot to a test or non-production environment before running DBCC CHECKDB. This allows you to validate the logical and physical integrity of the database without risking disruptions.
As Part of a Regular Integrity Check Process
If you're using volume snapshots for backups or disaster recovery, include DBCC CHECKDB as a scheduled task in your maintenance plan. This ensures periodic validation of database integrity.
Using the DBCC CHECKDB Command
The DBCC CHECKDB command is used in SQL Server to check the logical and physical integrity of all the objects in a specified database. To check the integrity of the entire database:
DBCC CHECKDB ([DatabaseName]);