Policy-Based Management Myths (1 of 3)

A few weekends ago I gave an “Introduction to Policy-Based Management and Central Management System” at SQL Saturday 61 in DC. Do to some great questions I am going to focus on some myths that come up with Policy-based Management this week.  Check back through the week to catch all three myths.  Today we will cover the first of the following three myths.

• Execute and apply policies against SQL Server 2000
• A policy using one of the database facets runs against all databases
• You cannot use Policy based Management with SQL Server 2008 R2 Express

You cannot execute and apply policies against an instance of SQL Server 2000.

The answer is yes and no.  You can execute and apply policies against an instance of SQL Server 2000 if the policy executed runs against features supported in SQL Server 2000. This is easy to do if you have a central management server setup.  I will walk you through the steps below assuming a CMS exists.

1. Right click on the node in your Configuration Management System that hosts your instances of SQL Server 2000 and right click and select evaluate policies as shown below.

2. Click on the ellipse next the source and select the policy files you would like to execute or select a sever where the policies reside.  In this example we will execute the Database Auto Shrink built-in best practice policy.  By default they are located at C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033\. Once you have loaded the Database Auto Shrink policy click execute and the policy will be evaluated against every server in your CMS group.

3.   The following are the results of executing the “Database Auto Shrink” policy against a group of development SQL Server 2000 instances.  You will noticed that the policy failed as there are databases that have auto shrink equals true. We can click on the checkboxes and apply the policy to force the databases to be compliant with our policy.

4. Click yes on the next popup screen will apply the policy to modify all selected targets that do not comply.  Make sure you want to do this before you apply the policy.

5. After selecting apply you should see that you were able to apply the changes to make your SQL Server 2000 databases are compliant with the policy.

Can you show me an example of a policy that will not work on SQL Server 2000?

Database Mirroring is not supported in SQL Server 2000 as it was delivered in SQL Server 2005 SP1.  If you have a policy that checks to see if mirroring is enabled and you run it against a SQL Server 2000 instance the policy will fail.  In fact, you will receive an “Property ‘IsMirroringEnabled’ does not exist error.

Check back during the week to catch the next Policy-Based Management myth.