Policy-Based management

DBA ‘s can define policies and ensure that they are being adhered to by evaluating the

• 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

Policies:

• On demand
• On schedule
• On change: Log only
• On change: Prevent

• Facets: Facets is the property of SQL Server which the policy will consider managing. There are several facets on which policies could be implemented. For example, we will use the “Database Option” facet to implement a policy which will ensure that the AutoShrink option should be TRUE for all hosted databases on the server. Similarly, we will be creating policies on the Stored Procedure facet.

Scenario1: We will create an on demand policy to ensure that all the databases have the Auto Shrink option set to True. By default, a database that is created has Auto Shrink set to False, as shown in the figure below.

Create a Condition

Created a condition below

Condition Created

Creating a New Policy

On Auto Shrinking for the databases

Created Policy

Evaluate the Policy

Second One we can See with Green Color means Condition is passes, Because the Condition is satisfied

Scenario 2: The scenario is ensuring that each user defined Stored Procedure created on the server is prefixed with ‘Fin_%’. Let’s design a policy for this.

Next, provide a name to the condition “Checking Procedure Name”, and select the facet from the Facets drop down as “Stored Procedure”. In the Expression Editor, choose the field from the drop down “@Name”, select operator as “LIKE”, and value as ‘FIN_%’.

The condition will check the names of all the Stored Procedure to be prefixed with FIN_.

CLICK OK.

Creating Policy

Provide the name as “SP Policy”; from the Check condition drop down, select the condition we just created, i.e.,Checking Procedure

Name. And from Targets, check every Stored Procedure in every database as we want every Stored Procedure to conform to this policy.

Next is the evaluation mode. Let’s keep it “On Change: Prevent” for this example. On Change: Prevent will evaluate the policy for any further procedure creation, and will prevent it from being created unless it is as per the policy.

Also, do remember to check the Enabled check box, and click OK.

Evaluation

We have been able to create the policy, now let the Policy Manager evaluate the policy. To evaluate this policy, open the SSMS query analyzer and try to create an SP under any database

.

Mention with Starting FIN