SQL Server 2008 R2 : Implementing Policy-Based Management (part 1) - Creating a Condition Based on a Facet, Creating a Policy

There are essentially six steps to implementing and administering Policy-Based Management:

• Creating a condition based on a facet
  
  
• Creating a policy based on that condition
  
  
• Creating a category
  
  
• Creating a Central Management Server
  
  
• Subscribing to a category
  
  
• Exporting or importing a policy
  
  

Let’s look at each of these steps in turn. The upcoming sections explain each step in its entirety.

Creating a Condition Based on a Facet

When you are creating conditions, the general principle includes three elements: selecting a property, an operator, and then a value. The following example walks through the steps to create a condition based on a facet which will enforce a naming standard on a table:

1.	To create a condition, connect to a SQL Server 2008 instance on which you want to create a policy.
2.	Launch SQL Server Management Studio (SSMS). In Object Explorer, expand the Management folder, expand the Policy Management folder, and then expand the Facets folder.
3.	Within the Facets folder, browse to the desired facet on which you want to create the policy (in this case, the Table facet).
4.	To invoke the Create New Condition window, right-click the facet and select New Condition.
5.	In the Create New Condition dialog, type a name for the condition (for example, Table Name Convention) and ensure that the facet selected is correct.
6.	In the Expression section, perform the following tasks: a. Select the property on which you want to create your condition. For this example, use the @Name property. b. In the Operator drop-down box, select the NOT LIKE operator. c. In the value text box, enter 'tbl%'.
7.	Repeat step 6 for any additional expressions. For this example, the following expressions were entered, as displayed in Figure 1. AndOr Field Operator Value   @Name NOT LIKE 'tbl%' AND Len(@Name) <= 50 AND @Name NOT LIKE '%s' Figure 1. Creating a condition based on a facet.
8.	Click OK to finalize the creation of the condition. You may have to click on the Field text box again for the OK button to be enabled.

Creating a Policy

After creating the condition or conditions, you need to create the policy. The policy is a standard that can be enforced on one or more SQL Server instances, systems, server objects, databases, or database objects. Follow these steps to create a policy with SQL Server Management Studio:

1.	In Object Explorer, expand the Management folder, expand the Policy Management folder, and then click on Policies.
2.	Right-click on the Policies folder and select New Policy.
3.	On the General tab of the Create New Policy dialog, enter a name for the new policy, such as Check Table Naming Conventions.
4.	In the Check Condition drop-down box, select a condition, such as the one created in the previous example, or select New to generate a new condition from scratch.
5.	The Against Targets section indicates which objects the policy should be evaluated against. For example, you could create a new condition that applies to a specific database, all databases, a specific table, all tables, or to databases created after a specific date. In the Action Targets section, indicate which targets this condition should apply to.
6.	Specify the Evaluation Mode by selecting one of the options in the drop-down menu. The options include On Demand, On Schedule, On Change Log Only, and On Change Prevent. Note If On Schedule is selected for the Evaluation Mode, specify a schedule from the predefined list or enter a new schedule.
7.	The final drop-down box is Server Restriction. You can restrict which servers you do not want the policy to be evaluated against or enforced on by creating a server condition. Create a server restriction or leave the default setting None. An example of the policy settings for checking table name conventions is displayed in Figure 2. Figure 2. The Create New Policy dialog.
8.	Before you close the Create New Policy dialog, ensure that the policy is enabled (the Enabled check box is selected) and then click on the Description page. The Description page allows you to categorize your policy, but it also allows you to display a custom text message when a policy is violated and a hyperlink where the DBA/developer can go for more information about the policy.
9.	Click OK to finalize the creation of the new policy.

An Alternative to Creating Policies

As you can imagine, for complex policies you might need to create many conditions. In some cases it may be easier to create a table, database, or server configured to conform to the policy you want to create and then right-click on the specific object and select Facets. This brings up the View Facets page. Click on the Export Current State as Policy button. This exports a policy and a single condition to which the existing object will conform.

Figure 3 illustrates the dialog that prompts you for a name for your policy and condition as well as where you want to store the policy. You can store it in the file system and then import it to a Central Management Server or other servers where you want the policy to be evaluated, or you can import it directly to a server. Note that this policy will contain conditions specific to the object you use as a template; for example, if you use the AdventureWorks2008 database, the policy will test for the condition where the database name is equal to AdventureWorks2008. For this feature to be useful, you likely need to edit the conditions to ensure that they are generic and evaluate exceptions correctly.

Figure 3. Exporting a policy based on an existing object.