Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Import SQL Server 2008 Database Engine Policies with Policy Based Management


By:   |   Last Updated: 2010-01-26   |   Comments (1)   |   Related Tips: 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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 

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

 

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

 

 


Learn more about SQL Server tools