cpyne wrote Jan 6 at 5:12 PM

Thanks. I wish I could just exclude these 2000 servers, but unfortunately I'm stuck supporting them for the near future using these policies.

One clarification on the 2000 bug is this was never used to happen prior to SP2, (notice someone else mentioned this in the comments in that connect link). When we applied SP2 to our CMS, we suddenly couldn't evaluate 2000 servers. The only fix I could find was to remove SP2, but this is only a short term solution since SP1 is out of support.

The R2 server I was testing on is 2008 R2 SP1. If you didn't hit this error with an R2 RTM server, then perhaps the problem was introduced in R2 SP1 as well.

sqlchicken wrote Jan 6 at 4:08 PM

Thanks for update! In regards to your 2000 problem, EPMFramework DOES work on down-level systems but the server that is running EPMF/CMS MUST be SQL Server 2008 SP1 + CU3 or higher (I confirmed in a blog post that 2008 R2 RTM works as well).

In the meantime you may want to add new Server Restriction conditions to your policies. Your condition will use facet of Server and your field will be @VersionMajor, operator >= and Value of 9. This will ensure your policies only evaluate against SQL Server 2005 (Version 9.x) or higher. Also FYI, some of the Microsoft policies that come in best practices bundle don't seem to like 2000 either even though some of the policies aren't evaluating against a specific feature. What I did to get around that was to recreate the policy manually and left off the server restriction condition used by the MS policy. For whatever reason that worked for me, YMMV.

cpyne wrote Jan 6 at 3:19 PM

Sorry for the late reply. I'm pretty sure this is mix of two issues...

1. The duplicate entry after evaluation errors problem....
http://epmframework.codeplex.com/discussions/229052

2. The SQL 2000 failure problem. This happens in 2008 R2 or 2008 R1 w/ SP2
http://connect.microsoft.com/SQLServer/feedback/details/637291/policy-management-cannot-evaluate-policies-that-use-executesql-against-sql-server-2000

We still have a large number of 2000 boxes in our environment, (80 of them). Issue #2 triggered a large number of evaluation errors and then issue #1 creates a duplicate each time and increases the output file size exponentially.

I have a ticket open with Microsoft on #2 to confirm exactly what versions of 2008 R1 and R2 this is going to be fixed in. I'll update the discussion when I hear more info

sqlchicken wrote Dec 28 2011 at 10:15 PM

I'm looking through the PowerShell code and can't find anything that jumps out at me as to what would be causing this issue. The only thing I could possibly think of is that the internal mechanics of CMS changed between versions (i.e. table names) which may have potentially caused some sort of crazy loop that's causing script to evaluate policies multiple times against servers.

I compared your outputs and not seeing anything jump out at me either. Has your CMS stayed same version throughout? Is the server you're running EPMF on the CMS itself or is it a standalone instance?

The only other thing I can think of is the way EPMF encodes the results. Before it writes results to XML file, it encodes it using UTF8 format. Not sure if there's some funky difference between the two instances of SQL Server that make a difference in that respect but just pointing out small differences I'm seeing.