By: Thomas LaRock | Comments (1) | Related: > 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 Server and start importing policies.
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
