Tag Archives: PBM

Policy-Based Management Myths (2 of 3)

Posted on December 16, 2010 by | Leave a comment

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.

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.

image

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.

image

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.

image

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.

image

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.

image

Come back on Monday to find our last installment of the Policy-Based Management myths.

Related Posts:

Leave a comment

Posted in SQL Server, SQLServerPedia Syndication

Tagged , ,

Policy-Based Management Myths (1 of 3)

Posted on December 14, 2010 by | 1 comment

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.

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.

image2. 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.

image3.   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.

image

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.

image

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.

image

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

1 Comment

Posted in SQL Server

Tagged , ,

I don’t get the Data and Log File Location Policy.

Posted on November 8, 2010 by | Leave a comment

Recently I have been spending some time to evaluate Policy Based Management.  Its a nice tool to monitor SQL Server and to prevent bad things from occurring.  At work we have a nice sized SQL Server farm so I am always interested in using tools that make my life easier.

First thing I noticed about PBM is that there are several policies out of the box that can be imported via SSMS.  The majority of the free policies are several best practices in the SQL Server community.  For example, its well known that you should keep data and transactional logs on separate spindles. Therefore, initially I was excited to see a policy for Data and Log File location that included a check condition for Data and Log Files on Separate Drives until I viewed the details of the check condition.

image

Looking at the image above you will see four conditions. The first condition is obvious and with an initial look I would just run with that condition.  The next three are OR conditions so if any of them are true then the policy succeeds for the database.  I fully understand why we look at the @DataAndLogFilesOnSeperateDrives and the @IsSystemObject is included.  Its valid to exclude system databases because its likely to have some system databases and transactional logs on the C: Drive.

There are two condition that are puzzling to me.  Are they puzzling to you? Why would there be a condition to exempt databases that are less than 5120 MB in size?  Why was the value 5120 selected? Is there some unknown reason why Microsoft didn’t select 5121? Seriously, does 5120 have value or am I missing something?  Why would we also exempt databases that have a status not equal to normal?

Because I am unsure  why @Status and @Size are included in the policy I decided to review the Database Performance  facet attributes just to verify that I understand them correct.  The attributes are shown below.  I still very curious towards why these attributes are included.  I am not saying they are good or bad because honestly I am not sure.

image

Ask the Experts

This week I am going to be attending the PASS Member Summit in Seattle.  There will be a section of the vendor expo for Ask the Experts and that is exactly what I intend to do.  Worst case it will be a good way to start a conversation. I will follow-up here with my findings.

Leave a comment

Posted in SQLServerPedia Syndication

Tagged , ,