Home > Articles > Databases > SQL Server

SQL Server Reference Guide

< Back Page 92 of 361 Next >

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.

Policy Storage

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:

syspolicy_policies
syspolicy_conditions
syspolicy_policy_group_subscriptions
syspolicy_policy_groups
syspolicy_system_health_state
syspolicy_policy_execution_history
syspolicy_policy_execution_history_details

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.

Figure 10

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:

Figure 11

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:

Figure 12

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:

Figure 13

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.

Figure 14

Once that runs, I see that the server in question meets the requirements of my Policy.

Figure 15

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:

Figure 10

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:

Figure 17

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.