TSQL Tuesday #005: Reports and Policy-Based Management

It’s another month and so we have ourselves another T-SQL Tuesday event, this time hosted by Aaron Nelson (Blog | Twitter) with this month’s theme being Reporting. If you don’t know what T-SQL Tuesday is here’s a quick blurb from Aaron’s blog:

You Don’t Know What T-SQL Tuesday Is Yet?

Adam Machanic (Also on twitter as @AdamMachanic) had a great idea 5 months back – Invite new and existing SQL Server bloggers to post about the same topic on the same day. The results have been excellent – diverse skill sets and data related job roles all posting from differing perspectives on the same issue.

Since this is still early in the game, a quick list of the post roundups from the first 4 topics:

• #001 Adam Machanic started this off with a topic of Date/Time tricks
• #002 Adam then wanted to hear about your Puzzling Situations
• #003 Rob Farley got romantic on us and asked about Relationships
• #004 Mike Walsh wanted to glean the ins and outs of IO

So given that this month we (Ken Simmons, Colin Stasiuk and myself) are proud to be releasing Pro SQL Server 2008 Policy-Based Management I figured this month’s TSQL Tuesday could tie in nicely. So how can one use Reporting Services in conjunction with the power of Policy-Based Management (PBM) in SQL 2008? Well, you’re in luck!

The folks who brought you PBM were also kind enough to create an open-source project called the Enterprise Policy Management Framework, or EPMF for short. This project utilizes the power of PowerShell, Policy-Based Management and Reporting Services to create a reporting dashboard that allows you to not only evaluate your custom policies against down-level servers (SQL Server 2000 or higher only) but it also gives you really nice drill-down reports that allow you to see granular-level view of your policy evaluation results.

I won’t go into detail here about how to install and use EPMF as that is all covered at the Codeplex page as well as in our book. Just a heads up, however, here are the requirements you’ll need in order to get this to work properly against your environment.

• SQL Server 2008 with SP1 Cumulative update 3 or higher (this is needed in order for EPMF to evaluate properly against down-level servers). Also need a SQL Server 2008 instance to use as a Central Management Server, which EPMF uses to evaluate multiple servers and groups.
• SQL Server 2008 Reporting Services setup in Native Mode (Note: does not have to be on same server as you can configure which report server to deploy to when you setup EPMF)

If you haven’t tried out Policy-Based Management yet I highly recommend you try it out as it can make your administrative life so much easier. Couple that with EPMF and you have a very versatile and powerful management solution. Some tricks that you can try doing with EPMF is utilizing Reporting Services subscriptions. This isn’t covered in our book or EPMF but since I like you guys I’ll let you in on the secret hehe. In order to utilize Reporting Services subscriptions the report requires that the credentials be embedded within the report. These credentials are the ones that need access against the database you’re reporting from. In this case you’re reporting against the database which EPMF (MDW by default).

To do this we have to open our Reporting Services reports page (Typically this is http://yourservername/reports). Once this loads you will see a folder for Data Sources. Open that and assuming you deployed EPMF properly you will see a data source here called PolicyDW. Click on it to open it up. Next you’re going to modify the credentials for this data source. Click on the radio button for ‘Credentials stored securely in the report server’ and put in the user name and password for an account you want to use. I suggest creating a dedicated Active Directory account with only read privileges against your database, but that’s just me being super-secure and paranoid. If you have an existing AD account you can use it just make sure you lock down proper rights on the SQL Server side. If you use an AD account make sure you tick the box for ‘Use as Windows credentials when connecting to the data source’. Also, if you don’t have the access or resource to create a new AD account for this you can create a new SQL Server login, give that account minimal rights on the database and specify its information here as well. Only difference is do not check that Windows credential account box if you use this method. Once you’re done click the Apply button to apply your new changes.

Now we’re going to setup the report subscription. Click on the Home link in the top left to take you back to the home reports screen. Click on the Policy Reports 30 folder, then click on the PolicyDashboard link to open the dashboard report. Once the report loads you will see a blue tab up top labeled Subscriptions. Click on that link to open up and then click on New Subscription button. This form is fairly straightforward in terms of information to supply (i.e. email information) but what’s really nice is down toward the bottom in the Report Parameter values. Here you’re able to specify when and how often you get this report generated, the groups you want to get automated reports on, the policy categories you want reported as well as reports on policy results (i.e. only send reports for failed policy evaluations). Once you have the report setup the way you want click OK.

So now you have setup an automated report to let you know everything you need to know about you policy evaluations in your environment! I have mine set to deliver to my inbox every Monday morning so that when I come in to the office on Monday morning I sit down and see the health state of my SQL environment in once nice neat report. Best part of subscriptions is that anyone can subscribe to reports so if your boss becomes curious as to what you’re doing, they can subscribe themselves and not have to bother you as they are being fed the same data as you! Have fun.