Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
Microsoft SQL Server Administration
- The DBA Survival Guide: The 10 Minute SQL Server Overview
- Preparing (or Tuning) a Windows System for SQL Server, Part 1
- Preparing (or Tuning) a Windows System for SQL Server, Part 2
- Installing SQL Server
- Upgrading SQL Server
- SQL Server 2000 Management Tools
- SQL Server 2005 Management Tools
- SQL Server 2008 Management Tools
- SQL Azure Tools
- Automating Tasks with SQL Server Agent
- Run Operating System Commands in SQL Agent using PowerShell
- Automating Tasks Without SQL Server Agent
- Storage – SQL Server I/O
- Service Packs, Hotfixes and Cumulative Upgrades
- Tracking SQL Server Information with Error and Event Logs
- Change Management
- SQL Server Metadata, Part One
- SQL Server Meta-Data, Part Two
- Monitoring - SQL Server 2005 Dynamic Views and Functions
- Monitoring - Performance Monitor
- Unattended Performance Monitoring for SQL Server
- Monitoring - User-Defined Performance Counters
- Monitoring: SQL Server Activity Monitor
- SQL Server Instances
- DBCC Commands
- SQL Server and Mail
- Database Maintenance Checklist
- The Maintenance Wizard: SQL Server 2000 and Earlier
- The Maintenance Wizard: SQL Server 2005 (SP2) and Later
- The Web Assistant Wizard
- Creating Web Pages from SQL Server
- SQL Server Security
- Securing the SQL Server Platform, Part 1
- Securing the SQL Server Platform, Part 2
- SQL Server Security: Users and other Principals
- SQL Server Security – Roles
- SQL Server Security: Objects (Securables)
- Security: Using the Command Line
- SQL Server Security - Encrypting Connections
- SQL Server Security: Encrypting Data
- SQL Server Security Audit
- High Availability - SQL Server Clustering
- SQL Server Configuration, Part 1
- SQL Server Configuration, Part 2
- Database Configuration Options
- 32- vs 64-bit Computing for SQL Server
- SQL Server and Memory
- Performance Tuning: Introduction to Indexes
- Statistical Indexes
- Backup and Recovery
- Backup and Recovery Examples, Part One
- Backup and Recovery Examples, Part Two: Transferring Databases to Another System (Even Without Backups)
- SQL Profiler - Reverse Engineering An Application
- SQL Trace
- SQL Server Alerts
- Files and Filegroups
- Full-Text Indexes
- Read-Only Data
- SQL Server Locks
- Monitoring Locking and Deadlocking
- Controlling Locks in SQL Server
- SQL Server Policy-Based Management, Part One
- SQL Server Policy-Based Management, Part Two
- SQL Server Policy-Based Management, Part Three
- Microsoft SQL Server Programming
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
SQL Server Policy-Based Management, Part Three
Last updated Mar 28, 2003.
Policy-Based Management is a new feature starting with SQL Server 2008 that allows you to manage your SQL Server systems by intent, as I explained in Part One of this series. In Part Two of the series, I demonstrated a simple Policy that you can use on your own system. In this tutorial I’ll show you some of the Policies that come “out of the box” with SQL Server 2008, where you can store them and run them from, and even how to run them with PowerShell against one or more servers.
Last week I created a policy and clicked the “OK” button on a few screens — but where did the Policy go? And what is a Policy, really?
I’ll start with that last question. A Policy is all of the instructions the Policy engine needs to evaluate the system. In other words, it’s just text that has all of the targets, facets, conditions and evaluation methods that you defined in the last tutorial. Depending on where you have stored the Policies, they take two different forms.
The first place that you can store a Policy is within the msdb database on the server where you create them. That’s what happened last week when I
There are several tables in msdb where the data for the Policy are stored, and you can get at those using several system views:
I’ll explain these views a little further in later tutorials, but for now you can see what Policies you have (if any) by using this query:
SELECT name , description FROM msdb.dbo.syspolicy_policies
You can, of course, look at the Policies graphically in the same node in SQL Server Management Studio that you created them in — just navigate to Management | Policy Management | Policies.
You can also store a Policy as an XML file. To create the XML file, simply create the Policy as you did last week. Now right-click the Policy and select Export Policy from the menu that appears.
Name the file, and now you have a Policy stored in a file and the database. The interesting thing is that you can take that Policy and import it to another server’s msdb database by clicking on the Policies node and selecting “Import Policy” from the menu that appears:
SQL Server 2008 actually comes with many Policies when you install it, stored as files. The “Best Practices Analyzer” or BPA, from Microsoft was a product that evaluates various settings on a server and its databases for the “right” way to do things. When a setting didn’t match the best practices, you would see a report of that fact. When you think about it, that’s exactly what Policy-Based Management does. So the team at Microsoft converted all those BPA “rules” to Policies. You can find them here, if you took the defaults when you installed:
C:\Program Files\Microsoft SQL Server\100\Tools\Policies
You’ll see Policies for Analysis Services, the Database Engine and Reporting Services as well. If you didn’t create a Policy in the last tutorial, you can use these to follow along in this tutorial.
Evaluating a Policy against a single server
Last week I showed you how to run a Policy that you just created on the server you created it on. Now I’ll show you how to run a Policy from one server against another.
I’m starting in SQL Server Management Studio (SSMS) and I’ve connected to two servers that I have: Monitoring and CRM:
I originally created the “Corporate Naming Standards” Policy on the Monitoring server, so it is stored in the msdb database there. I want to run the Policy against the CRM server.
I’ll right-click the name of the CRM server, and select Policies | Evaluate from the menu that appears:
Once I make that selection, I get the panel to choose the Source for the Policies, which I change to Monitoring, and then the Policies on that server that I can run. I’ll select my “Corporate Security Standards” Policy in this case.
Once that runs, I see that the server in question meets the requirements of my Policy.
Evaluating a Policy against multiple servers
In SQL Server 2008, a new feature allows you to run commands and queries against a group of servers, rather than just one at a time. In the Registered Servers panel, I’ll right-click a “Folder” of servers (called “Development Environment”) and then select “Evaluate Policies...” from the menu that appears:
This time I’ll select a file — in this case, from the Best Practices Policies stored on my hard drive. I’ll select the one that states that the log file and the data file should be on separate drives:
After I click the Evaluate button I see that all my servers are in compliance.
Evaluating a Policy using PowerShell
I’ve mentioned PowerShell before on this site multiple times, and I’m finding more and more uses for it. One such use is that I can use PowerShell along with Policies to monitor my whole environment. I won’t cover how to use a “driver” file in this tutorial, since I’ve covered that already, but I will point out an interesting command in the PowerShell Provider for SQL Server 2008 that allows you to navigate SQL Server as if it were a drive letter.
In this example, I’ll set the directory to the name where my Policies are stored as files. From there, I’ll run the best practices Policy that checks to make sure that my backups are also being stored on a separate drive than my data files:
cd "C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033" Invoke-PolicyEvaluation -Policy "Backup And Data File Location.xml" -TargetServerName "$machineName\$instanceName"
In this case I had a variable called $machineName and another called $instanceName that I used for the target, but you could certainly replace that with the name of the server and instance or use a loop or control file for that.
In future articles, I’ll explain some more real-world uses for this feature, as well as how you can interpret the results of the execution.
InformIT Articles and Sample Chapters
There’s a lot more about PowerShell starting in this series of articles. You can read up on that before you move on to the next tutorial.
Books and eBooks
Ross Mistry has a great book on administration, which will help you understand the general settings that you can use as Facet choices.
The official documentation for PBM is here.