This article will cover the most spectacular feature of SQL 2008 – Policy-based management and how the configuration of SQL Server with policy-based management architecture can make a powerful difference. Policy based management is loaded with several advantages. It can help you implement various policies for reliable configuration of the system. It also provides additional administration assistance to DBAs and helps them effortlessly manage various tasks of SQL Server across the enterprise.
Basics of Policy Management
SQL server 2008 has introduced policy management framework, which is the latest technique for SQL server database engine. SQL policy administrator uses SQL Server Management Studio to create policies that can handle entities on the server side like the SQL Server objects and the instance of SQL Server databases. It consists of three components: policy administrators (who create policies), policy management, and explicit administration. Policy-based management in SQL Server assists the database administrators in defining and enforcing policies that tie to database objects and instances. These policies allow the administrator to configure and manage SQL server across the enterprise.
The following advantages can be achieved by appropriate administration of policy management system.
- It interacts with various policies for successful system configuration.
- It handles the changes in the systems that are the result of configuration against authoring policies.
- It reduces the cost of ownership with simple elaboration of administration tasks.
- It detects various compliance issues in SQL Server Management Studio.
Policy Management Terms
To have a better grip on the concept of Policy-based management there are some key terms you need to understand.
- Target – A type of entity that is appropriately managed by Policy-based management. For example, a table, database and index, to name a few.
- Facet -A property that can be managed in policy-based management. A clear example of facet is the name of Trigger or the Auto Shrink Property of database.
- Conditions – Criteria that specifies the state of facet to true or false. For example, you can adjust the state of a facet that gives you clear specifications of all stored procedures in the Schema “Banking”.
- Policy – A set of rules specified for the server objects or the properties of database.
Practical Example of Policy Management
Exploring of Facets
Facets are database objects and each of them is a container of one or more database object. First, you need to navigate the object explorer and expand the policy-based management node and the management node. You will see conditions, policies and facets nodes. SQL Server 2008 has many different facets available to use.
To view the list of facets, expand the facet node
Double click on each of these facets to the list of the facet properties.
Let us understand the two next elements of creating condition and creating policy with real life example of Statistics. We will try to create statistic property of the database. We have property of statistic
IsAutoCreated. We can set that using database property window under option tab. This property takes two values
We will follow up on this property in different steps. We will first create condition and right after we will use the same condition in a policy. That policy will be evaluated by user. User will have to two options either let us evaluated by scheduled task or fix non complaining policy manually.
Create a Condition
Creation of condition in Policy-based management is the next thing after identifying the problem. In our case, we want to make sure that everywhere
IsAutoCreate is set to
Each facet displays different kinds of properties. As we are interested in the
IsAutoCreated property of the statistics we have to select facet as Statistic.
Create a Policy
Creating a policy is the next important task after creating a condition. The condition has to be created to select the proper property of the object. However, a policy is created to specify the location where the condition has to be applied.
Please follow the instructions given in the above image. Make sure to select all target databases. In given example I have two database installed on my server that brings up two different targets servers.
This brings up an interesting concept of targets. Targets are database objects. They can be whole database or single parts of the database. It may be possible that they are different kind of objects but have same kind of properties.
While creating a policy we have kept the evaluation mode as “on demand”, which means that we will be running this policy manually, instead of scheduled job. Scheduled job is good idea to run policy.
Evaluate a Policy
As in the previous step, we have selected to evaluate the policy manually; we will evaluate that using SSMS. Right click on policy brings up with lots of options. Select Evaluate from the menu, this will bring up the following image.
On the screen of Evaluate Policies there is a button which suggests evaluating the policies. Once clicked it will give the following kind of screen with a status.
You will see a green icon in the image. This icon indicates the policy evaluated the status to
True. If you notice you will find that in our example all the policies are complied with.
Fix Non-complying Policy
Now, let us change the
IsAutoCreate policy of one of test databases to
true and run the evaluation all over again. If you observe you will notice that one of the statuses is marked as
false with a little red image on the left.
Furthermore, once you select the checkbox and clicked on the Apply button you will find an additional checkbox on the side. This Apply button will raise a popup confirming that the property of the target has been changed to comply with the policy. Next, click Ok to confirm. This will change the properties of the Test database to comply with the new policy on all of our targets specified earlier.
In our example, we have manually evaluated the policy and fixed its noncompliance. With powershell you can perform the same using SQL Server Agent.
Policy-Based Management empowers you with greater control over the procedures of database as a Database Administrator. It provides you the ability to enforce paper policies at database level. Paper polices are used as guidelines for understanding database standards. However, it necessitates some skills, time and efforts to enforce these polices. You need to go with a fine toothed comb to enforce these policies. Policy-based management system helps you define these polices and ensure that they will be enforced appropriately.
Reference : Pinal Dave (https://blog.sqlauthority.com), DNS
I have long time been a fan of your site and the great simple information you provide. This is a good article to help me start to understand this new feature.
I am interested if you have an article with perhaps a collection of best policies to apply and monitor? I also now help support about 200 servers so centrally monitoring would be great. Outside of email, is there a way you suggest for a DBA to check so many servers efficiently? thanks in advance!
your example and screen shots dont match
How we can give condition as USP_ so I can check my all User procedure should must start with USP_. when I have created this @name like ‘USP_’ then its also considering ‘uspcheckname’. I can not use Escape here. Can you please suggest.
Hi Vikas, give naming convention like ‘USP[_]%’.
Can we implement a Policy to restrict certain users to access certain no. of rows for example 2000 for all tables in all databases in list of servers?
Please suggest how to do that so that users may not hamper Production Server performance by fetching millions of rows in select statement.