Designing Policy Based Management in Microsoft SQL Server 2008

Import and Export Policies

Policies and conditions can be exported to files as well as imported from files. SQL Server ships with 53 policies that are located in the Microsoft SQL Server\100\Tools\Policies folder. There are 50 policies for the database engine, 2 policies for Reporting Services, and 1 policy for Analysis Services. The CodePlex site (http://www.codeplex.com) has additional policies that you can download and import.

You can import policies within the Registered Servers pane or the Object Explorer. Within Object Explorer, you can right-click the Policies node underneath Policy Management and select Import Policy. Within Registered Servers, you can right-click the Central Management Server or any folder or instance underneath the Central Management Server and select Import Policies. If you import policies from the Central Management Server, the policies are imported to every instance defined underneath the Central Management Server, but not to the Central Management Server itself. Likewise, right-clicking a folder imports the policies to all instances within the folder hierarchy. To import policies to the Central Management Server, you must connect to the instance within Object Explorer and import from the Policies node.

Quick Check

1. What are the five objects that are used within Policy Based Management?

2. What are the allowed execution modes for a policy?

3. Which object has a property that allows you to mandate checking for all databases on an instance?

4. How many facets can be checked within a single condition?

5. How many conditions can be checked within a single policy?

Quick Check Answers

1. The objects that are used with Policy Based Management are facets, conditions, policies, policy targets, and policy categories.

2. The policy execution modes are On demand, On schedule, On change, Log only, and On change, prevent.

3. Policy categories allow you to mandate checking of all databases within an instance.

4. A condition can be defined on only one facet.

5. A policy can check only a single condition.

PRACTICE: Defining Policies and Checking for Compliance

In these practices, you define and check several policies for your environment.

PRACTICE 1: Create a Condition

In this practice, you create a condition for the following:

• Check that a database does not have the auto shrink or auto close properties set.

• Check that CLR, OLE Automation, Ad Hoc Remote Queries, and SQL Mail are all disabled.

• Check that a database is not in the Simple recovery model.

• Check that all tables have a primary key.

1. In Object Explorer, expand the Policy Management node within the Management node.

2. Right-click the Conditions node and select New Condition.

3. Configure the condition as shown here. Click OK when you are done.

   

4. Right-click the Conditions node again, select New Condition, and configure the condition as shown here. Click OK.

   

5. Right-click the Conditions node, select New Condition, and configure this third condition as shown here. Click OK when you are finished.

   

6. Right-click the Conditions node and select New Condition. Select the Table facet, click the ellipsis button next to the Field column to display the Advanced Edit dialog box, enter the following code in the Cell Value text box, and click OK:

   IsNull(ExecuteSql('Numeric', 'SELECT 1 FROM sys.tables a INNER JOIN sys.indexes b
      ON a.object_id = b.object_id WHERE b.is_primary_key = 1
       AND a.name = @@ObjectName AND a.schema_id = SCHEMA_ID(@@SchemaName)'), 0)

7. Configure the Name, Operator, and Value as shown here, and then click OK.

   

PRACTICE 2: Create a Condition for a Target Set

In this practice, you create a condition to target all SQL Server 2005 and later instances, along with a condition to target all user databases that are online.

1. Right-click the Conditions node, select New Condition, and configure the condition as shown here. Click OK.

   

2. Right-click the Conditions node, select New Condition, and configure the condition as shown here. Click OK when you are done.

   

PRACTICE 3: Create a Policy

In this practice, you create policies that use the conditions you just created to do the following:

• Check that a database does not have the auto shrink or auto close properties set.

• Check that CLR, OLE Automation, Ad Hoc Remote Queries, and SQL Mail are all disabled.

• Check that a database is not in the Simple recovery model.

• Check that all tables have a primary key.

1. Right-click the Policies node, select New Policy, and configure the policy as shown here. Click OK.

   

2. Right-click the Policies node, select New Policy, and configure this second policy as shown here. Click OK.

   

3. Right-click the Policies node, select New Policy, and configure the policy as shown here. Click OK.

   

4. Right-click the Policies node, select New Policy, and configure the last policy as shown here. Click OK.

   

PRACTICE 4: Create a Policy Category

In this practice, you create two policy categories for the policies that you created.

1. Right-click Policy Management, select Manage Categories, and create the categories as shown here. Click OK.

   

2. In SSMS, in the console tree, expand the Policies folder. Right-click the Check For Auto Shrink And Auto Close Policy, select Properties, click the Description tab, and change the category to Database Best Practices. Click OK.

3. Right-click the Check For Simple Recovery Model Policy, select Properties, select the Description tab, and change the category to Database Best Practices. Click OK.

4. Right-click the Check For Surface Area Configuration Policy, select Properties, click the Description tab, and change the category to Instance Surface Area Best Practices. Click OK.

5. Right-click the Check Tables For Primary Key Policy, select Properties, select the Description tab, and change the category to Database Best Practices. Click OK.

PRACTICE 5: Import Policies

In this practice, you import the policies that ship with SQL Server.

1. Right-click the Policies node underneath Policy Management and select Import Policy.

2. Click the ellipsis button next to the Files To Import text box, navigate to the Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033 folder, select all the files in the folder, as shown here, and click Open.

   

3. Select the Replace Duplicates With Items Imported check box, select Preserve Policy State On Import, and click OK.

4. Take the time to browse the policies and conditions that were created during the import.

Lesson Summary

• You can build policies to enforce conditions across any version of SQL Server.

• Policies can enforce a single condition and each condition can be based on a single facet.

• Policy categories allow you to group policies together for compliance checking.

• A policy category can be set with the Mandate property, which requires the policy to be checked against all databases within an instance.

Lesson Review

The following question is intended to reinforce key information presented in this lesson. The question is also available on the companion CD if you prefer to review it in electronic form.

1. You have defined several policies that you want applied to all databases within an instance. How do you ensure that a database owner is not allowed to avoid the policy check with the least amount of administrative effort?

   1. Create a condition that checks all databases.

   2. Add the policy to a user-defined policy category and set the Mandate property.

   3. Add the policy to the default policy category.

   4. Check the policies manually against the instance.