Export (0) Print
Expand All
Expand Minimize

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

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

Collating User-defined Databases to Match Master 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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft