When I first got my hands on SQL Server 2008, Policy Based management was once of the features I was keen to make use of. Especially as they could be evaluated against older versions which made up most of my estate. However, I soon learned that it was instance based unless being evaluated manually or via powershell script. At the time, powershell scared me and I was looking for something automated to measure compliance levels.
I eventually discovered the Enterprise Policy Management Framework. A free to use solution for evaluating SQL policy's across an enterprise and audit compliance. It consisted of a simple database, a powershell script to perform the evaluations, and some SSRS reports to show the compliance. The script made use of a central management server (which fitted in perfectly with me.) and was able to filter by policy category.
I used it for about 3 years across 2 employments and only ever made minor adjustments to the reporting layouts. My biggest issues with it where that I wanted a daily audit of a few policy's, and the amount of space take by my small estate was outside of my management data storage quota. To get by, I had to only keep a short amount of history. I realize I could have build a rolling summary to keep a basic view of compliance.
At my next employment, my estate had more than tripled in size. Also, historical reporting was much more important. Rather than implement the solution again, I decided to re-invent it for my needs.
I first created a powershell script to run as an evaluation engine. I again opted for a method that used a central management server, but built policy scheduling into the data model rather than filter by category.
I now have data for 25 policys mostly evaluated daily against 124 instances going back over 3 years in less than 300MB.
No comments:
Post a Comment