Edit

Share via

Monitor and Enforce Best Practices by Using Policy-Based Management

Applies to: SQL Server

Policy-Based Management allows you to monitor best practices for the SQL Server Database Engine. 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 Administer Servers by Using Policy-Based Management. A set of sample policy files is available 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.

Policy and Rules for Database Engine

The following table lists the policies included in the set of sample policies and information about the best practices rules 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 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
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
Guest Permissions Guest Permissions on User Databases
Last Successful Backup Date Outdated Backup
Public Not Granted Server Permissions Server public Permissions
SQL Server 64-bit Affinity Mask Overlap Correct Affinity Mask and Affinity Input and Output 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 Choose an 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 Password Expiration SQL Server Login Password Expiration
SQL Server Password Policy SQL Server Login Password Strength
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 Failed I_O Request Error Detect Failed Input and Output Requests
Windows Event Log I_O Delay Warning Check Disk Input and Output Subsystem for IO Delay Problems
Windows Event Log I_O Error During Hard Page Fault Error Input and Output Error During Hard Page Fault
Windows Event Log Read Retry Error Check Disk Input-Output Subsystem for Read Retry Problems
Windows Event Log Storage System I_O Timeout Error Storage System Input-Output Time-out
Windows Event Log System Failure Error Unexpected System Failures

See Also

Working with Policy-Based Management Facets