SQL SERVER – Policy Based Management – Create, Evaluate and Fix Policies

Introduction

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 True or False.

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 True everywhere.

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.

Summary

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.

What is the Difference between a View and a Materialized View?

A view takes the output of a query and makes it appear like a virtual table; and it can be used in place of tables.


A materialized view provides indirect access to table data by storing the results of a query in a separate schema object.

Dictionary in C#, VB.NET with Examples

Dictionary Object

Dictionary is a collection object which is used to store data in key and value pairs. In dictionary objectKey value must be always unique and it won’t accept any null or duplicate values because we use keys to identify values stored in dictionary object but values can be duplicated or we can set null value also.

The Dictionary collection is a part of generic collections so to use Dictionary object in our applications we need to add following namespace in our applications.

C# Code

using System.Collections.Generic;

VB.NET Code

Imports System.Collections.Generic

We will see the syntax of using Dictionary object in our applications.

Syntax of Dictionary Object

Following is the syntax of using Dictionary object in our applications.

Dictionary<TKey,TValue>

The Dictionary class will accept two parameters one is for key and another for value.

TKey – It defines what type of keys which we are going to store.

TValue – It defines what type of values which we are going to store.

Following is the declaration of Dictionary object

Dictionary<int,string>

The dictionary object can be initialized with IDictionary<TKey,TVal> interface or Dictionary<TKey,TVal>class like as shown below

C# Code

IDictionary<int, string> objdict = new Dictionary<int, string>(); //or Dictionary<int, string> objdict = new Dictionary<int, string>();

VB.NET Code

Dim objdict As IDictionary(Of Integer, String) = New Dictionary(Of Integer, String)() 'or Dim objdict As New Dictionary(Of Integer, String)()

It’s always recommended to use IDictionary<TKey,TVal> interface instead of Dictionary<TKey,TVal>class.

We will see how to add elements to Dictionary object and how to access Dictionary object elements with example.

Example of Dictionary Object

Following is the example of using Dictionary object in our applications.

C# Code

using System; using System.Collections.Generic; namespace Linqtutorials { class Program { static void Main(string[] args) { // Adding Elements to Dictionary Object IDictionary<int, string> objdic = new Dictionary<int, string>(); objdic.Add(1, "Suresh Dasari"); objdic.Add(2, "Rohini Alavala"); objdic.Add(3, "Praveen Alavala"); objdic.Add(4, "Sateesh Chandra"); // Read Data from Dictionary Object Console.WriteLine("Number of Users: {0}", objdic.Count); Console.WriteLine("User Details"); foreach (KeyValuePair<int, string> user in objdic) { Console.WriteLine("Key={0}, Value={1}", user.Key, user.Value); } Console.ReadLine(); } } }

VB.NET Code

Module Module1 Sub Main() ' Adding Elements to Dictionary Object Dim objdic As IDictionary(Of Integer, String) = New Dictionary(Of Integer, String)() objdic.Add(1, "Suresh Dasari") objdic.Add(2, "Rohini Alavala") objdic.Add(3, "Praveen Alavala") objdic.Add(4, "Sateesh Chandra") ' Read Data from Dictionary Object Console.WriteLine("Number of Users: {0}", objdic.Count) Console.WriteLine("User Details") For Each user As KeyValuePair(Of Integer, String) In objdic Console.WriteLine("Key={0}, Value={1}", user.Key, user.Value) Next Console.ReadLine() End Sub End Module

If you observe above example we are adding elements to Dictionary object “objdic” and getting elements from Dictionary object “objdic” using “KeyValuePair<TKey, TVal>”. Now we will run and see the output that would be like as shown below.

Output of Dictionary Object Example

Following is the result of Dictionary object example.

In Dictionary object we have different properties and methods available to perform like getting count or elements or check elements in Dictionary object or get particular value from dictionary object with key value, etc.

Dictionary Object Properties

Following are the properties available in dictionary object.

Property	Description
Comparer	It is used to determine equality of keys
Count	It is used to get number of elements exists in Dictionary<TKey,TVal>
Item	It’s used to gets or sets the value associated with the specified key.
Keys	It’s used to get collection containing keys in the Dictionary<TKey,TValue>.
Values	It’s used to get a collection containing the values in the Dictionary<TKey,TValue>.

Dictionary Object Methods

Following are the methods available in dictionary object.

Method	Description
Add(TKey,TVal)	It is used to add specified key and value to the dictionary
Remove(TKey)	Removes the value from Dictionary<TKey, TValue> based on specified key
TryGetValue(TKey, TValue)	It is used to get the value associated with the specified key.
Clear	It removes all keys and values from the Dictionary<TKey, TValue>.
ContainsKey(TKey)	It determines whether the Dictionary<TKey, TValue> contains specified key or not.