Policy-Based Management : SQL Server 2008
NOTE: Declarative Management Framework has been renamed to Policy-Based Management so I just changed the Title of the article, the contents are unchanged.
Hi, I am writing this post after somewhat long time … I hope you all are continuing with your learning on SQL Server 2008. Today I have some time to blog so I am writing something.
So after thinking hard what to write about I thought why not to write about the Declarative Management Framework. A wonderful solution for the DBAs who keep themselves busy patching and closing the mouse holes what other folks have created by violating some of the best practices, may it be naming convention of the Tables or they have created the Stored Proc. in the wrong schema. So if you are wondering what is this “Declarative Management Framework” then, please stop wondering and download the latest SQL Server 2008 November 2007 available, install it, read this article and start practicing. I promise you will like it.
What is Declarative Management Framework
Declarative Management Framework is a policy-based system for managing one or more instances of SQL Server 2008 which means that now rather than turning on/off the physical knobs to do the effective database administration you would have logical knobs to do it which could even be exported to a file for the future use and could be applied to a bunch of SQL Servers in a Group.
DBAs don't have to worry, it is not any other Framework like .Net Framework where you need to reach the Visual Studio and write custom code, test it, deploy and manage it. It is very simple and all the policies could be authored well from the SQL Server 2008 Management Studio.
Components of Declarative Management Framework
Declarative Management Framework has three components:
- Policy management
- Explicit administration
- Automated administration
Policy Management
Policy administrators create policies.
Explicit Administration
Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly make the targets comply with a policy.
Automated Administration
Policy administrators can run policies on demand, or enable automated policy execution by using one of the following execution modes:
- Changes are attempted, prevent out-of-compliance. This uses DDL triggers to prevent policy violations.
- Changes are attempted, log out-of-compliance. This uses event notification to evaluate a policy when a relevant change occurs.
- On schedule, log out-of-compliance. This uses a SQL Server Agent job to periodically evaluate a policy.
When automated policies are not enabled, Declarative Management Framework will have no effect on system performance. If enabled the system will have only negligible effect on the performance depending on the number of policies applied on a particular object.
Declarative Management Framework - Key Terms and Concepts
Managed Target
Entities that are managed by Declarative Management Framework, such as an instance of the SQL Server Database Engine, a database, a table, or an index. All targets in a server instance form a target hierarchy. A target set is the set of targets that results from applying a set of target filters to the target hierarchy, for example, all the tables in the database owned by the HumanResources schema.
Facet
A set of logical properties that model the behavior or characteristics for certain types of managed targets. The number and characteristics of the properties are built into the facet and can be added or removed by only the maker of the facet. A target type can implement one or more management facets, and a management facet can be implemented by one or more target types.
Condition
A Boolean expression that specifies a set of allowed states of a Declarative Management Framework managed target with regard to a management facet.
Policy
A Declarative Management Framework condition and the expected behavior, for example, execution mode, target filters, and schedule. A policy can contain only one condition. Policies can be enabled or disabled.
Category
A user-defined category to help manage policies. Users can classify policies into different policy categories. A policy belongs to one and only one policy category. Database owners can subscribe a database to a set of policy categories. Only policies from its subscribed categories can govern a database. All databases implicitly subscribe to the default policy category.
Execution Mode
Specifies how a policy will be executed. On-demand execution modes are Check and Configure. Automated execution modes are:
- Changes are attempted, prevent out-of-compliance
- Changes are attempted, log out-of-compliance
- On schedule, log out-of-compliance
Effective Policy
The effective policies of a target are those policies that govern this target. A policy is effective with regard to a target only if all the following conditions are satisfied:
- The policy is enabled.
- The target belongs to the target set of the policy.
- The target or one of the targets ancestors subscribes to the policy group that contains this policy.
Usage Scenarios : Examples
Declarative Management Framework would be helpful in resolving the issues presented in the following scenarios:
- A company policy prohibits enabling Database Mail or SQL Mail. A policy is created to check the server state of those two features. An administrator compares the server state to the policy. If the server state is out of compliance, the administrator chooses the Configure mode and the policy brings the server state into compliance.
- The AdventureWorks database has a naming convention that requires all stored procedures to start with the letters AW_. A policy is created to enforce this policy. An administrator tests this policy and receives a list of stored procedures that are out of compliance. If future stored procedures do not comply with this naming convention, the creation statements for the stored procedures fail.
A Quick Walk Through : Declarative Management Framework
We are going to create a quick policy that will make sure that the CLR integration is not enabled on this instance of SQL Server 2008. I am adding screen shots below, in case they are not clearly visible, click on them and they will be enlarged.
Step 1: We will reach the SQL Server 2008 Management Studio, log in and then expand the Management Node as demonstrated below.
Step 2: Now we will first create a condition so expand the node CONDITION and right click and in the menu select "New Condition". A new dialog box appears, in the "Name" field type in a name for this condition like "Condition_Disable_CLR" or any fancy name you like. In the Facet select "Server Configuration". Please see the screen shot below.
Step 3: In the Expression section reach the Field and select "@ClrIntegrationEnabled" from the Drop Down, Operator should be "=" and in the Value field select "False" from the Drop Down. Press OK and you have created a condition.
Step 4: Now it is the time to create a policy based on this condition. So right click on the "Policies" node in the Management Studio. Select "New Policy" from the menu. This brings up a new dialog box. In the "Name" field type in a name for the policy like "Policy_Disable_CLR". In the "Check Condition" select the condition that you have authored last in this case "Condition_Disable_CLR" which you would find under "Server Configurations". Select the "Execution Mode" as "On Demand" and Press OK.
Congratulations !!! you have created your first policy on the SQL Server 2008 Server.
Step 5: Now expand the "Policies" Node in the Management Studio and locate the Policy that you have created just now.
Step 6: Right click on the Policy and select "Test Policy" this will bring up the dialog box. If the CLR Integration is disabled on this Server instance then it will be notified else if the CLR was enabled anytime then it will be notified.
Policy Not Violated
Step 7: Now I will enable the CLR by executing the System Stored Procedure
sp_configure 'clr enabled', 1
Now we will execute "RECONFIGURE" to make the changes effective.
We will now retest the policy and see the result.
Policy Violated
So, I hope the DBAs will find it comfortable authoring policies for effective administration. If you like or dislike the article please post it .. it helps me to bring better articles every time. I will bring more articles on SQL Server 2008 Enhancements so keep a watch.