Q&A with Adam Saxton
Inside CSS: Tricks for Troubleshooting SSRS
Adam Saxton loves a good case. And as a Microsoft CSS Senior Support Escalation Engineer focusing on SQL Server Reporting Services, he uses his finely honed investigative skills and arsenal of troubleshooting tools to help customers through their thorniest reporting problems. Now you can know what he knows.
In his demo-heavy Nov. 2 PASS Summit Unite 2009 pre-conference seminar, “Tackling Top Reporting Services Issues,” Adam shares his secrets for troubleshooting everything from configuration and performance problems to rendering and pagination culprits to SharePoint integration and Kerberos security issues. Here’s a preview of what you can expect.
Q: It seems people always have pagination and report layout questions. What generates the most Reporting Services-related problem reports? What are the most common issues you see?
A: Processing and rendering are the big areas. But pagination is always complicated because of the number of options available to the Report Designer. We get a lot of calls asking, “Why do I have a blank page when I export to X.” Since SQL Server 2008 shipped, we’ve started seeing questions related to the differences between the soft page break renders vs. hard page break renders, which tie back into the pagination issues. That and SharePoint configuration and setup are probably the most consistent issues I see. However, we get lots of issues in the processing and rendering areas related to parameters, subreports within Tablix controls, and so on. It’s always amazing to see what people can do with reports.
Q: Speaking of SharePoint integration, what are people doing with SharePoint and Reporting Services?
A: I’m seeing a lot more integration scenarios with SharePoint. Dashboards seem to be the new thing, and reports help facilitate that. But there are definitely some issues you may encounter going down that road. Performance is one that comes to mind right away, depending on the setup. Most of the calls we get deal with getting the integration piece configured correctly. SharePoint integration can be challenging, especially as you get into the more distributed environments. I also see a lot of cases where people want to expose SharePoint with Reporting Services to the Internet as well as to the intranet, and you need to watch for some things that could cause problems when you do that.
Q: What issues do you continue to see with Kerberos? What are you recommending to customers in that area?
A: Once you get your head wrapped around the Kerberos area, it’s really not that bad. The problem with Kerberos is that there’s a pretty big checklist you have to go through, and keeping track of what goes where can be difficult, especially in larger scale-out environments. I consistently see issues related to service principal names (SPNs), though—what ones to have and where they go. Nine out of 10 times, the problem will be SPN-related.
We’ve been trying to get some whitepapers out on how to troubleshoot Kerberos with Reporting Services, but it’s not as easy as you might think. First, there are all the conditions involved, and it’s hard to cover how to troubleshoot the problem without going into how the technology really works, which can get very long. The tact I’m taking now is to write a whitepaper that tackles the most common issues we face. If it can help prevent 80% of the calls to CSS, then I’ll gladly handle the other 20%. We’re also always looking for ways to improve the Kerberos troubleshooting process with customers. We’re definitely aware that this can be a painful experience.
Q: One of the most interesting elements of your Reporting Services seminar sounds like the troubleshooting tricks you provide. Can you give us a sneak preview of one or two quick ones?
A: We have created some internal tools to assist with troubleshooting issues. One that I use a lot is called Diag.aspx (we also have a SharePoint version of this). Diag.aspx lets us see environment information when browsing to a website without having to get a dump or go through logs, which may or may not indicate at the time we want to see. We use another SharePoint tool that helps with setup and can be useful if URLs were messed up. I’ll be demonstrating both of these during the seminar. Of course, we also have tips for using our standard tools for diagnosing data—such as network traces and memory dumps or just looking through the RS Logs for useful information.
Q: Do you work exclusively with Reporting Services or other SQL Server components as well?
A: I’m with our SQL Server Developer Escalation group, which owns Reporting Services, connectivity, client APIs (ODBC, OLE DB, SqlClient, JDBC, and so on), XML, LINQ, and Entity Framework—just to name a few. We don’t typically deal with Engine-related cases or Analysis Services. However, by nature of where I sit, I tend to deal with more and more Engine-related issues. And I’ll help out with anything where I can.
I’ve been with CSS in the SQL Server group since December 2005. And I’ve been working with Reporting Services my entire time in the SQL group, starting with Reporting Services 2000. I also did Windows Consumer support from 1996 until 2001. In between those times, I focused primarily on Web application development and interacting with SQL Server—namely with the .NET platform and C#. As our Reporting Services volume picked up, I shifted more and more to those cases. If you look at the cases on my plate today, they will pretty much all be Reporting Services with the occasional JDBC case thrown in. What I like about Reporting Services, though, is that it covers everything—connectivity, .NET Web applications, networking, native code, performance, Windows internals… You definitely can’t get bored with the types of issues you see in Reporting Services. And if you can handle a Reporting Services issue, you’re usually in a good position to handle any product from a troubleshooting perspective.
Q: What’s the relationship between CSS and the Microsoft Development team? When should a customer contact one team or the other?
A: CSS has a great relationship with the Development team. I’m on weekly calls with the Product team and know pretty much all the members on the Dev team, and they either know me or have heard of me. Customers can always call CSS with issues, and we can provide assistance if it’s something we’re already aware of or try to find the solution. I can usually help with guidance—for example, I can determine whether there’s a workaround we can implement or whether we need to get a fix for the customer. If customers just want to file a bug and not really spend time diving into the problem, they are always welcome to call CSS. But they can also file such items on our Connect site, and it will go directly to the Product team. CSS can also file the issue with the Product team directly, but I tend to shy away from that. I would say that CSS has daily interactions with the Dev team in one form or another, with the minimum of weekly formal engagements. I always have the ability to just pick up the phone and call someone if I need to. I try not to abuse that privilege, though.
Q: What is the best way for customers to report an issue they might be having?
A: There are three ways customers can report issues: via our forums, through our Connect site, and by opening a case with CSS. The Microsoft forums provide a great resource to the Dev team, CSS, MVPs, and the community. They are great places to go if you’re not in a rush and just want to start with some general questions to see if anyone has hit something similar.
Our Connect site at http://connect.microsoft.com/sql lets customers search for issues, vote on items, and submit their own items. The items go directly into the Dev team’s issue-tracking system, where team members can triage it. You’ll see comments from the Product team, and others in the community can vote on these items as well. Voting is very important. When it comes time for major product releases or service packs, we try to get some of the high vote items into the product if we can. Customer votes definitely carry a lot of weight, which is why I encourage customers to use the Connect site if they want to file something for a future release and aren’t looking for a fix right away.
Opening a case with CSS is probably the most involved and detailed way to report an issue. We can troubleshoot the issue with you, help provide workarounds, and facilitate the process of getting a fix for a bug if it’s warranted. We can engage the Product team if necessary, although that isn’t required for most issues. We typically engage the Dev team if we need to verify a bug that we’ve already narrowed down, if we need a fix, or if we just have a question that we can’t find anything on and need an official comment from the Dev team. Again, this doesn’t happen for most of the issues we deal with.
When it comes to troubleshooting, the Dev team will often redirect people to CSS because that’s what we do. From a case perspective, a case starts with the first email (if it’s a web request) or the first call we receive. We want to understand the issue and the circumstances that the customer hit. We will collect data needed to understand what’s going on and may go so far as to set up a reproduction environment internally. The more the customer can explain the issue and provide the data we need, the better and quicker the case will usually go.
Q: Do you have any other tips that can help customers get the answers or solutions they need from CSS better and faster?
A: When you engage us, there are a few things I’m going to ask right off the bat. What is your environment—OS and RS versions, URLs, IP addresses, etc.—as it pertains to the issue? What is the error you are getting or the situation you are encountering (be as detailed as possible)? How do you reproduce it? Do you have a portable repro? The more information we can get upfront about the issue, the better the troubleshooting process will go. One thing I always encourage customers to do is ask if they don’t understand why we’re collecting something or feel that it isn’t relevant. We should be able to explain why we’re collecting the data or information we’re asking for and what we’re looking for.
Q: What do you like most about your job? Can you give us an idea of what a typical day is like for you?
A: I love working with people to fix issues. I also like understanding what caused the problem. My typical day has changed a lot in the past 12 months. A year ago, I was primarily working on customer issues and cases, and that was about all I did. I still do that, but I also work a lot on what we call supportability: trying to identify trends and working with the Product team to get these issues addressed through documentation, a change in the product, or the addition of a feature that doesn’t exist yet. So we work with the Dev team in suggesting new items for future releases such as SQL Server 2008 R2. Another thing that falls under supportability is reviewing the items that are being worked on in upcoming releases. I spent a lot of time going through the new items coming in R2 and working with the Dev team to improve the experience with these features. I really love working on items like that because I feel I’m directly contributing to the product itself. But I also look forward to a good case to work on.
Q: Any memorable Reporting Services issue that sticks with you as interesting or that was particularly valuable to Microsoft in identifying a needed improvement or bug?
A: I worked on a request for 508 Compliancy (accessibility) changes within Reporting Services 2008. I thought it was valuable for the product and Microsoft as a company because it made the product more accessible to visually impaired individuals. I’m not going to say that RS is fully compliant, and we call out exceptions in our VPAC documents. But it was a really good experience in terms of a customer request that we worked on with the Product team, and we were able to address some specific needs. It really stood out to me because it wasn’t a case of numbers from a volume perspective. It was just the right thing to do, which made me really proud to be involved in that effort.
Adam Saxton is a Senior Support Escalation Engineer based at the Microsoft Customer Service and Support site in Irving, Texas, and has worked with the SQL Server group since 2005. His primary focus is connectivity issues to SQL Server and Reporting Services. He has been in the computer industry for 12+ years, which included Windows Platform support and Web and database development. He’s also a regular contributor to the PASS SQL Blog.
Hear more from Adam and other SQL Server and BI experts at PASS Summit Unite 2009 in Seattle, WA. See the lineup of pre/post-conference seminars, review the full conference agenda, and register today.