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.
Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry in roles including programmer, developer, analyst, and database administrator.
LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010. While at Confio, his research and experience helped to create the initial versions of the software now known as SolarWinds Database Performance Analyzer. LaRock joined the SolarWinds family through the acquisition of Confio in 2013.
LaRock is also the Immediate Past President of the Professional Association for SQL Server (PASS) and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses his time working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle, MySQL, SAP, and DB2, making it his mission to give IT and data professionals longer weekends.