Report Throttling and Asynch Processing

My enterprise is looking into migrating to SSRS 2005 and I am trying to gather as much information as possible. One area that I've been unable to find much information is around the area of process (or report request) throttling inherent to SSRS. We are a large organization and the reporting technology that we choose must be able to process thousands of report requests a day. In our current solution, we have the ability to throttle the report requests so that a limit is placed on the number of report request that can be processed concurrently so that the reporting infrastructure is not overwhelmed during peak load times. Subsequent request are queued for processing at a period when there is less load on the servers. Does SSRS have this capability and is it configurable? Is there any detailed information (articles, whitepapers, books) available addressing this subject?

This leads me to a second question concerning an asynchronous reporting solution. Has anyone developed a solution using SSRS that allows long running reports ( that are requested online) to be processed on a background thread and has the background thread alert the user when the report has completed? This is a nice feature of our current solution but I have not been able to find a comparable solution utilizing SSRS.There looks like there might be possibility in the Subscription capabilities but I’m not sure how this would work online requested reports.

Any help would be appreciated. Thanks!!

You can have users subscribe to reports so they are processed and pushed out during down time - either to email, or say to an ftp server. If you want a queuing feature for on demand though, you would have to front-end that. And you would need a mechanism to determine long running reports.

You can scale the infrastructure of RS and setup farms. Throttling though..not likely.

Yes, in your second question...have the user setup a subscription to the report. Then either email as an attachment or send as a link so they can retrieve it. Again, you might want to front end this on an ASP page so the user makes their report selections and then a few lines of code creates the subscription for them...providing the appearance of a request queue.

|||Thanks David.

I will definitely research these options. On the surface, I don't think that attaching the reports to emails is a valid option for us because of the size of the files. In the situations where you include a link to the report, how is this being secured? Is the link to the Report Manager site with URL access? Or is the report output saved to a share with the link taking the user to a share? If it is persisted to a share, how are these files secured? How are the user and calling application notified of a failure?

Looks like a combination of subscription and custom delivery extension might be the answer.

Thanks for the help and sorry about all the questions.

|||

David,

In the case of Scheduling, does it do any throttling in that case? What if I schedule 200 reports for the same time? Will it run all 200 at once or will it be smart about it and do something to prevent this from hurting the performance of the entire system?

Mark

|||

swfranklin wrote:

Thanks David.

I will definitely research these options. On the surface, I don't think that attaching the reports to emails is a valid option for us because of the size of the files. In the situations where you include a link to the report, how is this being secured? Is the link to the Report Manager site with URL access? Or is the report output saved to a share with the link taking the user to a share? If it is persisted to a share, how are these files secured? How are the user and calling application notified of a failure?

Looks like a combination of subscription and custom delivery extension might be the answer.

Thanks for the help and sorry about all the questions.

You have a choice in output. You can send to a file share and manage the security on your own using Windows/AD / ftp folder...etc.
Or you can supply a link to the report in the email..in which case you'd be setting up security on the Report Server and the user gets authenticated when they click the link.

By failure..it depends upon what you mean. This would be report creation, not batch programming. Errors I have seen are email (address or server not being set up correctly)..in that case, no mail. Something like your dataset becomes unavailable? Other than that there really isn't much to subscriptions. A report is set to generate at 9AM and if I don't receive it in a timely manner I contact someone..If your report is first dependent upon say, a cube processing after a long ETL to a datawarehouse and that bombed...you would have to build some custom mechanism to alert your users. That would be outside of the scope of SSRS.

sql