The Declarative Management Framework is a new policy based system to manage SQL Server 2008 instances. Using this framework, DBAs can create policies to govern the properties and behavior of various entities of an SQL Server 2008 instance. This new feature gives DBAs control of their database servers, from an entirely new perspective. For instance; if their database servers require a restricted setup where database mail is not allowed, they could easily create a policy to ensure that these restrictions are followed. Or take an instance where it is required that tables of a particular database always be named with a certain prefix. Within a couple of minutes, the DBA could create a policy and enforce it on the database. Policies can also be exported to other servers to be used in a multi-server environment. The Declarative Management Framework is limited only to the Database Engine instance, and does not extend to other components such as Analysis Services etc.

This article is the result of some research that I had done on the Declarative Management Framework. It includes explanation of concepts, terminology and some how-to's on the subject. The research was conducted on SQL Server "Katmai" July CTP, therefore the final product would tend to have changes and/or improvements.

Getting into it

The Declarative Management Framework (DMF or DMgF as referred to by some, to avoid confusion with Dynamic Management Functions) consists of three components:

• Policy Management
• Explicit Administration
• Automated Administration

Policy Management

Policy management is when the policy administrator defines the policies. Any number of policies can be created by an administrator.

Explicit Administration

Explicit administration is when an administrator picks one or more targets and explicitly checks or enforces a policy upon it. A target is an entity of a SQL Server 2008 instance such as a database or table. More on targets later. Explicit administration is performed in an ad hoc basis, and not routinely.

Automated Administration

Automated administration is when an administrator configures a policy to automatically check or enforce itself upon a target. In this mode of administration, the automatic checking can be set to either run when the state of the target changes or run on a schedule.

Terminology and Concepts

Before directly moving into creating our first policy, let us first look at some terminology and concepts

Declarative Management Framework managed target A managed target is any entity that is managed by the DMF, such as a database engine instance, a database or a table. A target set is a set of targets defined by applying a filter, such as tables that belong to a specific schema. All targets form a target hierarchy with the server instance at the top of the hierarchy. Declarative Management Framework management facet A management facet is a set of logical properties that is modeled upon the characteristics of a specific management target. For example, the Table Facet is a management facet that is modeled upon a table (which can be a candidate for a management target). Some management facets can be modeled upon properties of several types of management targets. For example, the Multipart Name Facet is modeled upon a couple of common properties belonging to several types of management target types including table, stored procedure and view. The properties of a management facet can only be modified by the author of the facet. Declarative Management Framework condition A condition is a Boolean expression which specifies a set of configured properties of a management facet in the context of a managed target. For example, we could create a condition that specifies that the Name property of the Table Facet should have a value that begins with the value "TBL". We could later, by means of some actions apply this condition to a table. Declarative Management Framework policy A policy is a combination of a condition and its expected behavior, such as its execution mode and targets. A policy can contain only one condition. A policy can be enabled or disabled. Declarative Management Framework policy group A policy group is used to assist us in managing policies. Since they are user-defined, users can create their own policy groups and assign policies to them. A policy can belong to only one policy group. If the policies in a certain policy group are to govern a database, the policy group should be subscribed to. The default policy group is implicitly subscribed to by all databases.

Declarative Management Framework execution modes

To build upon what was explained in the Getting into it section; there are two types of execution modes:

• Ad hoc
  This execution mode is used during Explicit Administration. There are two options under this mode:
  • Check
    - Here, the policy administrator would explicitly only check his/her policy against the target. In this case no rules are enforced on the target. The policy administrator would only get a message indicating the status of the policy check (i.e. whether it succeeded or not).
  • Configure
    - Under this option the policy administrator would explicitly enforce his/her policy against the target. When that happens the target property would be changed to reflect the policy enforcement. Sometimes policies would not be able to change the target property when it is enforced, in such cases a critical health warning icon would emerge next to the target indicating that the policy enforcement had failed.
• Automated - This execution mode is used during Automated Administration. This mode has three options:
  • Enforce
    - This option would straightaway get the policy working, provided that it has been subscribed to. It would enforce those rules wherever the properties of the target can be changed, if not it would just indicate with a critical health warning icon next to the target. This option uses DDL triggers to enforce policies.
• Check on Changes
  - This option waits until a change occurs on the target which would trigger the policy to start regulating.
• Check on schedule
  - This option regulates the targets with the policy at a scheduled time. Check on schedule uses the SQL Server Agent service for scheduling.

Effective Policy

An effective policy of a target is a policy that can govern the target. In order for a policy to be effective, it should conform to the following conditions:

• The policy must be enabled.
• The target must belong to the target set defined for the policy.
• The target or one of its ancestors in the hierarchy should subscribe to the policy group of the policy.

Giving it a try

Now that you have grasped the theory and terms used in the DMF, it is now time to get our hands dirty.

First let us see how a policy can be applied in the most simplest of steps:

1. Pick a target/target set such as the tables of a database.
2. Choose the appropriate management facet that is associated with the target. For instance the Table facet is associated with tables.
3. Create a condition using the facet.
4. Add the condition to a policy and set the target and execution mode for the policy.
5. Optionally include the policy in a user-defined policy group.
6. Put the policy to work; by either using Explicit Administration or Automated Administration.

Now that we know how simple it is to implement policies we shall go a bit further by trying it out on a couple of scenarios.

Scenario 1: Servers with Windows Authentication Only

This scenario requires that all SQL Servers in a particular environment need to have only Windows Authentication enabled as a security configuration. Let us create a policy to oversee this requirement. We shall implement Explicit Administration (or ad hoc execution) to apply the policy that we create.

1. Connect to the SQL Server "Katmai" instance through SSMS, and expand the server in the Object Explorer and find the Management folder.
2. Expand Management to find the Policy Management folder. Within this you would find three more folders namely, Policies, Conditions and Facets.
3. The first task for us would be to identify the target. Since authentication mode is a property of the server, the target obviously would be Server. This leads us to our next task of selecting the appropriate management facet. That would be Server Facet.
4. Next, we need to create the condition.
   1. To do this right click on the Server Facet and select New Condition.... this will open up the Create New Condition dialog with the Server Facet already selected.
   2. Enter a name for the condition in the Name field (In our case let us call it, Authentication restriction), and move on to the Expression grid. The Expression grid allows for multiple Boolean expressions to be entered; all joined by "And"s and "Or"s.
   3. Since we need only set the authentication mode, we would only be having one expression. Select LoginMode in the Field field. Select "=" in the Operator field and select Integrated in the Value field. Integrated in this case refers to Integrated Security (a.k.a. Windows Authentication). Click on OK.
   4. The condition is created! Alternatively you can create a condition by right clicking on the Conditions folder under Policy Management.
      
5. Now towards creating a policy.
   1. Right click on the Policies folder under Policy Management and select New Policy.... This would open up the Create New Policy dialog.
   2. Enter Authentication restriction policy in the Name field.
   3. Select the condition created above (Authentication restriction) from the Condition drop down. Let us leave the policy group as default.
   4. In the Applies to section you would notice that "Server" is displayed. This is because this policy can only be applied to the server. To specify a filter we could click on the Edit Filter... hyperlink. Check the checkbox next to Server to apply the policy to the target (i.e. Server).
   5. Make sure that the Execution mode is set to None, since in this scenario we would be carrying out explicit administration.
   6. The Create New Policy dialog does not allow the Enabled checkbox to be checked when the Execution mode is set to None. (Note: This is in contrast to what BOL says, where it provides steps to create a policy stating to Enable the policy and set the Execution mode to None.) Therefore let us leave this policy disabled (i.e. do not check the Enabled checkbox). Click on OK.
      
   7. The policy is done!
6. The next phase involves the administration of the policy. As discussed earlier in the article, there are two types of administration: Explicit and Automated. Since this scenario requires explicit administering of the policy, we shall go ahead with it. Another reason why we cannot use Automated here is because the policy we created has its Execution Mode set to None.
   1. Right click on the server in Object Explorer, point to Policies... and select Run Now.... This would open up the Run Now dialog.
   2. In the From server drop down select the Authentication restriction policy that we created earlier.
   3. Let us first click on the Check button to check if the server setting conforms to the policy.
      
   4. Note the result of the check. Click on the View... hyperlink to get a more detailed report of the policy check.
      
   5. Since we now know that the authentication restriction policy is not enforced in the server environment, we could configure (or enforce) the policy.
   6. Close the above results and return to the Run Now dialog. Click on the Configure button. The policy will now be enforced upon the server.
      
      
   7. If you check the Security settings of the server, you would now see that the Server authentication is set to Windows Authentication mode.

Scenario 2 - Table Naming Standard

This scenario involves setting up a standard for table naming. The requirement is that, all tables created on the Southwind database have "TBL" as a prefix to their names. The policy to be created here should run in an automated fashion.

1. Before we go any further, let us create a database in the current SQL Server "Katmai" instance, and name it Southwind.
2. The first task now, as usual would be to identify the target of the policy to help determine the facet that we should use. Since it is tables that should be having a prefix in their names, we shall choose Table Facet. Another facet that could be used in this scenario is the MultipartName Facet (which by the way can be used for Views, Stored Procedures, User-defined Functions and more).
3. Let us first create the condition.
   1. Right click on the Conditions folder and select New Condition....
   2. In the New Conditions dialog enter Table naming as the name in the Name field.
   3. Select Table Facet from the Facet drop down.
   4. Finally in the Expression section select the Name property from the Field field, select LIKE from the operator field and type TBL%. Click on OK.
      
      The condition is now done.
4. Now on to creating the policy.
   1. Right click on the Policies folder and select New Policy....
   2. Enter Table naming policy as the name in the Name field.
   3. Select the previously created condition Table naming from the Policy drop down.
   4. We shall also put this policy into a policy group. Click on the New button next to the Group drop down. Enter Object Naming Policies as the name for the Policy Group.
   5. The target type to which this policy can be applied to appears in the Applies to region. Note that this is different from what was displayed in this area in Scenario 1. Since this policy was created from the Table Facet, it can only be applied to tables.
      The Server/Database/Table value which is displayed here shows the hierarchy of the target. By clicking on the Edit Filter... hyperlink we could define a filter on which tables we want this policy to be applied on.
      1. Click on the Edit Filter...hyperlink. This opens up the Policy Target Filter Settings dialog.
      2. The Server type filter cannot be specified with any filter. This is because the setting will be applied to the current server environment (or the server on which the policy is being enforced upon).
      3. Select the Database type, and specify the filter settings in the Filter grid. Select Name from the Field field, "=" from the Operator drop down and type Southwind in the Value field.
      4. The Table type filter too cannot be specified.
      5. The end result of the filter is: All tables in the Southwind database of the current server instance. Click on OK.
         
   6. The policy is now almost done. But before we can click on OK, we need to enable it and also specify a execution mode, which would then automate the policy enforcement.
   7. Click on the Enabled checkbox to enable the policy.
   8. Select Enforce as the execution mode. Click on OK. The policy is now complete.
5. Let us now test the policy on the Southwind database. The first step for us would be to subscribe to the policy, since the policy we created does not belong to the automatic-subscription-enabled default policy group.
   1. Right click on the Southwind database under the Databases group and point to Policies and select Group Subscriptions.
   2. In the Group Subscriptions dialog check the checkbox against Object Naming Policies. The policy group is now subscribed to and the policy is now up and running.
   3. Create a table by entering the following:
   4. use Southwind
      GO
      CREATE TABLE Employee
      (
          [ID] int,
          [Name] nvarchar(100)
      )
      GO
      
      Note that the policy does not allow you to perform this action. Read the error message that it provides.
   5. Try running SQL again by modifying the table name to TBLEmployee; you would notice that it executed successfully.
   6. Now, disable the policy by right clicking on the policy in the Object Explorer and selecting Disable.
   7. Now run the above SQL once again (with the table name as Employee). This time the table will be created with no problems.
   8. Enable the policy once again. Refreshing the server should show you a series of critical health warnings from the Databases group onwards until the Employees table as shown in the below image.
      
      Note: Even though these critical health states should be ideally shown once the policy is enabled and the server refreshed, it does not happen as such. In my case I had to right click on the policy and click on Test Policy... to get the health states to appear. Hopefully this should be fixed with the final product.

A little more on policy enforcement

While trying out the two scenarios you would have noticed that some policy violations can be at once configured to be corrected. For example in the first scenario, the authentication mode of the server was fixed by configuring the policy to change the authentication mode to Integrated from Mixed. But in the second scenario when the policy was enabled after the Employee table was created, it only displayed a Critical policy health state, but did not fix the table name. For a policy enforcement to be configured the following criteria will have to be met, otherwise a critical policy health state will be displayed.

• The properties that violate the policy should be settable
• The result of the configuration must be deterministic (a knowable value)

Conclusion

The Declarative Management Framework is a easy to use and powerful tool for the DBA to implement standards in his/her SQL Server environment. Policy based management has long been a need for the DBA what with the operating system already having it for quite a while.

This article focuses on how to implement policies. It does not however cover security and trouble shooting, which has been gladly left for the reader to explore. Though quite a great deal of the DMF has been implemented in the July CTP of SQL Server "Katmai", there could be more expected when the final product rolls out next year.

References

SQL Server "Katmai" July CTP Books Online

[ RATE this ARTICLE, and COMMENT on it ]