Import SQL Server 2008 Database Engine Policies with Policy Based Management


By:   |   Updated: 2010-01-26   |   Comments (1)   |   Related: More > Policy Based Management

Problem

SQL 2008 introduced Policy Based Management (PBM), allowing for database administrators to have greater control over their environment through the use of policies. SQL 2008 comes with over fifty (50) policies, but none are installed by default. You need to import these policies manually in order to benefit from their use.  In this tip I will walk you through the process of importing these default policies.

Solution

Importing the policies is straightforward using SQL Server Management Studio (SSMS). With only a few mouse clicks you can begin to import the policies (stored as XML files) through SSMS and enable their use.


Inside of SSMS, navigate to the policies folder and right-click.

Select ‘Import Policy...'

Click on the ellipses, and navigate (if necessary) to where the policies are installed. On a default installation this would most likely be the C:\Program Files\Microsoft SQL Server\100\Tools\Policies directory. Inside that directory you will see three directories: AnalysisServices, DatabaseEngine, ReportingServices. We will focus on the policies inside of the DatabaseEngine directory. The final path will then become:

C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033

We will import the ‘Last Successful Backup Date' policy. Click ‘Open', then ‘OK'. Your policy has now been imported. Inside of SSMS you should see the policy listed under the Policies folder and also see the condition listed under the Conditions folder.

You can now right-click on the policy and select ‘Evaluate', and the policy will run against the current instance.


Result

Here is a screenshot of a sample result set returned by the policy.


Summary

Here is a list of Database Engine policies that you can import.

  • Asymmetric Key Encryption Algorithm.xml
  • Backup and Data File Location.xml
  • CmdExec Rights Secured.xml
  • Data and Log File Location.xml
  • Database Auto Close.xml
  • Database Auto Shrink.xml
  • Database Collation.xml
  • Database Page Status.xml
  • Database Page Verification.xml
  • File Growth for SQL Server 2000.xml
  • Guest Permissions.xml
  • Last Successful Backup Date.xml
  • Public Not Granted Server Permissions.xml
  • Read-only Database Recovery Model.xml
  • SQL Server 32-bit Affinity Mask Overlap.xml
  • SQL Server 64-bit Affinity Mask Overlap.xml
  • SQL Server Affinity Mask.xml
  • SQL Server Blocked Process Threshold.xml
  • SQL Server Default Trace.xml
  • SQL Server Dynamic Locks.xml
  • SQL Server I_O Affinity Mask For Non-enterprise SQL Servers.xml
  • SQL Server Lightweight Pooling.xml
  • SQL Server Login Mode.xml
  • SQL Server Max Degree of Parallelism.xml
  • SQL Server Max Worker Threads for 32-bit SQL Server 2000.xml
  • SQL Server Max Worker Threads for 64-bit SQL Server 2000.xml
  • SQL Server Max Worker Threads for SQL Server 2005 and above.xml
  • SQL Server Network Packet Size.xml
  • SQL Server Open Objects for SQL Server 2000.xml
  • SQL Server Password Expiration.xml
  • SQL Server Password Policy.xml
  • SQL Server System Tables Updatable.xml
  • Surface Area Configuration for Database Engine 2005 and 2000 Features.xml
  • Surface Area Configuration for Database Engine 2008 Features.xml
  • Surface Area Configuration for Service Broker Endpoints.xml
  • Surface Area Configuration for SOAP Endpoints.xml
  • Symmetric Key Encryption for User Databases.xml
  • Symmetric Key for master Database.xml
  • Symmetric Key for System Databases.xml
  • Trustworthy Database.xml
  • Windows Event Log Cluster Disk Resource Corruption Error.xml
  • Windows Event Log Device Driver Control Error.xml
  • Windows Event Log Device Not Ready Error.xml
  • Windows Event Log Disk Defragmentation.xml
  • Windows Event Log Failed I_O Request Error.xml
  • Windows Event Log I_O Delay Warning.xml
  • Windows Event Log I_O Error During Hard Page Fault Error.xml
  • Windows Event Log Read Retry Error.xml
  • Windows Event Log Storage System I_O Timeout Error.xml
  • Windows Event Log System Failure Error.xml
Next Steps
  • Navigate to an instance of SQL 2008 and start importing policies.


Last Updated: 2010-01-26


get scripts

next tip button



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

View all my tips
Related Resources





Comments For This Article




Tuesday, May 07, 2013 - 1:37:49 PM - Mark Back To Top (23772)

 

A note on location of policies, on the 64x they reside here

 

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies

 

 



download


Recommended Reading

Why SYSPOLICY_PURGE_HISTORY job fails in SQL Server 2008 Failover Cluster Instance

Using Policy Based Management in SQL Server

Internal SQL Server Certificates have expired is this an issue

Enforce SQL Server Database Naming Conventions Using Policy Based Management

Configuring Alerts for SQL Server Policy Based Management





get free sql tips
agree to terms


Learn more about SQL Server tools