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.name, sp.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	http://go.microsoft.com/fwlink/?LinkId=116384
SQL Server System Tables Updatable	Microsoft Best Practices: Configuration	http://go.microsoft.com/fwlink/?LinkId=116352
Backup and Data File Location	Microsoft Best Practices: Maintenance	http://go.microsoft.com/fwlink/?LinkId=116373
Database Page Status	Microsoft Best Practices: Maintenance	http://go.microsoft.com/fwlink/?LinkId=116379
Database Page Verification	Microsoft Best Practices: Maintenance	http://go.microsoft.com/fwlink/?LinkId=116333
Last Successful Backup Date	Microsoft Best Practices: Maintenance	http://go.microsoft.com/fwlink/?LinkId=116361
Read-only Database Recovery Model	Microsoft Best Practices: Maintenance	http://go.microsoft.com/fwlink/?LinkId=116383
Data and Log File Location	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116362
Database Auto Close	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116338
Database Auto Shrink	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116337
Database Collation	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116336
File Growth for SQL Server 2000	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116378
SQL Server 32-bit Affinity Mask Overlap	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116381
SQL Server 64-bit Affinity Mask Overlap	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116381
SQL Server Affinity Mask	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116357
SQL Server Blocked Process Threshold	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116356
SQL Server Dynamic Locks	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116358
SQL Server I/O Affinity Mask For Non-enterprise SQL Servers	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116381
SQL Server Lightweight Pooling	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116350
SQL Server Max Degree of Parallelism	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116335
SQL Server Max Worker Threads for 32-bit SQL Server 2000	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116324
SQL Server Max Worker Threads for 64-bit SQL Server 2000	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116324
SQL Server Max Worker Threads for SQL Server 2005 and above	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116324
SQL Server Network Packet Size	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116360
SQL Server Open Objects for SQL Server 2000	Microsoft Best Practices: Performance	http://go.microsoft.com/fwlink/?LinkId=116334
Asymmetric Key Encryption Algorithm	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116370
CmdExec Rights Secured	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116363
Guest Permissions	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116354
Public Not Granted Server Permissions	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116364
SQL Server Login Mode	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116369
SQL Server Password Expiration	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116332
SQL Server Password Policy	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116331
Symmetric Key Encryption for User Databases	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116328
Symmetric Key for master Database	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116329
Symmetric Key for System Databases	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116329
Trustworthy Database	Microsoft Best Practices: Security	http://go.microsoft.com/fwlink/?LinkId=116327
Windows Event Log Cluster Disk Resource Corruption Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116377
Windows Event Log Device Driver Control Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116371
Windows Event Log Device Not Ready Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116349
Windows Event Log Disk Defragmentation	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116353
Windows Event Log Failed I/O Request Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116385
Windows Event Log I/O Delay Warning	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116375
Windows Event Log I/O Error During Hard Page Fault Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116355
Windows Event Log Read Retry Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116339
Windows Event Log Storage System I/O Timeout Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116330
Windows Event Log System Failure Error	Microsoft Best Practices: Windows Log File	http://go.microsoft.com/fwlink/?LinkId=116326
Surface Area Configuration for Database Engine 2005 and 2000 Features	Microsoft Off by Default: Surface Area Configuration	http://go.microsoft.com/fwlink/?LinkId=117322
Surface Area Configuration for Database Engine 2008 Features	Microsoft Off by Default: Surface Area Configuration	http://go.microsoft.com/fwlink/?LinkId=117323
Surface Area Configuration for Service Broker Endpoints	Microsoft Off by Default: Surface Area Configuration	http://go.microsoft.com/fwlink/?LinkId=117326
Surface Area Configuration for SOAP Endpoints	Microsoft Off by Default: Surface Area Configuration	http://go.microsoft.com/fwlink/?LinkId=117327