Tag: T-SQL Tuesday #005

Introduction

Today I am taking a little detour from the scripts I have been posting to take part in “T-SQL Tuesday #005 – Reporting“.

My T-SQL Tuesday #005 entry allows selected users of a server to see what is going on with it and react accordingly, freeing members of the DBA team from constant questions about why the server is running slow. Admittedly it does often just lead to more informed complaints but at least the time to resolution is quicker.

The Reports

I am a sucker for free stuff so I was extremely happy when I discovered the SQL Server 2005 Performance Dashboard Reports. The reports are a package of Reporting Services reports that put a nice user interface over all of the DMVs that were new in SQL 2005. The Performance Dashboard allows you to see CPU History, Waits, Active Sessions, Waits By CPU, IO etc. They are a powerful tool that will quickly guide users directly to whatever issue it is they are looking to solve.

The one drawback to the the Performance Dashboard Reports is that they run as a custom report in Management Studio so if you wanted to distribute them to non-administrators then a whole bunch of people are going to get way more access than you would ever want them to have. To solve this problem I converted the reports to reporting services.

The change to reporting services was really quite straightforward. The big problem was sorting out the connection strings. I ended up passing an extra parameter, ServerName, to each of the reports then using that server name to put together a connection string for that server. The beauty of this approach is that it allows me to create a linked report for every server I want people to see. To keep it user friendly the linked report will call the reports for the appropriate server using the correct server name. Here is how the connection string looks:

What About Security?

If you are anything like me you are shaking your head and thinking this is a security nightmare. Well it could be if not implemented correctly but properly thought through is reasonably safe.

Security works in layers here. First of all I made sure the reports are running under a specific account that is unique to them. This account only has the exact rights needed to get the job done.

The next layer of security is access to the linked reports themselves. Not everyone gets access to the linked reports. Only people in certain groups or roles should even be considered for access to these reports. By no means would I recommend leaving the reports open to your entire organization.

Lastly, the catch-all for security is that I have added a call to “How Can I Tell if a Windows Login has Rights to My Server?“, set to raise an exception if the report requestor does not have access to that server, to every report in the package. Even if I screwed up somewhere and left a big hole that someone could exploit, short of letting anyone edit the reports, they will not be able to see any pertinent information because the first thing they see will be an exception. I also include the group that is allowing the user to see the report, allowing me to quickly see where any surprises are coming from if I get emailed a screenshot of a report from someone unexpected.

Conclusion

So there you have it, my entry for T-SQL Tuesday #005. I hope to go into these reports further in future posts, sharing some of the modifications I have made. Some of the changes include bug fixes and changes to make the reports keep working in SQL 2008, but others are new reports. One of the more useful new reports looks at executions per second to identify logic bugs and caching opportunities.

Apologies for not including lots of fancy screenshots, but I am unable to show pictures of my work without showing pictures of the work of others. After including callouts to show what is mine and what is not the pictures just would not be fancy anymore.