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