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 second myth.
- 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
By default a policy using the database facets will run against all databases
The myth is true and false. Actually, using the default database target condition (every database) the myth is true. If you build your own condition for the target then the myth can be false.
Lets take another look at the “Auto Shrink Database” policy that comes out of the box with SQL Server 2008. When you evaluate the policy you will see that the system databases (msdb, master, model, tempdb) are not included.
My first thought is to checkout the target properties. Maybe for some reason the system databases are exempt. Looking at the target below you will see that every database is suppose to be included. Obviously, this means all databases are included, right? No, sorry system databases are not included.
How do I evaluate policies against all databases?
Lets create a new condition that we will use as our target condition. In a nutshell we need a condition that shows all databases when they are system objects or are not system objects. If you select the database facet you will notice there is an IsSystemObject field. This is our ticket getting all databases.
Lets now change the target condition to use the condition created above. We will no longer have every databases as our target condition and select our new System and User Databases target condition.
Now lets evaluate the policy again. You will now see all user databases and system databases as I expected when you used the default target condition.
Come back on Monday to find our last installment of the Policy-Based Management myths.