OK so the last time I handed out a SQL Bacon Nugget of Excellence Award was July 8th for @SQLAgentMan ‘s article on Determining Free Space, Consumed Space, and Total Space Allocated for Microsoft SQL Server Databases
and this time around @SQLGal has put something together that can only be described as a SQL Bacon Nugget of Excellence. So a little background story on this one… I’ve been doing more with Policy Based Management lately and coincidently more with PowerShell as well. So I started playing around with having an automated policy checker run in the night via PowerShell and email me the “non conformists” 🙂
I was almost done my initial release when I all of a sudden found that if I ran the powershell script from the server directly that I couldn’t connect to some of my servers (SQL 2000). I re-ran… nothing… tried a few different ideas… no dice. So as I like to do from time to time if my “googling” doesn’t help much is I posed the question to the Twitterverse.
As always (cause Twitter is just great for community support) I got a couple replies and offers of assistance. One reply was from @Timbo_B_Edwards. His tweet pointed me to a site that suprisingly (with my googling on Policy Based Management and Powershell) I didn’t find before.
This offering from @SQLGal puts my ragamuffin first attempt at Policy Based Management via Powershell to shame.
The Enterprise Policy Managment Framework uses PowerShell to evaluate policies against your SQL Server Environment, shreds the XML for you, and then renders and delivers your policy history reports via Reporting Services.
So for the love of all things good and pure in this world… if you have SQL Server 2008 (and if you don’t this is one heck of a good reason to start) this is something you HAVE TO check out and spend some quality time with.
P.S. I found the solution to my problem on the top of the Enterprise Policy Management Framework page…
“Update on SP1: You must install SQL Server 2008 SP1 Cumulative Update 3 (http://support.microsoft.com/kb/971491) if you have installed SP1 on the Central Management Server. SP1 Cumulative Update 3 will fix the SP1 bug which prevented down version policy evaluation.”