Policy-Based Management is a feature that introduced with SQL Server 2008. It allows us to set the rules we want in our systems, to determine our standards and to perform our controls automatically. For example, we can create a policy for control purposes so that the names of our stored procedures do not start with a number or database recovery models are not simple.
To comprehend “Policy Based Management (PBM)”, you need to understand some concepts.
Facet: A feature that can be managed by the PBM. For example, there is a facet named Database. And using this facet, you can create a policy that controls characters in the database names.
You can access all facets via SSMS as follows. You have to double-click on Facet details.
Condition: checks whether the sub properties of the related facets meet the specified condition.
For example, you can use the Name sub property of the Database facet to check if there are any characters in the database that you don’t want.
Let’s create a policy to check if there are characters that we don’t want in database names.
We click New Policy on the Management> Policy Management> Policies tab as follows.
We give a name to Policy and we need to create a condition from Check Condition.
We click on New Condition.
We give a name to the condition from the Name section.
We choose the Database facet in the Facet section.
We make the necessary settings as shown in the picture below.
By selecting “On Schedule” from the Evaluation Mode section, we determine at what intervals the policy will be checked, and then click the Enable check box.
If we choose On Demand, we only do checks when we execute policy.
In the server restriction section, if you have a server-based condition, you can control your condition by creating a server-based condition.
If we want to automatically check at regular intervals, we click new in schedule and determine the frequency of the policy check. As you can see below, we set it to work once every day at 00:00:000.
We created our policy. To run manual, we click Evaluate as follows.
When I performed this operation in my local, I got a result like this.
My failing database names are as follows:
The first database is failed because it contains “.” and “/”
The second database is failed because it has more than 20 characters.
hakan/.gurb11as.
AdventureWorks2014Yeni
When you refresh on Instance, a red color x mark appears next to Instance that looks like the letter. The reason for this is that there is an unsuccessful policy that SQL Server wants to notify us. When you see this mark, you must look at the defined policies and correct the problems.
There are many controls you can do on SQL Server with Policy Based Management. And I think a professional database administrator should use this feature that SQL Server provide us with all the details. You can see other controls you can do with Policy Based Management by typing “Policy Based Management” in our search section.
