Monitoring Database – Using Policy Based Management

In this article we will make use of Policy based management to check some of the basic but important properties of database. We will use policy based management to report if the specific database properties are changed. Some of the basic database properties that we monitor here are to database owner, recovery model of database, compatibility level, auto shrink and auto update statistics properties of database, etc.

Prior to SQL Server 2008, we use to write complex scripts and schedule jobs to get reports on these database properties and if some makes any changes to the scripts and it fails, again we have to spend time to fix the scripts and make sure we get the reports. Thanks to SQL Server 2008 policy based management, now their is no need to write scripts as most of the monitoring task can be done by using this very good feature.

Step as how to make use of policy based management to monitor these database properties and to report in case of violation:-

1) Connect to the database server and navigate to Management à Policy Management à Policies. Right click on Policies, thenclick on New Policy to start creating a Policy.

2) In ‘Create New Policy’ window give a relevant name to Policy like in example below its ‘Database Monitor Policy’. Then in the check condition click on New condition (if you have already created a condition you can either select the condition.  But here we will create a new condition).

Click on ‘New Condition..’ New window will open.

3) Here type the name of new condition like ‘Database Monitoring Condition’ and then against Facet click on drop down and select Database. After selecting the database (next to Facet), start adding expression. In this case we will select the database properties fields that we need to monitor and get notified if the default values are changed.

@AutoShrink: – Auto Shrink of database is usually and preferably set to false. If the auto shrink property is set to true and total free space in database fall below the threshold value, SQL Server will start shrinking the database. In times this can happen during peak working hours and can cause blockings and performance issues. So as a best practice this option is always set to false.

@AutoUpdateStatisticsEnabled: – Auto update statistics option of database is set to true to make sure that the database statistics are always up to date. Although some time you may have to manually update the statistics also (even if this option is true) depending on the insert delete updates in the database.

@CompatibilityLevel: – To make full use of the new features available in SQL Server 2008, your database should be in SQL Server 2008 compatibility level i.e. 100. Although there are some applications that prefer to remain in previous version compatibility level to avoid costs linked to making necessary changes in code. What I believe is, there is no point moving to newer version if you are not willing to make use of the new features available.

@IsAccessible: – Is set to true to allow connections to database

@Owner: – As a best practice, owner of the database should always be ‘sa’ and not any individual user.

@ReadOnly: – If true then user cannot make any modifications to the database. (In the example I have considered that all database in production servers are set to read write mode. Although some exceptions might be there)

Now, after selecting all the fields and values click OK. You can select more or less fields as per your need.

4) Back to Create New Policy window. After selecting the newly created condition against ‘Check Condition’ option just makes sure that every option is checked if you want this policy for all databases.

Now, its time to select the evaluation mode for this policy. In this case you will have only two options either to run on demand or to schedule it via SQL Server agent job. Ideally there are four evaluation modes available for a policy but depending on the conditions that you have selected, these evaluation options will vary.

After selecting the Evaluation mode click OK. In this example we have selected the evaluation mode as ‘on demand’.

5) Now to evaluate the policy just navigate to Management -> Policy Management -> Policies. Just right click on the newly created policy and select Evaluate.

6) Once evaluation is complete, you will get the below screen with the evaluation results. For the databases where all properties are as per policy you will see green tick and for the once which deviate for the policy setting are marked by red cross.

To check the details of the policy results just click on View under Details column. A new screen will open with the expected values (policy settings) and the actual values. Thus it will help you to understand as which database properties need to be corrected to the expected values.

7) If you want to save the evaluation results, just click on the Export Results option. You can save this report in xml format and can view it later by opening it in any xml editor.

To Conclude, Policy based management is a very good feature introduced in SQL Server 2008. You can make use of these features to monitor/force policies as per your requirements. There are many more options available in Policy Based management that will help you to manage your database servers more easily and to enforce almost all the standards as per your company requirements.

Policy-Based Management in SQL Server 2008

Some time ago I promissed publishing a post on Policy-Based Management feature in SQL Server 2008. Well, it's time to fulfil my promise! :) This is a rather large post, I hope you find the information it contains useful!

Policy-Based Management is indeed one of the greatest administration features in SQL Server 2008. With this feature you can easily manage one or more instances of SQL Server!

The main concept in Policy-Based Management, is that Policy Administrators can set up policies that are evaluated upon certain events within the scope of the monitored SQL Server instance(s). After setting up the policies, based on certain scenarios, the policies are evaluated and specific actions are automatically performed as set up in the active Policies. 

To this end, there are four evaluation modes:

• On demand - This mode evaluates the policies when explicitly requested by the user.
• On change: prevent - This mode uses DDL triggers in order to prevent Policy violations.
• On change: log only - This mode allows Policy violations but it logs each violation using event notification. It logs the violations to the SQL Server logs and Windows Application logs.
• On schedule - This mode allows the user to schedule (using a SQL Agent job) when the policies will be evaluated.

Let’s get a first look and feel at the Policy-Based Management set up dialogs in SSMS. In the following screenshot you can see the Policy Management module in the Object Explorer in SSMS:

• When right-clicking on Policies you can create a new Policy.
• When right-clicking on Conditions you can create a new condition and then assign it to an existing or a new Policy.
• The Facets is a collection of SQL Server objects that allow you to set up conditions and policies against their properties. By right-clicking on a Facet you can create a condition or a Policy.

Note that the Policy Management module falls under Management as its scope is for the entire SQL Server instance. Of course, via the Policies you create, you can evaluate multiple SQL Server instances, certain databases, even certain database objects!

OK! Now let's proceed with an example, showing how we can set up policies based on the abovementioned evaluation modes. For this post, I will be using the "AdventureWorks2008" sample database.

On demand
We right-click on Policies and we select New Policy. We are presented with the following dialog where we provide a name for the Policy and then we either select the check condition or we create a new one. In this case we will be creating a new condition:
 

The next screenshot shows the newly created condition:

For creating a new condition, you must provide a name, select the Facet (in this example) the Stored Procedure, and set the expressions for evaluating the properties you like. In this case we want the condition to track all the stored procedures that their names start with 'sp_'.

We click on the OK button and we are returned to the Policy:

You can see that the Policy now contains the "SPNames" condition we created in the previous step. Also, I set up the Evaluation Mode to "On Demand". Though, this type of condition allows you to select any Evaluation Mode.

The available Evaluation Modes are dependent on the type of the condition. For example there are conditions for which a DDL trigger cannot be executed so the “On change: prevent” mode cannot be selected in such case, etc.

Also, in the “Against targets” list you can target specific databases against which the condition will be evaluated. Additionally, in the“Server restriction” you can select against which SQL Server instances the condition will be executed. Remember: Policy-Based Management allows managing multiple instances of SQL Server!

Now, let’s set the Policy to only evaluate the “AdventureWorks2008” database by clicking on the “In Every - Database” list item. Here’s the new condition for restricting the Policy’s evaluation on the specific database:

We click on OK and we are returned back to the Policy properties dialog:

I will not add a condition for the Server restriction as I am only using a single SQL Server instance for this post.

That’s it! We click on OK and the Policy is ready! As this Policy’s Evaluation Mode has been set to “On demand” we cannot enable it. It wouldn’t make any sense after all. We can only use it by right-clicking on it and selecting “Evaluate”. This is the purpose of the On Demandevaluation mode after all! :)

After running the Policy we get its evaluation results:

From the above screenshot we can conclude that at the time being all stored procedures within the “AdventureWorks2008” database comply with the condition set up in the Policy.

Now, what about adding a new procedure on the “AdventureWorks2008” database having the name “sp_SampleSP”? Let’s do so, re-run the Policy and check the evaluation results!

Here are the results:

Oops, we got an error message by the Policy. Let’s click on [Details] View… to see what’s wrong:

We can see that the Policy reported that the stored procedure “sp_SampleSP” does not comply with its condition of not having stored procedures with names starting with 'sp_'.

The above is a very simple example of what a Policy can do. Imagine what else you can do with Policy-Based Management thus making your life easier! :)

Let’s delete now the sample stored procedure we earlier created. We need to comply after all! :)

On change: prevent
If we want to change the Evaluation Mode of the above Policy to “On change: prevent”, we can double-click on the Policy, change it and then enable the Policy:

Now let’s try to create again the same stored procedure:

As you can see from the above screenshot, the Policy prevented the Database Engine from creating the stored procedure as it does not comply with the naming condition specified by the Policy!

On change: log only
Similarly, we can change the Evaluation Mode of the Policy to “On change: log only”. This will allow the stored procedure to be created but it will also add a log entry in SQL Server Logs and Windows Application Log reporting the violation of the Policy:

On schedule

Last but not least, you can set up a schedule for when the Policy should be executed. This will create a SQL Server Agent job that will execute the Policy at the selected time.

Summary

This post was a very basic example on how you can use Policy-Based Management in SQL Server 2008. You can build really advanced Policy scenarios where you can have multiple Policies and conditions, evaluating a large set of actions in more than one SQL Server instances.

The Policy-Based Management feature in SQL Server 2008 is extremely powerful and allows setting up strict Compliance scenarios ensuring the health and desired structure of the monitored SQL Server instances and the databases that exist within them.

The best way to learn more about Policy-Based Management in SQL Server is to get experimented with it in order to fully realize its potentials.

You can also check out this link for more information on Policy-Based Management in SQL Server.

Until next time!


Source: 
http://aartemiou.blogspot.com/2010/04/policy-based-management-in-sql-server.html

Using Policy Based Management in SQL Server 2008

ProblemAs we are reviewing the new features in SQL Server 2008, we found one that looks really interesting - Policy-Based Management.  Could you help us to understand how this works and provide some examples?  Can you please explain each of the components and how to manage them in the interface and with commands?

SolutionPolicy-Based Management is indeed a new feature in SQL Server 2008.  It allows you to define and enforce policies for configuring and managing SQL Server across the enterprise.  Originally this feature was called the Declarative Management Framework but has since been renamed.  There are a number of terms that we need to define in order to begin to understand Policy-Based Management:

• Target - an entity that is managed by Policy-Based management; e.g. a database, a table, an index, etc.
• Facet - a predefined set of properties that can be managed
• Condition - a property expression that evaluates to True or False; i.e. the state of a Facet
• Policy - a condition to be checked and/or enforced

Policy-Based Management is configured in SQL Server Management Studio (SSMS).  Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes:

Expand the Facet node to see the list of facets:

As you can see there is a rather comprehensive collection of facets predefined in SQL Server 2008, allowing you to manage just about every aspect of SQL Server.  Double click on a facet to see the actual list of properties in the facet; e.g. double click the Database facet:

These facet properties are used to specify a condition; e.g. AutoShrink = False means that you do not want to automatically shrink database files.  A policy specifies an expression that evaluates to True or False.  The expression can be made up of one or more conditions logically joined by And / Or.

In this tip we are going to gain an understanding of Policy-Based Management by walking through the following demonstration:

• Create a Condition
• Create a Policy
• Evaluate a Policy

The demo steps below were only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008. 

Create a Condition

The starting point in Policy-Based Management is to create a Condition.  Right click on Conditions in the SSMS Object Explorer (under the Management | Policy Management node) then select New Condition from the menu.  Fill in the dialog as follows:

You select a single Facet for a Condition, then enter an Expression.  The Expression evaluates to either True or False.  This is the essence of Policy-Based Management which will test whether the Condition is True.

Create a Policy

Right click Policies in the SSMS Object Explorer (under the Management | Policy Management node) then select New Policy from the menu.  Fill in the dialog as follows:

The Check Condition drop down will include the list of conditions that you have defined.  You can check Every Database in the Against targets list, or you can click the glyph (between Every and Database) and define a condition.   Execution Mode can have one of the following values:

• On Demand (this is the default)
• On Schedule
• On Change - Log Only
• On Change - Prevent

The On Demand option only evaluates the policy when a user right clicks on the policy in the SSMS Object Explorer and selects Evaluate from the menu. 

The On Schedule option takes advantage of SQL Agent to execute a job on a particular schedule to check the policy.  After selecting On Schedule from the Execution Mode drop down list, you can click either the Pick or New button.

To pick an existing schedule, make a selection from the available options:

To create a new schedule, fill in the familiar schedule dialog:

When policy evaluation is scheduled, any violations are logged to the Windows Event Log.

The On Change - Log Only option evaluates the policy whenever the property in the facet is changed and any violation is logged to the Windows Event Log.  The On Change - Prevent option evaluates the policy whenever the property in the facet is changed and actually prevents the change; this option uses DDL triggers to enforce the policy.  Not all changes can be detected and rolled back by DDL triggers; the Execution Mode drop down list will include the On Change - Prevent option only when it is available.

One final note on the policy setup concerns the Enabled check box.  When the Execution Mode is On Demand, the Enabled check box must be unchecked; for all other options you must check the Enabled check box in order for the policy to be evaluated.

Evaluate a Policy

To evaluate a policy on demand, right click on the policy in the SSMS Object Explorer and select Evaluate from the menu.  The following is a partial screen shot of the output from evaluating a policy on demand:

 

The green check icon signifies that the policy evaluated to True for the databases shown.  Not shown above is a Configure button that allows the user to automatically fix a target where the policy evaluates to False.

Right click on a database in the SSMS Object Explorer and select Properties from the menu.  Click the Options page and change the AutoShrink property to True.  Evaluate the policy again and you will see the following output:

Note the red icon with the X indicating that policy evaluation failed for a particular database.  Not shown above is the Configure button which you can click to automatically change the AutoShrink property to comply with the policy.

Edit the policy and change the Execution Mode to On Change - Log Only.  Select a database and change the AutoShrink property to True.  Open the Windows Event Viewer, click on Application and you will see an event that was written when the policy evaluation detected the violation:

To test the On Change - Prevent Execution Mode for a policy, create a new condition and a new policy.  Create a new condition as follows:

Now create a new policy as follows:

This policy will prevent a table from being created if the table name does not begin with 'tbl_'.  Open a New Query window in SSMS and enter a create table script.  When you execute the CREATE TABLE script you will get the following error message and the table will not be created:

CREATE TABLE sample ( message varchar(256) ) Policy 'Table Prefix Must Be tbl_' has been violated by '/Server/(local)/Database/demo/Table/dbo.sample'.This transaction will be rolled back. Policy description: ''Additional help: '' : ''. Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 50 The transaction ended in the trigger. The batch has been aborted.

Source:

http://www.mssqltips.com/sqlservertip/1492/using-policy-based-management-in-sql-server-2008/