Policy Based Management in SQL Server

This great feature was introduced in SQL Server 2008 and since then it has been considered as one of “standard” method to administer database proactively. As the name suggests, it allows DBAs to create various policies to enforce certain rules to single database, to group of databases or to whole server… for example default database backup location has to be some particular drive or every new database has to be in full recovery mode or each SP that are created and that will be created has to have prefix “USP_” etc.

It is very common issue of collaborative database development that developers like to keep follow their own naming convention then industry standards or MSFT recommendations for database objects. And they usually choose proper names for any objects they create, based on function / purpose of that object. But sometimes they like to choose name which could effect how SQL Server Query Execution planner behaves and you might see performance impact. For example in case of Stored Procedures, use of prefix SP in front of the stored procedure name. Now, I am not an expert in performance tuning but I know basic thing that it causes Query Execution planner to look into system stored procedure list at first because SP keyword is reserved for system stored procedures.

So instead of routinely monitoring any change made by devs and modifying / rolling back their change for such a silly name, it is far more convenient to have SQL Server watch for any thing like that and if such event occurs simply fail the execution and let developer know about this mistake. And this is exactly what I am going to demo here Smile

Policy Based Management (aka PBM) is made of 3 components. Facets, Conditions and Policies. And the order it works is, you use existing facet in condition and based on that condition you create a policy to be implemented on either single database or whole server.

1) Facets : They are kind of hard to explain by simple definition. Think of it as a class of .NET which exposes various properties. For example, database has different properties like Size, Name, Status, Owner, etc.… And you can use any of these properties to define condition. See this post for more detailed definition of Facets. By default there are number of pre-defined facets exist in SQL Server and there is no option to create user defined facet. A typical facet for Store Proc looks something like below,

SP_Facet

2) Conditions : As title suggests it is a conditions of monitoring. With help of facets you can create different conditions to monitor. Only restriction is you can’t use more than one facet per condition. By default there are few pre-defined conditions exist in “System Conditions” but usually you will have to create a condition that fits your requirement. A typical UI to create condition looks something like below,

CreateCondition1

 

3) Policies : This is last component of PBM. It is used to create policy based on pre-defined condition(s). And it is possible to combine more than one condition into one policy. A typical UI to create condition looks similar to below,

Policy

But if you want you can be fancy pants and use SQL scripts too to create conditions and policies. It is particularly helpful if you want to create same policy for multiple instances.

Say I want to create a policy so that all stored procedures created by developers on a database used by our application should have prefix “USP_” as an indication of user created stored procedure (plus an scenario to avoid use of SP prefix). For that I am going to need two conditions,

1) Create a condition that all SP should have prefix “USP_

2) Create a condition to select specific database (in this case database used by application)

To create a condition, go to Management –> Policy Management –> Conditions. Right click and select “New Condition”. This will open up a form similar to the one above (conditions). In that give some name to that condition which will be used by SQL Server to refer it. Then select facet for this condition which will be “Stored Procedure” in this case. And the last is to add expression (which is actual condition) for that @Name LIKE ‘USP_’ … it is kind of same as we write into any SQL query. And once you click on OK, this condition will be created.

CreateCondition1 CreateCondition2

To create another condition to select database name ‘TestDB’. Start new condition and this time select “Database” facet. And in expression create an expression something like, @Name = ‘TestDB’. This will cause to select database with name “TestDB”, just like any SQL statement would do.

Once we have both condition, next step is to create policy based on these conditions. For that right click on Policies and select “New Policy” this will open up a form just like shown above (in policy). Give some name to policy, then in check condition select condition of SP Name, which will cause all SP to use name prefix “USP_” but we don’t want to apply this to all databases we want to apply this condition to only database used by our application. So for that, instead of “In every database”, select another condition which we created to select “TestDB”. Final selection is to choose “Evaluation Mode”, it can be either On Demand, On Schedule, On Change: Log only and On Change Prevent …. and as name suggests for each mode, this policy can be either evaluated on demand (but it will not do anything), by some fixed schedule (ditto), when a stored proc created/modified with logging (this will cause an event to be logged in SQL Server logs), when a stored proc created/modified prevent invalid change (this will cause create/alter statement to fail with error message) respectively. In this case I want update process to fail so I will select last option, On Change Prevent. This option will shamelessly throw an exception and prevent any changes to that proc.

CreatePolicy1 CreatePolicy2 CreatePolicy3

Once that is done clicking on OK will create a policy which will check condition of name of Stored Procedure against database whose name is ‘”TestDB” and it anytime this policy gets violated, it will show an exception and prevent any changes to that stored procedure.

Now, for test .. I tried to create a stored procedure which didn’t had “USP_” prefix. And SQL Server threw an error,

Exception

 

As you can see this is kind of very simple example, but if used properly Policy Based Management can be a great tool to manage database proactively with least amount of pain Open-mouthed smile

That’s it for now …

It’s Just A Thought … Peace

Gaurang Sign

Leave a Reply

Your email address will not be published. Required fields are marked *