Monitor and Enforce Best Practices by Using Policy-Based Management

Policy-Based Management allows you monitor best practices for the SQL Server Database Engine. SQL Server 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 Administer 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 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 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 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

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 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

Working with Policy-Based Management Facets