Whenever we develop application of maintain application we define sets of rules or policies like naming convention , data type, database & SQL SERVER properties (like which property should be unable or disable) but the problem is to cross check or to enforce these properties is very tedious.
To enforce the policies SQL SERVER provided a great feature which is Policy Based Management. This is the feature which helps you to not only write the policies or rules but also enforce and cross check whether those rules or policies is followed in your environment or not.
Although these feature is mainly for A DBA but I think it is good for a everyone who love SQL Server.
Now let me share an example which will help us to better understand Policy Based Management.
So to begin with firstly we have to open the Policy Based Management’s interface. Which we can get in Management folder in SQL SERVER’s object explore window. (You can see below yellow highlighted)
So when you expand the Policy Management in Management feature You will see 3 different folders which are facets , condition and policies.
So there are around 84 in build facets which is basically properties for different conditions.
Conditions are basically simple check applied using facets.
Policies are created on condition which will enforce once it created.
Below I am creating policy to check table who has row count =0 (Little bit odd) .You can try with stored procedure name not started with sp_ (will be good start as well).
So , Now create a simple condition by right click and choosing menu New condition
You will get following screen just add condition
We are creating a simple condition which will check the table which has RowCount is 0.
Once you created this condition you will get condition in conditions folder.
Now once condition is created we have to create policy. To create policy we have to right click on policy and select new policy.
When you click the new policy you will get following screen
Now we can add check Condition which we have created as shown in below figure. We can apply policy against target Like in below snap we want policy against every table for IndiandotnetDB
We can set evaluation mode according to our need.
Once you saved the policy we can run it any time.
We can evaluate policy by right click Evaluate as shown in below figure
When we run the policy we will get following result
So , If you see above highlighted row which means a table contain Row Count.
I hope with above steps you understand a basic way to create Policy Base Management concept.
Policy based management is surely a great tool to manage database.
Please feel free to give a chance whether you are DBA or not.
Enjoy PBM !!
Happy learning !!
RJ
