Transmenu powered by JoomlArt.com - Mambo Joomla Professional Templates Club

To set the scene for this post, try and put yourself in the position of today’s database administrator. Being the person inherently responsible for those servers and databases that are efficient, secure, organised, compliant and in some cases, policed, is by no means an easy feat.

At the moment, we more than likely have some sort of policy that governs what can actually happen on our SQL Servers or even within our databases. However, in a lot of cases implementation of these policies is reactive rather than proactive. Furthermore, problems of inconsistency between SQL Servers, not to mention unnecessary administrative overheads, are further amplified as we have no refined means to enforce rules. This is where Policy Based Management (PBM) or Declarative Management Framework (as it’s otherwise known in CTP’s) comes in.

PBM allows us to control a group of SQL Server instances from a central location. For the first time, we now have an easy-to-use method for controlling security, compliance, database options and even naming conventions for database objects with precision. In concept, it is very similar to group policy.

Policies are made up of management facets and conditions.

A management facet can be defined as a manageable area/component of SQL Server, examples of which include a database, an endpoint, an index and even a backup device. These predefined facets are available to us from within the management studio as shown in the accompanying diagram.

Conditions are used to perform tests against facet properties. For example, you may want to implement best practice in regards to data and log file placement for all those SQL Server instances that are running SQL Server Standard or Enterprise editions.

This would require us to create two conditions; one to test the placement of file location and the other to test the edition of SQL Server. This means that we would also have to reference two different facets, with one condition relating to the database, while the other relates to the server.

Creating the first condition, as seen in the following diagram, requires us to specify the “Database Performance” facet, as it contains a property called, “@DataAndLogFilesOnSeperateLogicalVolumes”. This property would have to evaluate to “true” for a database to be classified as complying with our best practice policy.

The second condition would be used to identify the edition of SQL Server as seen below. We want to ensure that all instances of SQL Server are running either Standard or Enterprise (or Developer) editions. For this, we need to query the value of the “@EngineEdition” property. We would need to use two expressions with an “OR” operator to properly comply with our policy. Be mindful not to use the “AND” operator in examples such as these as no single instance of SQL Server can be both Standard AND Enterprise.

Once our conditions have been created, the next step is to bring it all together in a policy. In the “Create New Policy” dialog, we are prompted to name the policy and indicate which condition is going to be used against our targets. As seen below, our condition, which will check for the data and log file placement, is targeting every database on the SQL Server instance(s). Our second condition comes into play at the bottom, where we use it in the server restriction drop down list.

Once the policy is in place, you can then evaluate Your SQL Server instances (individually or as a group) against that policy. There are four evaluation modes available to us. We have:

• On Demand – Manual evaluation when you directly execute the policy.
• On Change: Prevent – Utilises the DDL triggers that were first introduced in SQL Server 2005. Automatically rolls back an action if it violates anything in our policy.
• On Change: Log Only – Utilises the Event Notifications that were first introduced in SQL Server 2005. Automatically logs policy violations in the event log.
• On Schedule - Uses SQL Server Agent jobs to periodically evaluate policies. Logs policy violations in the event logs and generates a report.

The following diagram displays the result of evaluating our policy. Of the two databases that are sitting on my development box, only one of them (“PolicyDemo”) actually meets the policy requirements. The “AdminCentre” database violates the policy as both data and log files sit on the “D:” drive.

In short, Policy Based Management is an ideal feature to have for any Database Administrator that values their time. It is very flexible and robust in that almost any facet of SQL Server can be managed.

You may have also noticed that SQL Server 2008 no longer ships with the Surface Area Configuration tool. Well in actual fact, what we could do with that tool in SQL Server 2005 you can still do in SQL Server 2008…