Policy-Based Management is
a new feature in SQL Server 2008 that allows you to define and
implement policies across the organization. Policy-Based Management was
initially called the Declarative Management Framework but was changed
before the final release of SQL Server 2008. First, we will show you
how to manually create a policy and then move on to some of the more
automated ways of creating a policy.
There are a few key terms you should be familiar with when discussing Policy-Based Management:
Target: Object that is being managed by a policy
Facet: Group of logical properties that can be applied to a target
Condition: A check that is evaluated by a policy
Policy: A condition that is applied to a given set of objects
Category: Group of policies that help you manage policy enforcement
When you look at the Policy
Management node in SQL Server Management Studio, you see three folders:
Policies, Conditions, and Facets. The folder structure forms a sort of
hierarchy of the objects required to use Policy-Based Management.
Facets are required in order to create conditions, and conditions are
required in order to create policies.
1. Manually Creating a Policy
To manually create a policy
you must first create a condition, and then you will be able to create
a policy that uses that condition. Once you have created a policy, you
can then place it in the appropriate category and apply it to one or
Let's start by creating a
condition that will be used in a policy. In this example, we will
create a condition that checks to see if a database is using the full
recovery model. To create a new condition, right-click the Conditions
folder and select New Condition. (The Conditions folder is located
under Policy Management in the Management node of the Object Explorer
in SQL Server Management Studio.) This will bring you to the Create New
Condition dialog box, as shown in Figure 1.
Figure 1. Policy-Based Management Create New Condition dialog box
Create the new condition by entering the following information:
Name: In the Name field, enter Full Recovery Model.
Notice that the dialog box title now says "Create New Condition – Full
Recovery Model," and the error displayed under the title bar changes
from saying that the name is not set to saying that the ExpressionNode
is not set.
Facet: Before you set the expression, change the Facet field to Database Maintenance by selecting it from the drop-down list.
Click the cell in the Field column and select @RecoveryModel from the
drop-down list. Leave the equals sign (=) in the Operator column. You
can see all the available operators by clicking on the drop-down list.
Click the drop-down list in the Value column and select Full. Notice
that the Value column changes to reflect the values that are
appropriate for the attribute that has been selected in the Field
column. For example, if you change the field to @LastBackupDate, a
calendar control would be displayed in the Value drop-down list. You
can also enter multiple expressions using AND/OR logic by selecting the next row and entering the appropriate information.
Optionally you can enter a description by selecting the Description
page on the left and entering it in the text box provided. Select the
Description page and type a brief description, such as Condition to check to make sure a database recovery model is set to Full.
Once you've entered all
the information correctly, the errors are removed from the top of the
dialog box, and the status changes to Ready as shown in Figure 2.
Figure 2. Completed Policy-Based Management Create New Condition dialog box
Click OK to finish
creating the condition. You should now see the new condition under the
Conditions folder in Policy-Based Management.
Now that you have created
a condition, you are ready to create a policy that can use the
condition. To create a new policy, right-click the Policies folder and
select New Policy. (The Policies folder is located under Policy
Management in the Management node of the Object Explorer in SQL Server
Management Studio.) This will bring you to the Create New Policy dialog
box as shown in Figure 3.
Figure 3. Policy-Based Management Create New Policy dialog box
Create the new policy by entering the following information:
Name: In the Name field enter Full Database Recovery Model.
Notice the title now says "Create New Policy – Full Database Recovery
Model," and the error displayed now states that the Condition is not
set instead of the Name.
Set the Check Condition option by clicking the drop-down list and
selecting Full Recovery Model. If this is your first time using
Policy-Based Management, it should be the only thing in the drop-down
you are allowed to select. Once the condition has been selected, you
can click the ellipsis next to the drop-down list to edit or review the
condition directly from the policy.
Once the condition has been set, the target is automatically set to
Every Database. You can exclude databases by clicking the drop-down
menu next to Every and selecting New Condition (see the completed
policy later in Figure 7-5).
This will allow you to create a condition you can use to exclude
certain databases based on given properties exposed in the database
facet. For example, you may want to create a condition that will
exclude read-only databases from a policy that verifies all databases
are using the full recovery model.
Use this drop-down list to select the evaluation mode. For this
example, we will be using On Demand. Selecting On Schedule will enable
you to either assign an existing schedule to run the policy or create a
new one. Also, selecting On Schedule will allow you to enable the
policy by selecting the Enabled check box located directly under the
policy name. Only enabled policies will be run by the scheduled job
that will be created to check the policies. Valid evaluation modes are
On Demand, On Change: Prevent, On Change: Log Only, and On Schedule.
The evaluation modes displayed in the drop-down list depend on the
facet you are using in the condition. All facets support On Change and
On Schedule, but On Change: Prevent relies on the facet being able to
use Data Definition Language (DDL) triggers to roll back the
transaction. On Change: Log Only relies on the ability of the facet
change to be captured by an event.
You can create a condition to exclude servers from the policy by using
the server facet. For example, you could create a condition that only
evaluates the policy on SQL Servers that are running the Enterprise or
Standard Edition. For this example, we will not be using a server
Select the Description page, as shown in Figure 4, to configure the remaining options.
Figure 4. Create New Policy dialog box Description page
The remaining options in Figure 4 are as follows:
For this example, we will leave the category set to Default. To change
the category, you would click the drop-down menu and select a defined
category or click the New button to create a new category. We will show
you how to manage categories later in this section.
Description: Optionally, you can define a description by entering it in the text box provided. Enter a brief description, such as Policy to make sure a database recovery model is set to Full.
Text to Display:
Type the text that will be displayed as a hyperlink when the policy has
been violated. For this example, type something such as Choosing a Recovery Model.
Type the address for the hyperlink. This could be a hyperlink to MSDN
explaining why you should use the policy or even to an internal web
site that lists the standards for the organization. For this example,
which will take you to an article on MSDN about choosing a recovery
model. Click the Test Link button to open a browser and validate the
Once you have entered all of
the information correctly, the errors are removed from the top of the
dialog box, and the status changes to Ready. The completed policy is
shown in Figure 5.
Figure 5. Completed Policy-Based Management Create New Policy dialog box
Click OK to finish creating the policy. You should now see the new policy under the Policies folder in Policy-Based Management.
Now that you have created a
policy, you are ready to evaluate that policy against the targets.
Right-click the policy you just created named Full Database Recovery
Model and select Evaluate from the context menu. The policy will be
evaluated against the defined targets, and the Evaluate Policies dialog
box will be displayed as shown in Figure 6. Notice that one of my targets did not comply with the policy and is displayed with a red X, indicating the failure.
Figure 6. Evaluate Policies dialog box
Click on the View
hyperlink in the Target Details column to display comprehensive
information about why the policy failed. After clicking the View
hyperlink, you will be able to see the Results Detailed View dialog
box, as shown in Figure 7.
The Results Detailed View will not only show you the expected value for
the condition that was used, you will also be able to see the actual
value to determine why the condition failed. You will also see the
description you entered for the policy, along with the hyperlink
information you defined when creating the policy. Close the Results
Detailed View dialog and return to the Evaluate Policies dialog box.
Figure 7. Results Detailed View dialog box
allows you to fix certain violations by checking boxes next to messages
indicating noncompliance in the Evaluate Policies dialog box and
selecting Apply. Not all policies can be automatically corrected. For
example, there is a predefined policy that you must manually address
stating that data and log files for a given database cannot be located
on the same drive. There are too many variables involved in moving data
files for SQL Server to automatically move them for you. You will know
that you cannot automatically correct a policy failure if there is no
check box that will allow you to select the policy in violation.
If you have selected policy
violations that you wish to automatically correct, then click the Apply
button. Your result will be a confirmation box, as shown in Figure 8, warning you that all noncompliant selected targets will be modified. Select Yes to accept and apply the changes.
Figure 8. Policy Evaluation Warning message box
To manage policy categories,
right-click on Policy Management in the SQL Server Management Studio
Object Explorer and select Manage Categories from the context menu.
This will display the Manage Policy Categories dialog box, shown in Figure 7-9.
Here you can add categories and specify whether the database
subscriptions to a category will be mandated. If a category is
mandated, it will be evaluated against all targets; if not you will
have to specifically designate the targets that will be evaluated. All
policies must be assigned to a category, and if no category is
specifically chosen, the policy will be assigned to the Default
category. One thing to note about the Default category is that you
cannot remove the Mandate Database Subscriptions check box. All
policies that remain in the Default category will be mandated against
Figure 9. Manage Policy Categories dialog box
That's all there is to
manually creating and executing a policy. Since Microsoft provides
predefined policies, you may never have to manually create a policy,
but it is the best way to become familiar with the available options.
One other thing that should be noted is that policies are stored in the
msdb database, so once you have created a new policy, you should make sure the msdb is backed up.
2. Exporting Current State As Policy
Many policies can be
exported based on the current state of a facet. Once you have
configured the facet, you can export the current state of the facet as
a policy. This section will walk you through exporting a Surface Area
Configuration policy using the current state.
If you are familiar with SQL
Server 2005, you may have noticed that the Surface Area Configuration
tool is not available when you install SQL Server 2008. The
configuration of the Database Engine features are now managed using the
Surface Area Configuration facet in Policy-Based Management. In SQL
Server Management Studio, right-click on the server instance you would
like to configure and select Facets from the context menu. This will
bring up the View Facets dialog box. Change the Facet option to Surface
Area Configuration, as shown in Figure 10.
Figure 10. The View Facets dialog box displays available facets for an object.
From the View Facets dialog,
you can configure the values and select OK to apply the new
configurations. You can also export the current configurations as a
policy to the local server or to a file that you can import and apply
to multiple servers across the organization. Click on the Export
Current State as Policy button on the View Facets dialog box to bring
up the Export as Policy dialog box, shown in Figure 11.
Figure 11. Export as Policy dialog box
The Export as Policy
dialog box allows you to name the policy and condition that will be
created. By default, the policy and condition name will be
FacetName_YYYYMMDD. For this demonstration, save the policy to the
local server and click OK. Click OK again to close the View Facets
You should now be able to see
the new policy and condition that was created in the Policy Management
node in SQL Server Management Studio. You can manage and evaluate the
policy using the same methods as if you created it manually. You can
use other objects to export current state as policy as well. For
example, if you right-click on a database and select Facets, it will
bring up the View Facets dialog box with a drop-down list of available
facets for the database object.
3. Importing a Predefined Policy
We mentioned earlier that
you could import predefined policies provided by Microsoft that
correspond with Best Practice Analyzer rules and default settings in
the Surface Area Configuration tool. Microsoft provides these policies
in the form of XML files as a part of the normal installation process.
The XML files are located in the Polices folder in the Tools directory
where you installed SQL Server. Importing a predefined policy is nice
because it not only creates the policy, but all the conditions required
as well. You also know that the policy is based on Microsoft best
practices and has been tested by someone other than yourself. You may
also want to create your own custom policies and deploy them as well.
This section will walk you through the steps required to import a
For this example, we will
be importing a policy that checks to make sure the data and log files
are not stored on the same drive. Right-click on the Policies folder
located under the Policy Management node in SQL Server Management
Studio, and then select Import Policy from the context menu. This will
open the Import dialog box, as shown in Figure 12.
Figure 12. Import dialog box
From the Files to Import
field, click the ellipsis to navigate to the C:\Program Files\Microsoft
SQL Server\100\Tools\Policies\DatabaseEngine\1033\ directory and select
the Data and Log File Location.xml file. (If you made custom
installation changes, your files may be in a different directory).
Select the Replace Duplicates with Items Imported check box to
overwrite any policies and conditions that have the same name of the
policy you are importing. You can choose to preserve the state of the
policy being imported, enable the policy on import, or, disable the
policy on import. For this example, select Preserve Policy State on
Import and select OK to import the policy.
You can now see the new policy
and the conditions that were created under the Policy Management node
in SQL Server Management Studio. The new policy is called Data and Log
File Location (see Figure 13).
The policy uses two conditions: one that checks to make sure the files
are on separate logical drives (called Data and Log Files on Separate
Drives), and one that places a server restriction on the policy (called
Enterprise or Standard Edition). As you can see in Figure 13,
you can use a condition as a check condition or a server restriction.
By placing a server restriction on the policy, it will only be
evaluated against servers that meet the condition defined for the
Figure 13. Data and Log File Location Policy General Tab
As you can see, importing
a predefined policy is an easy way to make sure your servers are using
Microsoft best practices or standards that you have implemented within
your organization. You can see the final policy in Figures 13 and 14. Figure 13 shows the general options that were automatically created when you imported the policy.
All the category, description, and hyperlink information is also prepopulated with the policy, as shown in Figure 14, making it easy to reference the documentation as to why this policy should be implemented.
Figure 14. Data and Log File Location Policy Description Tab