Policy Management Components - SQL Server 2008

PM contains a plethora of concepts and definitions. To help explain these, we’ll consider a standard naming convention scenario as an example. Suppose that the development management team has come out with guidelines describing the proper naming convention to be used in your organization, with the following specific requirements:

  • All table names within the Staging database must start with the characters tbl.
  • Anyone attempting to issue a Data Definition Language (DDL) statement to create or alter a table with a name that does not start with tbl will have that transaction aborted.
  • All tables must be created in the developer or test schema.

Keeping this scenario in mind, let’s start reviewing the key components that make up PM.

Managed Targets

A managed target defines which entity you want to manage. These entities that PM is able to manage in SQL Server 2008 all reside within a SQL Server instance. Examples of entities include the instance of SQL Server itself, a database, a table, or a schema. Note that these entities by themselves can form a natural hierarchy.

As an example, a database contains objects like tables and stored procedures. Thus, if you define a database as a target, you have the ability to apply a set of target filters to obtain a more specific target, like “only databases that are greater than 1GB in size.”

In the example, you would like a filtered database target that is described as “all tables within the Staging’ database.” You can create this filter using a PM condition, as described shortly.

Facets

Each managed target has various properties and behaviors associated with it. For example, a database has properties that describe things like collation, default filegroup, whether it’s a snapshot, and its last backup date. PM exposes these properties through a management facet. Facets not only model properties of a target, but they sometimes also model behaviors. For example, a behavior within the Database Maintenance facet is Data AndBackup OnSeparate Logical Volumes, which returns whether or not the database has its data and backup files on separate logical volumes.

Table lists all the available facets and examples of some of the facet properties. To see a complete list of properties of a facet, select to view the properties of a facet in SQL Server Management Studio.

Policy Management Components

Policy Management Components

Policy Management Components

Policy Management Components

Management Facets

In the standard naming convention example, you are concerned with the names of tables and the schemas where the table resides. You want a facet that exposes a name property and schema property for a table. Looking at the list of available facets in Table, you can see that the Multipart Name facet fits this description. This facet exposes these properties from any of these managed targets: stored procedures, synonyms, tables, user-defined functions, user-defined types, views, and XML Schema collections. Now that you have found your facet, it is time to create a condition.

Conditions

Facets by themselves state only the facts. For PM to be useful, we want to define certain conditions that are interesting to us as administrators, such as “when database file size is greater than 5GB” or “when schema is equal to developer.” For this, we have conditions, which are Boolean expressions defined on a specific management facet. They specify a set of valid states of the management facet.

To create a new condition, connect to SQL Server using SQL Server Management Studio and navigate down to the Policy Management node, under the Management node of the server instance. Right-click this node and choose to create a new condition. You will see the New Condition dialog box, as shown in Figure.

Creating a new PM condition

Creating a new PM condition

The Facet drop-down list shows all the facets listed in Table. In order to define a condition, you must specify a facet. For this example, selecte Multipart Name as the facet, and you will see the options @Name and @Schema in the grid’s Field column, as shown in Figure.

Field column showing properties of selected facet

Field column showing properties of selected facet

The Field column in the grid is populated with properties available for theselected facet. If you had selected the Backup Device facet instead, for example, the Field list would change to those properties that are exposed by the Backup Device facet. This dialog box is also smart enough to know which kinds of values apply to which fields. For example, if you selected @BackupDeviceType, the Value column would be populated with possible backup device types.

Value column showing properties of selected field

Value column showing properties of selected field

Returning to the sample table name condition, you want to specify the @Name field and change the operator to LIKE, since you want a partial string match, rather than an equality. Next, put the value tbl% in the Value cell of the grid.

At this point, you could click OK, and you would have a condition that checks only if the first three characters of the Name property are tbl. However, for the example, you need a more complex expression. You want to make sure the first three characters of the Name property are tbl and that the schema of the table is only within the developer schema or the test schema. To add the first schema restriction, click the AndOr cell on the next line in the grid and select AND. In the Field cell, select @Schema, and put 'developer' in the Value cell, as shown in Figure. Then click the AndOr cell below that, select OR, select @Schema in the Field cell, and enter 'test' in the Value field.

Building an expression using expression grid

Building an expression using expression grid

It is also possible to create groups of expressions. In the example, you also need to group both @Schema fields, since the logic would be incorrect if you did not. This grouping can be done by selecting both the @Schema rows, right-clicking, and selecting Group Clauses, as shown in Figure.

Grouping expression clauses

At this point, you have created a condition whose Boolean expression evaluates to true when the name of a table contains tbl as the first three characters and the schema for the table is either in the developer schema or the test schema. The final configuration for the condition is shown in Figure.

A configured condition

A configured condition

Policies

A policy binds a condition against its expected behavior. To create a policy, right-click the Policies node in Object Explorer and select New Policy. For this example, enter the name Table Name Policy for the policy and select Table Name Condition for the check. You will be presented with a list of objects to apply this policy against, as shown in Figure.

Creating a new policy

Creating a new policy

This list in the Against Targets box is based on the facet you selected for the condition. If you had selected a condition that was checking the database file size, for example, you would only be allowed to apply this policy against database targets.

Since this example is concerned only with tables within a database, check the Every Table check box. The Every words in the Against Target box are links that reveal a drop-down list that allow for further filtering. Click the Every link beneath the check box you just selected to see the list of available conditions for a database.

Options to filter targets within the policy

Options to filter targets within the policy

Since you want to apply this policy only for the Staging database, select New Condition. You will see the dialog box for creating a new condition, with the Database facet already selected. Finish the creation of this condition by specifying @Name = 'Staging'.

Condition for restricting the name of the database

Condition for restricting the name of the database

Note

At this point, your Against Targets box should look like Figure.

Specifying the targets for the new policy

Specifying the targets for the new policy

Specifying the targets for the new policy

In addition to creating your own conditions, you can use one of the predefined conditions that come as part of the SQL Server 2008 sample policies, which are described in the next section. The Execution Mode option beneath the Against Targets list controls when the policy is applied. The choices are as follows:

  • On Demand: When a policy is defined, it is applied only when the administrator proactively runs the policy against the server. This is the default selection for when to apply the policy.
  • On Change– Prevent: Changes are attempted. They will be aborted if the changes are out of compliance with the policy.
  • On Change – Log Only: Changes are attempted. A log entry is created if the policy is violated. So, the processing will proceed without error, but an event will be written into history tables in the msdb database. These tables can be viewed directly via the syspolicy_policy_execution_ history view or syspolicy_policy_execution_history_detail view, or by simply selecting View History from the Policies context menu.
  • On Schedule: The policy is checked on a scheduled basis. A log entry is created for policies that are violated. This option schedules a policy check via the SQL Server Agent. With this option, you can pick from an existing schedule or create a new schedule to run the policy check job.

For this example, choose On Change – Prevent to abort the transaction if it violates the policy.

By default, this policy can run on any server to which it is applied. If this policy should run only on certain types of servers (for example, only when the server instance is SQL Server 2000 or only when the instance is an Enterprise Edition), you can apply a predefined condition or create your own. Use the Server Restriction drop-down list to pick the desired condition for the server type, if any.

The Create New Policy dialog box also contains a page called Description, where you can provide some extra information that will be presented when the policy has been violated.

Adding a policy description

Adding a policy description

Adding a policy description

As a policy editor, you are free to put whatever text you deem suitable in the Description box. Obviously, one might not want to be as candid as in Figure if this policy is defined on a live server.

You can also add a hyperlink as part of the policy violation error. This hyperlink will be displayed along with the description text.

To complete your policy definition for this example, check the Enabled check box on the General page of the dialog box. Click OK, and your policy is created and actively in use.

You can now test the policy by issuing the following commands:

--Test Case: Create a table in the dbo schema with name violation
CREATE TABLE developer.Orders
(order_num INT NOT NULL,
purchase_time DATETIME2 NOT NULL,
order_amount DECIMAL(2) NOT NULL)

issuing this CREATE TABLE command yields the following:

The transaction ended in the trigger. The batch has been aborted.

If we corrected the table name and called it tbl_Orders instead of Orders and left the schema of developer unchanged, the command would execute successfully, since both criteria are satisfied. We would also get this same policy violation error if the name were acceptable but the schema were not developer or test.

Policies can be deployed across multiple servers by native import and export support. Basically what is provided is the ability to script out in the policy. The resulting script is an XML document, which can easily be imported on any destination server.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics