Implementing Policy-Based Management in Microsoft SQL Server

Managing Policies Throughout the Enterprise

As discussed previously, you create and manage policies in SQL Server Management Studio. Implementing Policy-Based Management is a multistep process that involves selecting a facet that contains the properties you want to configure, defining a condition that specifies the permitted states of the facet, and defining a policy that contains the condition and sets an evaluation mode. The evaluation mode you set determines whether SQL Server uses automated policy monitoring, reporting, and compliance enforcement.

Importing and Exporting Policies

SQL Server 2012 includes predefined policies for the Database Engine, Analysis Services, and Reporting Services. You can import these predefined policies with their preconfigured conditions if you want to use them with a particular instance of the Database Engine, Analysis Services, or Reporting Services. If you create your own policies, you can export those policies with their conditions to XML files and then import the XML files to another instance of SQL Server.

You can export a policy by completing the following steps:

1. In SQL Server Management Studio, access the Management folder on the instance of the Database Engine you want to work with.

2. In Object Explorer, under the Management node, expand Policy Management and Policies. Right-click a policy, and then click Export Policy. This displays the Export Policy dialog box, shown in Figure 3-2.

   

   Figure 3-2 The Export Policy dialog box.

3. Use the options in the Export Policy dialog box to select a save location, and then type the name of the XML file.

4. Click Save. By default, SQL Server preserves the current state of the policy. This state will be set when the policy is imported. Note that any condition associated with the policy is exported as well.

You can import a policy by completing the following steps:

1. In SQL Server Management Studio, access the Management folder on the instance of the Database Engine you want to work with.

2. In Object Explorer, under the Management node, expand Policy Management, right-click Policies, and then click Import Policy.

3. In the Import dialog box, shown in Figure 3-3, click the options (...) button, and then use the Select Policy dialog box to locate the XML file that contains the policy. Select a policy by clicking it, and then click Open. You can select multiple files using Shift+click or Ctrl+click.

   

   Figure 3-3 The Import dialog box.

4. Remember that any condition associated with the policy is imported as well. If identically named policies (and conditions) already exist, the import process will fail with an error stating that the policies exist. To force SQL Server to overwrite existing policies, you must select the Replace Duplicates With Items Imported check box.

5. By default, SQL Server preserves the policy state on import. If a policy was enabled when it was exported, it will be enabled. If a policy was disabled when it was exported, it will be disabled. You can modify this behavior by explicitly setting the state. To enable the policies you are importing, select Enable All Policies On Import in the Policy State list. To disable the policies you are importing, select Disable All Policies On Import.

6. Click OK to begin the import process.

Configuring and Managing Policy Facets

Facets define management areas within the policy-based framework. Each management area has a set of related properties that you can configure by using a particular facet. You can view or modify the current state of any facet properties via the related object.

To view an object’s current state and modify this state, follow these steps:

1. In Object Explorer, right-click a server instance, database, or database object, and then click Facets.

2. In the View Facets dialog box, shown in Figure 3-4, use the Facet list to select a facet related to the object. You then see a list of properties that shows the names and values of the facets.

   

   Figure 3-4 Modify property values as necessary.

3. Click in the box next to the property to select a property value. If a property appears dimmed, you cannot modify the property value.

4. Click OK.

Exporting an object’s current state as policy allows you to use the current configuration of a server instance, database, or other database object to define policies that you want to implement throughout the enterprise. After you export an object’s current state as policy, you can save the policy to the Policy Management\Policies node on the local server or to a policy file that you can import on another server.

Exporting an object’s current state as policy creates a condition and a policy. To view an object’s current state and export this state as policy, follow these steps:

1. In Object Explorer, right-click a server instance, database, or database object, and then click Facets.

2. In the View Facets dialog box, use the Facet list to select a facet related to the object. You then see a list of properties that shows the names and values of the facets.

3. Click Export Current State As Policy to display the Export As Policy dialog box, shown in Figure 3-5.

   

   Figure 3-5 Export the property settings as a policy.

4. Type a name for the policy, and then type a name for the condition.

5. To save the policy to the Policy Management\Policies node on the local server, select To Local Server, and then click OK to close the Export As Policy dialog box. The related policy and condition are created under the appropriate nodes within Policy Management.

6. To save the policy to a file that you can import on another server, select To File. Click the options (...) button. Use the Export Policy dialog box to select a save location and name for the policy file, and then click Save. Click OK to close the Export As Policy dialog box. Later, you can import the policy using the technique discussed in the Importing and Exporting Policies section earlier in this chapter.

Creating and Managing Policy Conditions

Facets represent management areas within SQL Server. Most facets have multiple properties that you can manage or evaluate using conditions. Conditions define the permitted states for properties based on expressed values. Although you can use a single condition in multiple policies, you define conditions for each facet individually.

Defining Conditions Using Properties and Standard Expressions

When you are defining conditions, you join property evaluation expressions by using the And or Or clause to form a logical statement. For example, with the database facet, you might want to establish the condition shown in Figure 3-6. In this example, the evaluation expression specifies the following:

• AutoClose must be True,

• And AutoShrink must be False,

• And PageVerify must be set to either TornPageDetection or Checksum,

• And AutoUpdateStatisticsEnabled must be True,

• And Trustworthy must be True.

Although the allowed values depend on the property you are configuring, values generally can be numeric, a string, or a fixed list. With properties that are on (true) or off (false), you can set the operator to equals (=) or not equals (!=). When you set a property to equals, the property must equal the specified value. When you set a property to not equals, the property cannot equal the specified value, but it can be set to other permitted values.

With multivalued properties, other operators you can use are as follows:

• >. Greater than; the property must be greater than the value specified.

• >=. Greater than or equal to; the property must be greater than or equal to the value specified.

• <. Less than; the property must be less than the value specified.

• <=. Less than or equal to; the property must be less than or equal to the value specified.

• Like. Pattern match, as with the LIKE clause in T-SQL; the property must match a specified pattern. Enclose the Like value in single quotation marks, such as ‘%computer%’ or ‘[D-Z] arwin’.

• Not Like. Pattern match, as with the NOT LIKE clause in T-SQL; the property must not match a specified pattern. Enclose the Not Like value in single quotation marks.

• In. Query or list match, as with the IN clause for T-SQL; the property must match a value in the specified query or list. Enclose the In clause in parentheses, enclose individual values in single quotation marks, and separate values with commas, such as (‘Hawaii’, ‘Idaho’, ‘Nebraska’).

• Not In. Query or list match, as with the NOT IN clause for T-SQL; the property must not match a value in the specified query or list.

Creating and Modifying Conditions

You can create a condition by completing the following steps:

1. In SQL Server Management Studio, access the Management folder on the instance of the Database Engine you want to work with.

2. In Object Explorer, under the Management node, expand Policy Management, expand Facets, right-click the facet that contains the properties that you want, and then click New Condition.

3. On the General page of the Create New Condition dialog box, type the name of the new condition, such as Standard Database Settings, in the Name box.

4. Confirm that the correct facet is shown in the Facet box, or select a different facet.

5. In the Expression area, construct condition expressions by selecting a facet property in the Field box together with its associated operator and value. When you add multiple expressions, the expressions can be joined by using And or Or.

6. To create complex expressions, press the Shift or Ctrl key, and then click two or more clauses to select a range. Right-click the selected area, and then click Group Clauses. Grouping clauses is like putting parentheses around an expression in a mathematical expression, which forces the clauses to operate as a single unit that is separate from the rest of the condition.

7. Optionally, on the Description page, type a description for the new condition.

8. Click OK to create the condition.

After creating a condition, you can view or modify its settings by completing these steps:

1. In SQL Server Management Studio, access the Management folder on the instance of the Database Engine you want to work with.

2. In Object Explorer, under the Management node, expand Policy Management, expand Conditions, right-click the condition that you want to view or modify, and then select Properties.

3. View the condition settings on the General page. Make changes as necessary, and then click OK.

Although you cannot delete a condition referenced in a policy, you can delete unreferenced conditions. You delete a condition by right-clicking it and then selecting Delete. When prompted to confirm, click OK.

Defining Complex Expressions

Although standard expressions are useful for evaluating conditions, complex expressions give you many more options, including the capability to replace object and schema names at run time. Complex expressions do this by extending the valid syntax for expressions to include a set of predefined functions that can be evaluated on either side of the condition operator. Thus, the standard expression syntax of

{property|constant} [operator] {property|constant]

becomes

{property|constant|function} [operator] {property|constant|function}

Available functions allow you to perform many complex tasks. You can add values, count values, or compute averages, and then evaluate the results. You can convert values to strings or concatenate strings and then evaluate the results. You can execute T-SQL queries or WMI Query Language (WQL) scripts and then evaluate the results.

To create a condition that uses complex expressions, complete the following steps:

1. Create a new condition or open an existing condition for editing using the techniques discussed previously.

2. While working with the condition, on the General page, click the options (...) button in the Expression area.

3. In the Advanced Edit dialog box, define your function in the Cell Value box and then click OK.

Creating and Managing Policies

You use policies to check and optionally enforce conditions. When you create a policy, you can use a condition that you created earlier, or you can create a new condition when you are creating the policy. Although you can use a particular condition in many policies, a policy can contain only a single condition.

When you create a policy, the policy normally is associated with the current instance of the Database Engine. If the current instance is a central management server, the policy can be applied to all subordinate servers. You also can directly create a policy by choosing New from the File menu and then saving the policy to a file. This enables you to create policies when you are not connected to the instance of the Database Engine that you want to work with.

You can create a policy and associate it with a particular instance of the Database Engine by completing the following steps:

1. In SQL Server Management Studio, access the Management folder on the instance of the Database Engine you want to work with.

2. In Object Explorer, under the Management node, expand Policy Management, right-click Policies, and then click New Policy. This displays the Create New Policy dialog box, shown in Figure 3-7.

   

   Figure 3-7 Create the policy and specify the condition that applies.

3. In the Name box, type the name of the new policy, such as Standard Database Settings Policy.

4. Use the Check Condition list to select one of the existing conditions, or select New Condition. To edit a condition, select the condition, and then click the options (...) button. Make changes as necessary to the condition settings, and then click OK.

5. In the Against Targets box, select one or more target types for this policy. Some conditions and facets can be applied only to certain types of targets. The available target sets appear in the associated box. If no targets appear in this box, the check condition is scoped at the server level. To select a filtering condition for some types of targets, click the Every entry and then select an existing filter condition, or define a condition by selecting New Condition and then specifying the settings for the condition.

6. Use the Evaluation Mode list to select how this policy will behave. Different conditions can have different valid evaluation modes. Available modes can include On Demand, On Change: Prevent, On Change: Log Only, and On Schedule. If you specify a mode other than On Demand, you can enable the policy by selecting the Enabled check box. If you specify On Schedule as the mode, click Pick to select an existing run schedule, or click New to create a new schedule. For more information on creating schedules, see the Configuring Job Schedules section in Chapter 10.

7. To limit the policy to a subset of the target types, select a limiting condition in the Server Restriction list, or select New Condition to create a new condition.

8. By default, policies are assigned to the Default category. On the Description page, in the Category box, you can select a different default policy category. (See Figure 3-8.) Otherwise, to create a new category, click New, type a category name, and then click OK.

9. On the Description page, you can type an optional description of the policy. Click OK to create the policy.

After creating a policy, you can view or modify its settings by completing these steps:

1. In SQL Server Management Studio, access the Management folder on the instance of the Database Engine you want to work with.

2. In Object Explorer, under the Management node, expand Policy Management, expand Policies, right-click the policy that you want to view or modify, and then select Properties.

3. View the policy settings on the General and Description pages. Make changes as necessary. Click OK.

You can manage policies you’ve created by using the following techniques:

• Delete a policy by right-clicking it and selecting Delete. When prompted to confirm, click OK.

• Disable a policy by right-clicking it and selecting Disable.

• Enable a policy by right-clicking it and selecting Enable.

Managing Policy Categories and Mandating Policies

SQL Server 2012 uses policy categories to help you organize your policies. In a large organization, grouping policies into categories makes policy management easier. You can assign a policy to a category in several ways. By default, any policy you create belongs to the Default category. When you create a policy, you can assign the policy to an available category or to a new category as well. To move a policy to a different policy category, complete the following steps:

1. Right-click the policy that you want to view or modify, and then select Properties.

2. On the Description page, in the Category box, select a different default policy category. Otherwise, to create a new category, click New, type a category name, and then click OK.

3. Click OK to apply the changes.

In addition to helping you organize policies, policy categories help with policy application. Policies within categories are either mandated or not mandated. If a policy is mandated, it means that all databases on the instance of SQL Server must enforce the policy. If a policy is not mandated, it means that you must apply the policy manually as appropriate.

By default, any policy assigned to the Default category is a mandated policy. You can control whether a policy category and its related policies are mandated or not mandated by completing the following steps:

1. In SQL Server Management Studio, access the Management folder on the instance of the Database Engine you want to work with.

2. In Object Explorer, under the Management node, right-click the Policy Management node, and then select Manage Categories.

3. The available categories are listed by name. To create a new policy category, simply click in an empty text box in the Name column and type the category name.

4. In the Manage Policy Categories dialog box, shown in Figure 3-9, select or clear the Mandate Database Subscriptions check box for each category, as appropriate. Click OK.

You can determine the policies that are applied to a database or other object by completing the following steps:

1. In Object Explorer, right-click a database or database object, point to Policies, and then click Categories.

2. In the Categories dialog box, expand the category entries to determine which policies are being applied. As shown in Figure 3-10, the following information is available:

   • Name. Shows the name of the policy category.

   • Subscribed. Indicates whether the selected object has subscribed to the policy category. If the related check box appears dimmed, the policy category is mandated and applies to all databases on the server.

   • Policy. Shows the policies in the policy category, provided that you’ve expanded the related category node.

   • Enabled. Indicates whether the policy is enabled or disabled.

   • Evaluation Mode. Shows the evaluation mode of the policy.

   • History. Click the View History link to open the Log File viewer and display the creation and change history for the policy.

Evaluating Policies

By using automatically evaluated modes, you can check policy compliance when changes are made or on a regularly scheduled basis. You also can evaluate a policy manually to determine whether a server instance, database, or other object complies with the policy. If you later apply or enforce the policy, you can configure the selected database instance or database object to comply with the policy.

Because the connections to subordinate servers are executed using Windows authentication in the context of the currently logged-on user, the effective permissions might vary. If a connection cannot be established to one or more servers, those servers are ignored, and evaluation against the other servers continues independently.

You can determine whether a particular object complies with a policy by completing the following steps:

1. In Object Explorer, right-click a server instance, database, or database object, point to Policies, and then click Evaluate.

2. The Evaluate Policies dialog box shows only the policies that are appropriate for the object. (See Figure 3-11.) In the Evaluate Policies dialog box, select one or more policies, and then click Evaluate to run the policy in evaluation mode. (The evaluation mode is defined as part of the policy and cannot be changed in the Evaluate Policies dialog box.)

   

   Figure 3-11 Evaluate an object against policies to determine compliance.

3. If there are compliance issues, you’ll see a red warning icon. You can click the View link that appears in the Details column under Target Details to view the detailed compliance results. As shown in Figure 3-12, the Result column shows whether each property in the joined evaluation expression is in compliance or out of compliance. Expected and actual values are also shown. Note that help text is provided if it was previously defined. Click Close when you finish reviewing the detailed results.

4. In the Evaluate Policies dialog box, clicking Evaluate generates a compliance report for the target set but does not reconfigure SQL Server or enforce compliance. For targets that do not comply with the selected policies and have properties that can be reconfigured by Policy-Based Management, you can enforce policy compliance by selecting the policy or policies to apply on the Evaluation Results page and then clicking Apply.

   

   Figure 3-12 Review compliance issues.

5. The first time you try to apply a policy, you’ll see a Policy Evaluation Warning dialog box prompting you to confirm the action. Click Yes to proceed. If you don’t want to see the warning in the future, select the Do Not Show This Message Again check box before clicking Yes.

6. After you apply a policy, you can review the detailed results by clicking the View link that appears in the Details column under Target Details. If the properties can be reconfigured using Policy-Based Management, the properties will be changed. Click Close when you finish reviewing the detailed results.

7. Optionally, you can export the results to a policy results file for later review. Click Export. Use the Export Results dialog box to select a save location for the results file, type a file name, and then click Save.

You can determine whether the targets of a policy are in compliance by completing the following steps:

1. In Object Explorer, expand Management, Policy Management, and Policies. Right-click a policy, and then click Evaluate.

2. Follow steps 2 through 7 in the previous procedure.

You can determine whether the targets of a policy are in compliance with a schedule by completing the following steps:

1. In Object Explorer, expand Management, Policy Management, and Policies. Right-click a policy, and then click Properties.

2. On the General page, specify On Schedule as the evaluation mode.

3. Click Pick to select an existing run schedule, or click New to create a schedule. Click OK twice to save your changes.

To view the history of compliance checks, right-click the policy and then select View History. In Log File Viewer, expand the available run dates to show additional details. Review the related details in the detailed view by clicking the link provided in the Details column.

Each property in the joined evaluation expression is listed according to whether it is in or out of compliance. If there are compliance issues, you’ll see a red warning icon to show properties not in compliance or a green OK icon to show properties in compliance. Expected and actual values are also shown. Note that help text is provided if it was previously defined. Click Close when you finish reviewing the detailed results.

Troubleshooting Policies

In the msdb database, you’ll find the following views for displaying policy information. These views are owned by the dbo schema:

• syspolicy_conditions

• syspolicy_policies

• syspolicy_policy_execution_history

• syspolicy_policy_execution_history_details

• syspolicy_policy_category_subscriptions

• syspolicy_policy_categories

• syspolicy_system_health_state

• syspolicy_target_sets

SQL Server records compliance issues in the Windows event logs. For scheduled policies, compliance issues are recorded in the SQL Server Agent log as well. To view the history information recorded in the SQL Server Agent logs, right-click the policy and then select View History. Review the related details in the detailed view by clicking the link provided in the Details column. If policies are not enabled or do not affect a target, the failure is not considered an error and is not logged. For more information, see the Evaluating Policies section earlier in this chapter.

Remember that compliance checks for scheduled policies occur only during scheduled run times and that on-demand policies run only when you manually execute them. If you have problems with policies set to On Change: Log or On Change: Prevent, be sure that the policies are enabled and that the target you want is not excluded by a filter. You also should ensure the target subscribes to the policy group that contains the policy. As discussed in the Managing Policy Categories and Mandating Policies section earlier in this chapter, you can determine the policies that are applied to a database or other object by right-clicking a database or database object, pointing to Policies, and then clicking Categories.

You can determine whether a policy was evaluated by right-clicking the policy and then selecting View History. The policy execution history in the msdb.dbo.syspolicy_policy_execution_history view also provides information about whether a policy was evaluated. You can also determine whether the policy executed for the specific target by checking the policy execution history for the specific target in the msdb.dbo.syspolicy_policy_execution_history_details view. You can determine the execution time for policies by querying the start_date and end_date columns in the msdb.dbo.syspolicy_policy_execution_history view.

For policies that use the On Change: Prevent mode, Service Broker handles the rollback of changes. You should ensure that Service Broker is running and configured properly. If it is, you can check the Service Broker Queue to be sure that it is monitoring for the correct events by using either of the following queries:

T-SQL

SELECT * FROM sys.server_event_notifications
WHERE name = N'syspolicy_event_notification';
GO

PowerShell

Set-Location SQLSERVER:\SQL\DbServer18\OrderSystem
Invoke-Sqlcmd -Query "SELECT * FROM sys.server_event_notifications
WHERE name = N'syspolicy_event_notification';"

Keep in mind that if the nested triggers server configuration option is disabled, On Change: Prevent mode will not work correctly. Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that do not comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL triggers or disabling nested triggers causes this evaluation mode to fail.

Because On Schedule policies rely on SQL Server Agent jobs, you should always check to be sure that SQL Server Agent is running and configured properly. You also should check to ensure that the related SQL Server Agent jobs are enabled and configured properly. Working with SQL Server Agent jobs is discussed in Chapter 10.

If you suspect there is a problem with policy-related jobs, you may be able to use sp_syspolicy_repair_policy_automation to repair the jobs. This stored procedure will attempt to repair triggers and jobs that are associated with On Schedule or On Change policies. Since policies are stored in the msdb database, you must run this stored procedure in the context of the msdb database, as shown in the following example:

EXEC msdb.dbo.sp_syspolicy_repair_policy_automation;
GO