Monitoring and Preventing policy violation activities on Database objects, Databases, Instances on SQL Server 2008 using Policy Based Management (PBM)
Policy-Based Management used to manage multiple SQL Server instances from a single location by creating policies that control security, database options, object naming conventions, and other settings at a highly granular level.
Policies can evaluate servers for compliance with a set of predefined conditions and prevent undesirable changes being made to servers.
- Management is centralized, thereby reducing the need to configure each server separately.
- Administration is simplified, reducing the effort required to maintain standardization and compliance, even in complex environments
- Configuration is straightforward and can be done entirely within SQL Server Management Studio
- Backwards compatibility supports managing instances of SQL Server 2008, SQL Server 2005, and SQL Server 2000
- The following Editions supports Policy Based Management
SQL Server 2008 Enterprise
SQL Server 2008 Standard
SQL Server 2008 Workgroup
SQL Server 2008 Web
SQL Server 2008 Express
SQL Server 2008 Express with Tools
SQL Server 2008 Express with Advanced services
The Policy based management has three components
1. Policy Management
- Creating and maintaining Policies.
2. Explicit Administration
- Administrator explicitly runs and checks one or more policies.
3. Evaluation Modes
- There are four evaluation modes
3.1 On Demand : (Non-automated mode and Default) – It can be evaluated by a specific user.
3.2 On Change: prevent : (automated mode) – It uses DDL triggers to prevent the policy violations.
3.3 On Change: log only : (automated mode) – It uses event notification to evaluate a policy when a relevant change is made.
3.4 On schedule : (automated mode) – It uses SQL Server agent job to periodically evaluate a policy.
Policies are stored in msdb database.
Policy based management terms and concepts :
1. Target :
Target can be entities that are managed by Policy-Based Management, such as an instance of the SQL Server Database Engine, a database, a table, or an index.
2. Facet :
A predefined group of logical properties.
3. Condition :
Evaluates the property of facets. True/False
4. Policy :
A policy can contain only one condition. What kind of condition to be evaluated.
We have defined some rules to follow the tables naming convention. Name of the table should be started with ‘Tb_’. There is a change to violate the rules by many others (Database Developers).
As a DBA, We have to monitor these kinds of rule violations. We can define policy based management activities.
1. Implementing “On Demand” evaluation mode:
We have to define a policy to monitor and validate the Table naming conventions. Now we would like to implement a policy to monitor the table’s naming convention using On Demand evaluation mode.
1.1 Expand Management node and Policy Management under Object Explorer.
1.2 Right click on Conditions node and Click New Condition…
1.3 The following wizard will appear and give the required information / validation.
1.4 Right click on Policies node and click New Policies…
1.5 The policy should be evaluated with each tables on a specific Databases (Not on all Databases)
1.6 So, we have to define a one more new condition for a particular database. Click New condition… as given below to define a condition.
1.7 Now we have created two conditions (Table Naming Convention, Database Name)
1.8 Again we can proceed with Policies. Select the condition : Database Name
1.9 We have created a policy.
1.10 Open a Query window and create two tables in DotnetFunda database.
1.11 Two tables (Sample1, Tb_Table1) created successfully on DotnetFunda database.
1.12 Anyway we have defined a policy on DotnetFunda database only.
1.13 Now, we going to evaluate the policy: Table Naming Convention.
1.14 Right click policy Table Naming Convention under policies node and click Evaluate item. We can not Enable the Policy during the Evaluation mode is On Demand.
1.15 Two tables have been evaluated. In Target Details area, Each line item denotes each Tables.
1.16 How do we know which table matches / which are not?
1.17 Click the Link View… to display the detailed description.
1.18 Click the Link View… to display the detailed description.
2. Implementing “On Schedule” evaluation mode:
As we have created On Demand Evaluation mode policy, We would like to create On Schedule evaluation mode policy.
Ensure that SQL Server Agent service should be started.
2.1 Right click on Policy: Table Naming Convention and Click the Item Properties
2.2 Change the Evaluation mode to On Schedule. And Click the New Button as given below.
2.3 Name the Schedule and required information as given below
2.4 We have scheduled the job for every 2 Minute(s) and Click the button OK on
Open Policy – Table Naming Convention dialog box.
2.5 Right click on Policy : Table Naming Convention and Click Enable the Policy as per the schedule.
2.6 Now the Policy enabled and the Policy is on schedule. It will be evaluated every 2 minutes.
2.7 Now, We can create three tables as given below. Two tables are not matching the Policy condition and Only one table matches with the policy condition.
2.8 Once created the tables, Right click one Policy: Table Naming Convention and Click View History item as given below.
2.9 Once selected the View History Item, The following are the Policy violated line items. The policy automatically evaluated every 2 Minutes (12:56:01 PM , 12:58:01 PM,…), By selecting the line item we can identify the object name(Table) which has been violated the policy.
3. Implementing “On Change: Prevent” evaluation mode:
As we have created On Schedule Evaluation mode policy, We would like to proceed with On Change: Prevent evaluation mode.
The On Change: Prevent evaluation mode will not be there for all Facets.
The On Change: Prevent option evaluates the policy whenever the property in the facet is changed and actually prevents the change; this mode uses DDL triggers to enforce the policy. Not all changes can be detected and rolled back by DDL triggers; the evaluation mode drop down list will include the On Change: Prevent option only when it is available.
We have to define some rules to follow the stored procedure naming convention. Name of the stored procedure should be started with ‘USP_’. There is a change to violate the rules by many others (Database Developers).
As a DBA, We have to monitor / prevent these kinds of rule violations. We can define policy based management activities.
3.1 Right click on Conditions node and Click New Condition…
3.2 New Condition Name as Stored Procedure Naming Convention, Select the Facet as Stored Procedure, We should validate the @Name field of Stored Procedure Facet, LIKE Operator used, The Value is ‘USP[_]%’ and Click the OK Button. The stored procedures name should be started with USP_.
3.3 We have created one more condition Database Only, We would like to enforce the policy on DotnetFunda database only.
3.4 Now we have created two conditions (Stored Procedure Naming Convention, Database Only).
3.5 We would like to create a Policy with the following information.
Give a policy name as Stored Procedure Naming Convention.
Choose Stored Procedure Naming Convention on Check Condition.
Select the Database Only condition on Against Targets as given below
Choose On Change: Prevent evaluation mode on Evaluation Mode.
3.6 Finally, Click the OK button as given below.
3.7 Now, We have created one Policy. This is the time we have to enable the Policy.
3.8 Right click on Policy and Click the Enable item as given below
3.9 Now, The policy enabled successfully.
3.10 We can create a stored procedure as given below
3.11 But, the stored procedure name doesn’t match the policy condition. Now, what will happen? It will thrown an Err.
4. Implementing “On Change: Log Only” evaluation mode:
As we have created On Change: Prevent Evaluation mode policy, We would like to proceed with On Change: Log Only evaluation mode.
This evaluation mode uses event notification to evaluate a policy when a relevant change is made. This evaluation mode is similar to On Change: Prevent mode however this will only log the changes and don’t prevent it from changing the value.
4.1 Right Click on Policy : Stored Procedure Naming Convention and Click Properties as given below
4.2 Change the Evaluation Mode to On Change: log only and click the OK button.
4.3 Create a stored procedure as given below
The procedure executed successfully
Command(s) completed successfully.
4.4 Anyway The stored Procedure successfully created. But, the Policy violation information logged in SQL Server Error Log.
4.5 Expand the SQL Server Logs node under Management node in Object Explorer as shown below
4.6 Double click the Current Log activity, It shows the detail error log as given below.
SELECT [Name],date_created,facet,obj_name FROM msdb..syspolicy_conditions
SELECT [Name],date_created FROM msdb..syspolicy_policies
The Policy Based management (PBM) helps DBAs to achieve the Policy based activities for Database Objects, Databases & Server Levels.