Share via

Keeping your report servers awake (or No more waiting for report server to startup)

  • Article

Update 5/3/2010:

With time and experience, we learn. So of course an easier way exists to do what this blog post states. Just set the Idle Time-out to zero on the Application Pool configuration. Bill Staples gives the UI snapshot of where to make the change here: https://forums.iis.net/t/1148433.aspx

HTH.

 

Reporting Services in SQL 2005 is hosted in IIS. IIS has a performance optimization built-in that you cannot disable (AFAIK), which shuts down an application once there is a period of 20 minutes of inactivity. This is governed by the Idle Timeout IIS Metabase property.

You can read about this Idle Timeout here:

https://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/b39a8ab9-7331-4bad-a89a-0210aadbd4a7.mspx?mfr=true

You can read about how to set the Idle Timeout here:

https://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/83b35271-c93c-49f4-b923-7fdca6fae1cf.mspx?mfr=true

The behavior of the Idle Timeout causes the user’s perception that Report Server is constantly falling asleep. In fact, the same behavior applies to any web application hosted in IIS. Once IIS has shut down the report server, it can take anywhere from 5 to 20 seconds to respond to the next request. This value ranges based on the performance of the server hosting IIS & RS. This time is taken up by IIS loading ASP.Net, ASP.Net in turn loading the report server DLLs, and finally report server starting up. All of this takes some time. Naturally, to a user it looks like report server is dozing off - again.

To address this issue you need to ping the report server periodically to ensure it is never inactive. To ping the server, make a Web Services (SOAP) request to the report server virtual directory. You could do the same with Report Manager; though RM load time is quite fast if the report server is already running. You can do this using your favorite scheduling technology like the Windows Task Scheduler, a SQL Agent job that runs an extended stored procedure, etc.

But since we’re talking about Reporting Services, I thought I’d create a sample to show you how to use the solve this problem using Subscriptions.

I posted a report that lists the items in the report server namespace.

To get the report:

First add yourself to the lukaszpblog group on https://groups.msn.com/lukaszpblog.

Then you can get the report here:

ListChildren Report:

https://www.msnusers.com/lukaszpblog/Documents/Files/ListChildren.rdl

Take the report and publish it to your report server. Viewing it will show you a very basic listing of content at the root of the report server namespace. Edit the properties for the report and set the data source credentials the report will run as. The credentials will either need to be stored or none. In order to choose none, you’ll need to set the unattended execution account. The account you choose should have the minimum level of privilege (I’d suggest Browser role) on the report server. Test that the report executes to make sure everything works.

Now you can create a subscription to this report. Use the parameters in the subscription to ensure the report executes against the report server you want it keep alive. If you’re deployed in a scale-out deployment you should create one subscription for each node in the scale-out and set the parameters explicitly to access the machine directly (not through the virtual server name or NLB). Set subscription to execute on a schedule and set that schedule to occur every 9 minutes. I like this frequency since if something were to go wrong, like a networking issue, you the subscription will run twice within the span of the 20 minute IIS application shutdown window.

With this solution, the report server will keep itself alive by continually processing the subscription which pings the web service and therefore IIS will never unload it. Problem solved.

Take care and good luck,

-Lukasz

Comments