Integrity maintenance rule

Purpose:

• To detect and report any corruption or inconsistencies in the database.

Why It’s Important:

• Running DBCC CHECKDB helps identify issues with data pages, tables, indexes, and the overall physical structure of the database.
• Detecting corruption early prevents data loss or operational downtime.
• Regular integrity checks are critical for ensuring database reliability and identifying problems before they become severe.

Integrity Summary Quick Access + Include Exclude

Integrity Rule Configuration

1. Integrity Rule Summary
   Displays an overview of the integrity rule, including its current configuration and status.
2. Include/Exclude Integrity Rule Tab
   Customize the integrity rule by specifying databases or database groups to include or exclude from the checks.
3. Navigation Between Rule Summary and Include/Exclude Tab
   Seamlessly toggle between the rule details summary and the Include/ Exclude tab for efficient configuration.
4. Configure Rule
   Opens the Rule Wizard, providing advanced options for fine-tuning the integrity rule settings.

Include Exclude integrity rule tab

See common DB Source selection for template and Custom server – include exclude for custom server level.

Integrity rule wizard step 1 – Main Options

Integrity Rule Advanced Configuration

1. Toggle Options

Configure the following settings to fine-tune the integrity rule:

a.    Physical Only:

• Limit checks to the physical structures of the database.
• Uses the PHYSICAL_ONLY option in SQL Server DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE commands.

b.    Data Purity:

• Validate column values to ensure they are not invalid or out- of-range.
• Uses the DATA_PURITY option in DBCC CHECKDB and DBCC CHECKTABLE.

c.     No Indexes:

• Exclude nonclustered indexes from integrity checks.
• Uses the NOINDEX option in DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE, and DBCC CHECKALLOC.

d.    Extended Logical Checks:

• Perform extended logical checks for more comprehensive validation.
• Uses the EXTENDED_LOGICAL_CHECKS option in DBCC CHECKDB.
• Note: Cannot be combined with the Physical Only option.

e.    Table Lock:

• Use table locks instead of internal database snapshots during checks.
• Uses the TABLOCK option in DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE, and DBCC CHECKALLOC.

f.    Max Degree of Parallelism (MaxDOP):

• Specify the number of CPUs to use during database checks.
• Uses the MAXDOP option in DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE.

2. Time Limit

1. Set the maximum time, in seconds, after which no further commands are executed.
2. Default: No time limit.

3. Time Delay

1. Define a delay, in seconds, between index commands to reduce resource contention.
2. Default: No delay.

4. Max Degree of Parallelism (MaxDOP)

1. Specify the number of CPUs to use for index rebuild operations.
2. If unspecified, the global maximum degree of parallelism is used.
3. Uses the MAXDOP option in the SQL Server ALTER INDEX command.

5. Lock Timeout (Seconds)

1. Set the maximum time, in seconds, that a command waits for a lock to be released.
2. Default: No time limit.
3. Uses the SET LOCK_TIMEOUT statement in SQL Server.

6. Lock Message Severity

Define the severity level for lock timeout and deadlock messages:

7.  Execution Order

Specify the order in which databases are processed during integrity checks:

Integrity rule wizard step 2 – Schedule

See Scheduling options in UI for details on how to use the schedule.

Differences between template integrity rule to custom server integrity rule

Custom server – Include Exclude integrity rule tab

Rule Configuration Options

1. Close Dialog and Toggle Rule
   Exit the configuration dialog while enabling or disabling the rule for the associated server or databases.
2. Navigate to Rule Details
   Access the detailed configuration settings of the selected rule for review or modifications.
3. Choose Predefined Database Group Type
   Select a predefined grouping of databases for streamlined rule application.
4. Select Availability Groups
   Choose specific availability groups from the server to include in the rule.
5. Select Replica Types
   Specify the types of replicas within the availability groups to participate in the rule:
   • PRIMARY: Include primary replicas.
   • SECONDARY: Include secondary replicas.
6. Select Specific Objects
   Choose specific objects (e.g., tables, indexes) to include in the rule for granular control.
7. Include or Exclude Databases
   Customize the rule by including or excluding specific databases on the customized server.
8. Final List of Databases
   View the final list of databases derived from the chosen group types, availability groups, replica types, and inclusion/exclusion criteria.