Inconsistent Results on Policy Evaluations

Dec 6, 2011 at 7:51 PM

I have completed the installation and configuration of the EPM Framework and am noticing some inconsistencies in the evaluation of the policies.  I have a SQL 2008 standard edition named instance called DEV01 (development #1).  Within this instance I have two databases: DBA and SDE_DEV2 which are configured as follows:

DBA

Data: D:\MSSQL10.DEV01\MSSQL\DATA\DBA.mdf

Log: D:\MSSQL10.DEV01\MSSQL\DATA\DBA_log.ldf

SDE_DEV2

Data: D:\MSSQL10.DEV01\MSSQL\DATA\SDE_DEV2.MDF

Log: D:\MSSQL10.DEV01\MSSQL\DATA\SDE_DEV2.LDF

As you can see, these databases are configured virtually the same.  I was quite surprised when the dashboard for EPM showed that DBA passes the "Data and Log File Location" policy while SDE_DEV2 fails.  I can't include a screenshot but here's the same information directly from the database.

 

select evaluationDateTime, EvaluatedPolicy, Evaluatedobject, PolicyResult
from policy.PolicyHistoryDetail
where EvaluatedPolicy='Data and Log File Location'
  and EvaluatedServer='VMDEV-APP2\DEV01';

evaluationDateTime       EvaluatedPolicy               Evaluatedobject                                     PolicyResult
2011-12-06 09:28:28.160  Data and Log File Location    SQLSERVER:\SQL\VMDEV-APP2\DEV01\Databases\DBA       PASS
2011-12-06 09:28:28.160  Data and Log File Location    SQLSERVER:\SQL\VMDEV-APP2\DEV01\Databases\SDE_DEV2  FAIL

Can anyone explain why I'm getting different results between these two DBs?  Can anyone explain how to trace through and debug to find the cause?

Ken

Coordinator
Dec 6, 2011 at 9:09 PM

Manually evaluate those policies against those databases and see if you still get the same issue. Curious to see if this is bug with policy or EPMF.

Dec 8, 2011 at 9:23 PM

Thanks for the hint.  I ran a manual evaluation as recommended and have determined the difference.  If you're interested, I found that the MS policy evaluates the following conditions:

1) DataAndLogFilesOnSeperateLogicalVolumes=TRUE

OR 2) Size<5120

OR 3) Status != Normal

OR 4) IsSystemObject=True

Both of my databases fail test 1) as they have data and log files on the same drive.  They both fail test 3) as they are "normal" DBs and both fail test 4) and they are not system objects. 

My "DBA" database is only 17MB (as I'm just getting started with EPM) so it gets a TRUE for test 2) where my SDE_DEV2 database is ~7G which means it fails test 2).  Since the conditions are OR'd together, the small DBA database is considered a pass where the large DB fails.  I assume that Microsoft only requires separate data and log files if the database is over 5G.