SSRS Performance and scaling

Now that we have dove deeper and deeper into the BI world, one of the Technical things that needs to be understood by someone who would be implementing and administarting a companies BI solution, is proper planning of hardware, and also scaling-out for performance.

That means, that someone would need the ability to understand SSRS, how to scale, when to scale, differences in version between SQL Server 2005 and 2008 for SSRS, etc.

So lets start with an older article from MS TechNet. Planning for Scalability and Performance with Reporting Services

This article was wrote in 2005, focusing on SSRS for SQL Server 2005. Still the knowledge that can be gained from this article is a good bit, and a great place to start.

This document covers scaling up vs. scaling out, the technologies used with SSRS and getting into specifics.



Like some of the basics for SSRS performance would be spliting out the Report Catalog (SQL Serve Databases) from the Report Server (IIS and SSRS Service). That would be an example actually of a scale out.

More true to the term of a scale out would be though, taking the Report Server, and having a few different boxes that run the Report Server role, and load balance them for execution. These all would point to the same Report Catalog, and therefore could connect to Dynamics AX.

You can see this from the following:
"The remote configuration eliminates contention for machine resources between the Report Server and the SQL Server hosting the catalog. However, you must provide adequate network bandwidth between the Report Server and the catalog server.

Scale-Up and Scale-Out

After you split the catalog to another system, you can choose to either scale up the Report Server, by adding processors, or scale out by adding machines. Figure 4 illustrates a scale-out configuration that uses multiple Report Servers to access a single catalog."


After you get a grasp at this level, the next step would be to go a little further with the when to scale out, and there is another article found here: Reporting Services Scale-Out Architecture



That one goes into more detail about the scale out architecture, and also covers the max number of concurrent users SSRS can handle, and how this increases when your scale out.

After getting this under your belt, the next level would be to understand the comparison between SQL Server 2005 and SQL Server 2008 SSRS. For that, a very detailed analysis was done over at SQLCat.com. The article for that can be found here: Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned

From the post:
"Http Errors vs. Request Time-Out Errors

To better understand the hardware limits we hit with Reporting Services 2008 compared to Reporting Services 2005, we compared the HTTP 503 errors we saw with Reporting Services 2005 with the Reporting Services time-out errors that we saw with Reporting Services 2008. We discovered that while the failure rate for reports with Reporting Services 2005 was directly correlated with HTTP 503 errors, the failure rate for reports with Reporting Services 2008 was directly correlated with time-out errors. These time-out errors, combined with our analysis of the disk queuing on the data source, indicate that the data source was unable to keep up with the request for data from the front-end servers."


So This is all very important to understand, or have someone udnerstand who will be helping you get the best out of your SSRS part of your overall Dynamics AX 2009 solution.

Well check back soon, performance tunning Dynamics AX 2009 will be getting a lot of focus from my post. I will also be adding another Dynamics AX BI pratical post, focusing this time on making use of OLAP Cubes, and creating a custom cube for use within your Dynamics AX 2009 instance.

See you then!




"Visit the Dynamics AX Community Page today!"