Monitoring and Enforcing Best Practices by Using Policy-Based Management

Policy-Based Management lets you monitor best practices for the SQL Server Database Engine. SQL Server 2008 provides a set of policy files that you can import as best practice policies, and then evaluate the policies against a target set that includes instances, instance objects, databases, or database objects. You can evaluate policies manually, set policies to evaluate a target set according to a schedule, or set policies to evaluate a target set according to an event. For more information about Policy-Based Management, see Administering Servers by Using Policy-Based Management.

Policy and Rules for Database Engine

The following table lists the policies that are included with the installation of SQL Server 2008 and includes information about the best practices rules that each policy evaluates. The policies are stored as XML files and must be imported into SQL Server. For more information about how to import policies, see How to: Export and Import a Policy-Based Management Policy.

Policy name	Best practice rule
Asymmetric Key Encryption Algorithm	Asymmetric Keys Encryption Strength
Backup and Data File Location	Backup Files Must Be on Separate Devices from the Database Files
CmdExec Rights Secured	Restrict CmdExec Rights to sysadmin
Data and Log File Location	Place Data and Log Files on Separate Drives
Database Auto Close	Set the AUTO_CLOSE Database Option to OFF
Database Auto Shrink	Set the AUTO_SHRINK Database Option to OFF
Database Collation	Set the Collation of User-defined Databases to Match Those of the master and model Databases
Database Page Verification	Set the PAGE_VERIFY Database Option to CHECKSUM
Database Page Status	Check Integrity of Database with Suspect Pages
File Growth for SQL Server 2000	Check File Size and Growth for SQL Server 2000
Guest Permissions	Guest Permissions on User Databases
Last Successful Backup Date	Outdated Backup
Public Not Granted Server Permissions	Server public Permissions
SQL Server 32-bit Affinity Mask Overlap	Correct Affinity Mask and Affinity I/O Mask Overlap
SQL Server 64-bit Affinity Mask Overlap	Correct Affinity Mask and Affinity I/O Mask Overlap
SQL Server Affinity Mask	Keep the Affinity Mask Default Value
SQL Server Blocked Process Threshold	Increase or Disable Blocked Process Threshold
SQL Server Default Trace	Default Trace Log Files Disabled
SQL Server Dynamic Locks	Keep the Locks Configuration Option Default Value
SQL Server Lightweight Pooling	Disable Lightweight Pooling
SQL Server Login Mode	Authentication Mode
SQL Server Max Degree of Parallelism	Set the Max Degree of Parallelism Option for Optimal Performance
SQL Server Max Worker Threads for 32-bit SQL Server 2000	Verify Max Worker Threads Setting
SQL Server Max Worker Threads for 64-bit SQL Server 2000	Verify Max Worker Threads Setting
SQL Server Max Worker Threads for SQL Server 2005 and above	Verify Max Worker Threads Setting
SQL Server Network Packet Size	Network Packet Size Should Not Exceed 8060 Bytes
SQL Server Open Objects for SQL Server 2000	Set the open objects Option to 0 for SQL Server 2000
SQL Server Password Expiration	SQL Server Login Password Expiration
SQL Server Password Policy	SQL Server Login Password Strength
SQL Server System Tables Updatable	Disable Updates for System Tables for SQL Server 2000
Symmetric Key Encryption for User Databases	Symmetric Keys on User Databases
Symmetric Key for master Database	Symmetric Keys on System Databases
Symmetric Key for System Databases	Symmetric Keys on System Databases
Trustworthy Database	Trustworthy Bit
Windows Event Log Cluster Disk Resource Corruption Error	Detect SCSI Host Adapter Issues
Windows Event Log Device Driver Control Error	Device Driver Control Error
Windows Event Log Device Not Ready Error	Device Not Ready Error
Windows Event Log Disk Defragmentation	Disk Fragmentation Error
Windows Event Log Failed I_O Request Error	Detect Failed I/O Request
Windows Event Log I_O Delay Warning	Check Disk I/O Subsystem for I/O Delay Problems
Windows Event Log I_O Error During Hard Page Fault Error	I/O Error During Hard Page Fault
Windows Event Log Read Retry Error	Check Disk I/O Subsystem for Read Retry Problems
Windows Event Log Storage System I_O Timeout Error	Storage System I/O Time-out
Windows Event Log System Failure Error	Unexpected System Failures

See Also

Concepts

Tutorial: Administering Servers by Using Policy-Based Management

Tutorial: Evaluating Best Practices by Using Policy-Based Management