I just got done implementing the EPM Framework for a client and thought I’d post a few things here I learned along the way. Nothing earth shattering but stuff worth noting.
SQL Server Version
The SQL Server 2008 instance serving as the EPM Framework hosting instance must be 2008 SP1 CU3 or higher. What is interesting about this is that this worked great prior to SP1 so this one caught be by surprise and I had to scramble to get the instance patched. We went with CU9 as SP2 hadn’t shipped yet. If you are on SP2 you’ll be fine. Just something to remember as you plan the implementation.
Exporting Policies
As will be the case in most environments, I set up a non-prod version first. This is where I built all the policies, tested the powershell script, customized reports, etc. When I got ready to move to production, I had a heck of a time figuring out how to export all the policies. From Object Explorer, you can script a policy, but only one at a time. And there is not an option for policies under Generate Scripts.
I spent a lot of time trying to figure this out and had actually started writing a PowerShell script to export the policies when I decided to try one last time with my buddy Google to see if I could find something. In an obscure MS whitepaper (don’t have the link anymore), I found something referring to Object Explorer Details. I never use the details window so had it hidden. Turn out in Object Explorere Details, you can select multiple policies and export them all. Which is good because I had 51 of them to export / move to the production instance.
PowerShell Script Timeouts
The other issue I ran into is that after the job had been running for a few weeks, it started failing with a timeout that was hard to track down. At first I thought this was a timeout related to connecting to one of the instances we were connecting to, so I started painstakingly removing them one by one to try and identify it. Finally got down to just one server and still had a timeout. Changed to a different one, same results. On digging deeper, I discovered it was this line in the script that was timing out.
Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Database $HistoryDatabase -Query “exec policy.epm_LoadPolicyHistoryDetail” -ErrorAction Stop
I played with the -querytimeout parameter for a while with not much luck. So I finally commented out that line and put the exec statment for the stored proc in a Job Step. Step 1 executes the PowerShell script. Step 2 runs the proc. The job is working great now.
