Thursday, March 11, 2010

Retrieve Microsoft Best Practices Using Policy-Based Management

I wanted a list of Microsoft Best Practices the other day and I started to seach the web. Then I thought this would be a perfect use for Policy-Based Management. I had a new install of SQL Server 2008 so I navigated to the default directory "C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033" and imported all the policies.

Then I just ran the following query to get a list of all the policies along with a help link for additional information.

SELECT sp.name PolicyName,
       spc.name CategoryName,
       sp.help_link

FROM msdb.dbo.syspolicy_policies_internal sp JOIN
     
msdb.dbo.syspolicy_policy_categories_internal spc ON
       
sp.policy_category_id spc.policy_category_id

ORDER BY spc.namesp.name

You can also add the sp.description column for a short description about why it is considered best practice. Here is the results.


PolicyName

CategoryName

help_link

SQL Server Default Trace

Microsoft Best Practices: Audit


SQL Server System Tables Updatable

Microsoft Best Practices: Configuration


Backup and Data File Location

Microsoft Best Practices: Maintenance


Database Page Status

Microsoft Best Practices: Maintenance


Database Page Verification

Microsoft Best Practices: Maintenance


Last Successful Backup Date

Microsoft Best Practices: Maintenance


Read-only Database Recovery Model

Microsoft Best Practices: Maintenance


Data and Log File Location

Microsoft Best Practices: Performance


Database Auto Close

Microsoft Best Practices: Performance


Database Auto Shrink

Microsoft Best Practices: Performance


Database Collation

Microsoft Best Practices: Performance


File Growth for SQL Server 2000

Microsoft Best Practices: Performance


SQL Server 32-bit Affinity Mask Overlap

Microsoft Best Practices: Performance


SQL Server 64-bit Affinity Mask Overlap

Microsoft Best Practices: Performance


SQL Server Affinity Mask

Microsoft Best Practices: Performance


SQL Server Blocked Process Threshold

Microsoft Best Practices: Performance


SQL Server Dynamic Locks

Microsoft Best Practices: Performance


SQL Server I/O Affinity Mask For Non-enterprise SQL Servers

Microsoft Best Practices: Performance


SQL Server Lightweight Pooling

Microsoft Best Practices: Performance


SQL Server Max Degree of Parallelism

Microsoft Best Practices: Performance


SQL Server Max Worker Threads for 32-bit SQL Server 2000

Microsoft Best Practices: Performance


SQL Server Max Worker Threads for 64-bit SQL Server 2000

Microsoft Best Practices: Performance


SQL Server Max Worker Threads for SQL Server 2005 and above

Microsoft Best Practices: Performance


SQL Server Network Packet Size

Microsoft Best Practices: Performance


SQL Server Open Objects for SQL Server 2000

Microsoft Best Practices: Performance


Asymmetric Key Encryption Algorithm

Microsoft Best Practices: Security


CmdExec Rights Secured

Microsoft Best Practices: Security


Guest Permissions

Microsoft Best Practices: Security


Public Not Granted Server Permissions

Microsoft Best Practices: Security


SQL Server Login Mode

Microsoft Best Practices: Security


SQL Server Password Expiration

Microsoft Best Practices: Security


SQL Server Password Policy

Microsoft Best Practices: Security


Symmetric Key Encryption for User Databases

Microsoft Best Practices: Security


Symmetric Key for master Database

Microsoft Best Practices: Security


Symmetric Key for System Databases

Microsoft Best Practices: Security


Trustworthy Database

Microsoft Best Practices: Security


Windows Event Log Cluster Disk Resource Corruption Error

Microsoft Best Practices: Windows Log File


Windows Event Log Device Driver Control Error

Microsoft Best Practices: Windows Log File


Windows Event Log Device Not Ready Error

Microsoft Best Practices: Windows Log File


Windows Event Log Disk Defragmentation

Microsoft Best Practices: Windows Log File


Windows Event Log Failed I/O Request Error

Microsoft Best Practices: Windows Log File


Windows Event Log I/O Delay Warning

Microsoft Best Practices: Windows Log File


Windows Event Log I/O Error During Hard Page Fault Error

Microsoft Best Practices: Windows Log File


Windows Event Log Read Retry Error

Microsoft Best Practices: Windows Log File


Windows Event Log Storage System I/O Timeout Error

Microsoft Best Practices: Windows Log File


Windows Event Log System Failure Error

Microsoft Best Practices: Windows Log File


Surface Area Configuration for Database Engine 2005 and 2000 Features

Microsoft Off by Default: Surface Area Configuration


Surface Area Configuration for Database Engine 2008 Features

Microsoft Off by Default: Surface Area Configuration


Surface Area Configuration for Service Broker Endpoints

Microsoft Off by Default: Surface Area Configuration


Surface Area Configuration for SOAP Endpoints

Microsoft Off by Default: Surface Area Configuration

0 comments: