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

Report Server Windows service - unable to start

Hi,
For some reason I am unable to start RS Windows service. Here are the
details of Exception that I get -
****************************
System.ServiceProcess.TimeoutException: Time out has expired and the
operation has not been completed.
at
System.ServiceProcess.ServiceControoler.WaitForStatus(ServiceControllerStatus
desiredStatus, TimeSpan timeout)
at
ReportServicesConfigUI.Panels.ServerInformationPanel.StartStopServiceTask(boolean start)
****************************
Both database and reporting servers are installed in a stand alone server
(MS Windows Server 2003 R2 Enterprise Edition SP1)
Any idea what could be wrong here please?
Many thanks in advance,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/Hi
You have the same problem as described in the following article (it applies
in your case, except for the hotfix). Certificate Revocation List is not
accessible.
http://support.microsoft.com/kb/918644
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Harish Mohanbabu" <Axapta@.online.nospam> wrote in message
news:D7ED0B0A-F196-4FED-89B9-5A4310B24A32@.microsoft.com...
> Hi,
> For some reason I am unable to start RS Windows service. Here are the
> details of Exception that I get -
> ****************************
> System.ServiceProcess.TimeoutException: Time out has expired and the
> operation has not been completed.
> at
> System.ServiceProcess.ServiceControoler.WaitForStatus(ServiceControllerStatus
> desiredStatus, TimeSpan timeout)
> at
> ReportServicesConfigUI.Panels.ServerInformationPanel.StartStopServiceTask(boolean
> start)
> ****************************
> Both database and reporting servers are installed in a stand alone server
> (MS Windows Server 2003 R2 Enterprise Edition SP1)
> Any idea what could be wrong here please?
> Many thanks in advance,
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/|||Hi Mike
Thanks for your reply.
Just to clarify - I noticed that the KB article refers to SSIS service. The
same applies to Reporting services as well is it?
Once again thanks for your response.
Regards,
Harish
--
"Michael Epprecht [MSFT]" wrote:
> Hi
> You have the same problem as described in the following article (it applies
> in your case, except for the hotfix). Certificate Revocation List is not
> accessible.
> http://support.microsoft.com/kb/918644
> Regards
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Harish,
Thank you for using MSDN Managed Newsgroup Support.
I would like to know some additional information:
1. What's the Reporting Services Version? SQL Server 2005 or SQL Server
2000? Have you applied any Services Pack?
2. What's the result when you access the Report Server and Report Manager?
3. How do you try to start the Reporting Services?
Please let me know the result and so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
Many thanks for your response. Here are the answers -
1. It is SQL Server 2005 (9.00.1399.00). I have not applied any SP1 yet
2 and 3 -
I receive the error message while trying to start Report server via
Reporting Services Configuration Manager.
Also now I am getting this different error message from the Database
connection -
***************************************
System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases
for databas 'DynamicsTempDB'. No entry found with that name. Make sure that
the name is entered correctly.
.......
***************************************
I am trying to get Report server connect to an existing database called
'Dynamics'.
Could you kindly let me know how to get this up and running please?
Thanks in advance,
Harish
_____________________________________
"Wei Lu" wrote:
> Hi Harish,
> Thank you for using MSDN Managed Newsgroup Support.
> I would like to know some additional information:
> 1. What's the Reporting Services Version? SQL Server 2005 or SQL Server
> 2000? Have you applied any Services Pack?
> 2. What's the result when you access the Report Server and Report Manager?
> 3. How do you try to start the Reporting Services?
> Please let me know the result and so that I can provide further assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support|||Hi Wei,
Forgot to add this - I did search for 'DynamicsTempDB' in the
rsreportserver.config file but couldn'd find this.
Regards,
Harish|||Hi Harish,
Thank you for your update.
From the description, my understanding of this is that: You get the
following error when you try to start the Reporting Services:
***************************************
System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases
for databas 'DynamicsTempDB'. No entry found with that name. Make sure that
the name is entered correctly.
......
***************************************
If I misunderstood your concern, please feel free to let me know.
Firstly, please try to confirm the database DynamicsTempDB is existed in
your database server.
It seems that you are trying to connect to a Report Server database. Please
make sure the Dynamics database is a Report Server database.
Please try to use the rsconfig utility to connect to the database.
rsconfig Utility
http://msdn2.microsoft.com/en-us/library/ms162837.aspx
rsconfig -c -s <SQLSERVERNAME> -d reportserver -a Windows -u
<MYDOMAIN\MYACCOUNT> -p <PASSWORD>
Secondly, would you please send the Reporting Service Error log to me if
the above steps does not resolve the issue?By default, the log file is
located at \Microsoft SQL Server\<SQL Server Instance>\Reporting
Services\LogFiles. You may zip the folder and send to me directly. I
understand the information may be sensitive to you, my direct email address
is weilu@.ONLINE.microsoft.com(Please remove ONLINE when you send the
email), you may send the file to me directly and I will keep it secure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
Thanks for your assistance.
I tried your suggestion without any luck (:
As mentioned I have sent those log files in Zip format. Would very much
appreciate if you could kindly help me.
Sincerely,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
"Wei Lu" wrote:
> Secondly, would you please send the Reporting Service Error log to me if
> the above steps does not resolve the issue?By default, the log file is
> located at \Microsoft SQL Server\<SQL Server Instance>\Reporting
> Services\LogFiles. You may zip the folder and send to me directly. I
> understand the information may be sensitive to you, my direct email address
> is weilu@.ONLINE.microsoft.com(Please remove ONLINE when you send the
> email), you may send the file to me directly and I will keep it secure.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support|||Hi Harish,
Thank you for the update.
I have checked the Log file you send to me.
According to the error message, I would like to get the config file from
you.
Please provide the following config files:
\Microsoft SQL Server\<SQL Server Instance>\Reporting
Services\ReportManager\RSWebApplication.config
\Microsoft SQL Server\<SQL Server Instance>\Reporting
Services\ReportManager\Web.config
\Microsoft SQL Server\<SQL Server Instance>\Reporting
Services\ReportServe\rsreportserver.config
\Microsoft SQL Server\<SQL Server Instance>\Reporting
Services\ReportServe\web.config
Please zip these files and send to me. Thank you !
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
Many thanks for your help.
As required I have mailed you the config files. Looking forward to hearing
from you.
Once again many many thanks for your assitance. Much appreciated :)
Sincerely,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
"Wei Lu" wrote:
> Hi Harish,
> Thank you for the update.
> I have checked the Log file you send to me.
> According to the error message, I would like to get the config file from
> you.
> Please provide the following config files:
> \Microsoft SQL Server\<SQL Server Instance>\Reporting
> Services\ReportManager\RSWebApplication.config
> \Microsoft SQL Server\<SQL Server Instance>\Reporting
> Services\ReportManager\Web.config
> \Microsoft SQL Server\<SQL Server Instance>\Reporting
> Services\ReportServe\rsreportserver.config
> \Microsoft SQL Server\<SQL Server Instance>\Reporting
> Services\ReportServe\web.config
> Please zip these files and send to me. Thank you !
> Sincerely,
> Wei Lu
> Microsoft Online Community Support|||Hi Harish,
Thank you for the update.
Would you please try to modify the <WebServiceIdentity> Entry in the
rsreportserver.config file?
Currently, it was <WebServiceIdentity>Local System</WebServiceIdentity>.
Please modify it to
<WebServiceIdentity>NT Authority\System</WebServiceIdentity>
After doing this, please try to start Reporting Services.
Here is an article for your reference:
You may not be able to access Reporting Services after you create an
application pool that runs under the LocalSystem account
http://support.microsoft.com/kb/907267/en-us
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Harish,
Just checking in to see if the suggestions were helpful. Please let us know
if you would like further assistance.
Have a great day!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
Thanks to your excellent support at last I managed to resolve the problem.
Thanks for your help :)
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/|||Hi Harish,
Thank you for your update and glad to hear the information is helpful!
If you have any questions or concerns, please feel free to let us know!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Wei Lu!
I read this thread in its entirety and I am having the exact same problem.
However when I went to modify my rsreportserver.config file based on your
instruction below, my config file was already set to <WebServiceIdentity>NT
Authority\System</WebServiceIdentity>.
Do you have any other thoughts on how I can resolve this problem? Thanks!
Amedeo Feroce
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:%23g9GfASlGHA.4948@.TK2MSFTNGXA01.phx.gbl...
> Hi Harish,
> Thank you for the update.
> Would you please try to modify the <WebServiceIdentity> Entry in the
> rsreportserver.config file?
> Currently, it was <WebServiceIdentity>Local System</WebServiceIdentity>.
> Please modify it to
> <WebServiceIdentity>NT Authority\System</WebServiceIdentity>
> After doing this, please try to start Reporting Services.
> Here is an article for your reference:
> You may not be able to access Reporting Services after you create an
> application pool that runs under the LocalSystem account
> http://support.microsoft.com/kb/907267/en-us
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||I've seen this issue on quite a few message boards. With that being said, I
am sharing the solution that worked for me.
I simply installed SQL SERVER 2005 SP1 and re-booted the server. I opened
the reporting services configuration
tool and and reporting services was already running (keep in mind that I had
previously configured all of the other settings when I could not start
reporting services).
I hope this helps!
Amedeo
"Amedeo Feroce" <aferoce@.akdhc.com> wrote in message
news:%23HIknFtpGHA.3820@.TK2MSFTNGP05.phx.gbl...
> Hello Wei Lu!
> I read this thread in its entirety and I am having the exact same problem.
> However when I went to modify my rsreportserver.config file based on your
> instruction below, my config file was already set to
> <WebServiceIdentity>NT Authority\System</WebServiceIdentity>.
> Do you have any other thoughts on how I can resolve this problem? Thanks!
> Amedeo Feroce
> "Wei Lu" <weilu@.online.microsoft.com> wrote in message
> news:%23g9GfASlGHA.4948@.TK2MSFTNGXA01.phx.gbl...
>> Hi Harish,
>> Thank you for the update.
>> Would you please try to modify the <WebServiceIdentity> Entry in the
>> rsreportserver.config file?
>> Currently, it was <WebServiceIdentity>Local System</WebServiceIdentity>.
>> Please modify it to
>> <WebServiceIdentity>NT Authority\System</WebServiceIdentity>
>> After doing this, please try to start Reporting Services.
>> Here is an article for your reference:
>> You may not be able to access Reporting Services after you create an
>> application pool that runs under the LocalSystem account
>> http://support.microsoft.com/kb/907267/en-us
>> Hope this will be helpful!
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>

Report Server 2005 - SQL Server 2000/2005 for DB

Hi,
I am currently running SQL Server 2000 SP3 with Reporting Services 2000 SP2.
I am unable to upgrade the SQL Instance 2005, but would really like to
upgrade RS to 2005.
RS 2000 is currently installed on a separate server to the SQL Instance, but
the RS Databases (ReportServer and ReportServerTempDB) are installed on the
SQL 2000. Would it work to upgrade RS to 2005, but leave the databases on a
SQL Server 2000?
Cheers,
NinaThis is a supported configuration. I have done this but in my case SQL
Server DB was on the same machine. However, I do know that what you want to
do is fully supported. Just do the upgrade for RS the same as it says, it
does not differ because the DB is on 2000 rather than 2005.
One point, RS 2005 management tools are integrated with the new 2005
management tools so you will need that installed somewhere.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nina" <Nina@.discussions.microsoft.com> wrote in message
news:8C9877E2-D227-43DB-8125-469272E72E31@.microsoft.com...
> Hi,
> I am currently running SQL Server 2000 SP3 with Reporting Services 2000
> SP2.
> I am unable to upgrade the SQL Instance 2005, but would really like to
> upgrade RS to 2005.
> RS 2000 is currently installed on a separate server to the SQL Instance,
> but
> the RS Databases (ReportServer and ReportServerTempDB) are installed on
> the
> SQL 2000. Would it work to upgrade RS to 2005, but leave the databases on
> a
> SQL Server 2000?
> Cheers,
> Nina
>