As you might imagine, with the scale and diversity of our environment, one of the most valuable things to the success of an Operations team is information. Information on server load, traffic patterns, server inventory, hosting costs, events, alerts, availability and pretty much any other metric you can think of we get constant requests for. Recently, we decided to implement SQL Reporting Services to aid in the creation, deployment, management and presentation of our operational metrics. As you may have noticed from previous blog entries, we are nothing if not honest in our assessments of products and services provided by the great company we work for. This entry is no exception.
On the great side, the development environment for SQL Reporting Services is clean, intuitive and in a word, easy. Even though the development environment is integrated into Visual Studio, it does not require any development skills to create a Buisiness Intelligence project and begin building professional looking reports. If you have ever used the report builder in Access, you will instantly recognize the report builder in Reporting Services. You can select to build your reports via a simple wizard or get down and dirty with a blank canvas adding a host of controls, data grids, sub reports, images, you name it. Once you are satisfied with your report (you can preview it to your hearts content before deployment), you can right click and instantly deploy your report to your reporting environment. If I've made it sound overly simple, it's because it is.
There are quite a few features of Reporting Services that I would consider to be good, but not great. At the top of that list is user documentation. Far be it from me to throw stones when it comes to creating great documentation around tooling, but I was hoping for more in this space. On the bright side, two books exist that fill the gaps nicely. The Rational Guide to: SQL Server Reporting Services is an excellent primer reference to get you started. I read it cover to over the course of a weekend and by the end, I had a firm grasp on features that had been previously unknown to me. The second, more reference style book is SAMS Professional SQL Server Reporting Services. Not exactly a book I would recommend reading cover to cover, but definitely a great reference for more advanced topics. Other features I would rank in the “good“ category include setup and configuration (could be better, but not by a lot), UI for report management (permission setting could be better) and cleaning up after itself during uninstall.
The Not So Great
Okay....let's call this part exactly what it is; ugly. On the surface, one of the coolest features of Reporting Services is the ability to create report subscriptions. However, beauty is truly only skin deep. The physical implementation of this feature initially made me laugh out loud, then made me want to cry. Here's a rundown of how it works. For every subscription (for every user) a SQL Agent job is created on the reporting SQL Server. So if you have 25 reports and 100 users and every user subscribes to every report, you will have 2500 SQL Agent jobs to manage. If that's not bad enough, these Agent jobs are physically named by GUID. No friendly name, no way to associate them (easily) back to the subscription (so if it fails, troubleshooting is difficult at best) and SQL Agent left managing memory for n agent jobs. Now for the really heartbreaking part...if you delete a subscription from the web UI, that agent subscription job does not get cleaned up. *sigh* So much potential.
Overall, SQL Reporting Services is definitely a fantastic product. The positives without question outweigh the negative aspects (unless you truly had your heart set on creating subscriptions). The implementation of Reporting Services in our environment has enabled us to centralize our reporting needs and distribute the development of reports to all of the members of the Operations team freeing up the tools developers to write code.
That's all for tonight