Share via

Using PBM Against SQL2K and SQL2K5

  • Article

We get this question a lot: can I use PBM against a SQL2K & SQL2K5 instances. The quick answer is yes but in a limited fashion. PBM is ultimately based on SMO (SQL Server Management Objects) and SMO supports SQL2K, SQL2K5, and SQL2K8. PBM relies on some changes to the DB engine which are not available in versions below SQL2K8, therefore, not all PBM functionality  is available in SQL2K and SQL2K5.

Let's divide the PBM functionality into four key areas:

  1. Authoring policies
  2. Storing policies on a server
  3. Automated policy evaluation
  4. Manual policy evaluation

Authoring Policies

There are two ways to author a policy: connected and disconnect. Connected policy authoring is only supported in SQL2K8. When authoring in connected mode (Object Explorer is connected to an instance) the policy is stored on the server. There is no way to alternatively save the policy to the file system (other than exporting it after it's created on the server). Disconnected authoring saves the policy to the file system (as an XML file). This functionality is only available in SQL2K8 Management Studio. Since it doesn't require a connection to a back-end server you don't need an instance of SQL2K8 available. Therefore, authoring policies is somewhat dependent upon SQL2K8 - you need the toolset but not a server.

Storing Policies On A Server

There are two ways to store a policy on a server: create it on the server and import a policy XML file to the server. Both of these are only available on SQL2K8 servers.

Automated Policy Evaluation

Policy automation is dependent upon enhancements to dependent features only available in SQL2K8 (SQCLR, SMO, Agent and DDL Eventing.) Therefore, automated policy evaluation (Check on Change - Prevent, Check on Change - Log, and Check on Schedule) is only available on SQL2K8 servers. In addition, the automation requires the policy to stored locally on the server which is only supported on SQL2K8.

Manual Policy Evaluation

Manually evaluating a policy is a client-side operation (simply meaning it doesn't run in the context of the DB Engine service) and therefore it requires SQL2K8 Management Studio (or at least the SQL2K8 management stack - PBM & SMO APIs). There are four ways to manually evaluate a policy:

  1. From Object Explorer in Management Studio
  2. From Registered Servers in Management Studio
  3. The PowerShell cmdlet Invoke-PolicyEvaluation
  4. From the PBM API

Object Explorer (OE): Policy evaluation is supported from OE when connected to SQL2K5 and SQL2K8 servers (note: this may also be available when connected to a SQL2K server but I didn't have one available to me at the time of this writing to verify). You get the same Evaluation dialog in both cases. However, when connected to a SQL2K5 server there are no locally stored policies which means you need to select policies from file or from a SQL2K8 server.

Registered Servers (RegSrvrs): Policy evaluation is supported from RegSrvrs for SQL2K, SQL2K5, and SQL2K8 servers. You can evaluate policies that are saved to file or stored on a SQL2K8 server. You can also evaluate policies against a group of servers. The server group can contain mixed versions.

Invoke-PolicyEvaluation: This PowerShell cmdlet can be run against SQL2K, SQL2K5, and SQL2K8 servers. You just pass in the server name to the TargetServerName parameter. You can evaluate policies stored on a SQL2K8 server or on the file system. This blog post shows how to use the Agent PowerShell subsystem in SQL2K8 to create a job that runs a PowerShell script to evaluate policies against a group of servers. These servers can be SQL2K, SQL2K5 and SQL2K8 servers.

PBM API: The PBM API is a public API and contains methods for evaluating policies. This is exactly what we use under the covers for our policy evaluation. You can create your own .Net application which calls this API. Similar to the PowerShell cmdlet you need to pass in a connection to the server to evaluate. This connection can be a SQL2K, SQL2K5, or SQL2K8 connection. The details for how to do this (the code you need to write) is beyond the scope of this posting but is a good topic for a future posting.

Summary

When we designed PBM we accounted for the fact that not all customers immediately upgrade their environment - you have to deal with mixed environments. We wanted to be sure PBM would add value to these mixed environments but we also need to give you a reason to upgrade :-). Therefore, we enable you to run policies against SQL2K and SQL2K5 just not with the same fidelity as SQL2K8.

Comments

  • Anonymous
    July 04, 2008
    PingBack from http://blog.a-foton.ru/2008/07/using-pbm-against-sql2k-and-sql2k5/

  • Anonymous
    July 16, 2009
    When connecting to the central management server and evaluating polices against 2000/2005 instances; i.e. Database Auto Shrink (from best practices), the evaluation fails on a 2000/2005 instance when I select the central management server as the source for the poilcy: Operation not support on SQL 2005. However when I select the XML policy file itself as the source, the policy evaluates just fine. Any ideas? Thanks, Tommy