SQL 2008R2 SSRS Subscriptions do not run consistently.

We are using 2008 R2 in an active/passive failover cluster.  The failover is working properly.  When we fail over from our primary server (server1) to the secondary server (server2), the SSRS reports are still available and functioning properly.
We are testing using timed subscriptions to send reports to users via email.  We have found that they are very inconsistent.  While testing, we have them scheduled to run every hour during the work day.  We receive the emails about half of the time.  While going through different logs, tables, etc.  I found the ExecutionLog table.  It is very consistent that when the job runs that creates the SSRS event, it gets logged here in the ExecutionLog table.  It is also very consistent that when the reports get delivered properly, I see Server1\MSSQLSERVER in the InstanceName field.  However, when the reports do not get delivered, I see Server2\MSSQLSERVER in the InstanceName.  It looks like that even though we are running on Server1, Server2 is trying to process the reports.  I assume this is due to load balancing seeing Server1 being busy processing other reports so it sends them to Server2.  So how can I prevent this from happening?  If we are running on Server1, then only Server1 should be processing the reports.  If it is not a load balancing issue, what could it be?
jat0818Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
Stop the reporting service on server2.
0
Deepak ChauhanSQL Server DBACommented:
you can stop this service from services.msc or sql server configuration manager.
0
jat0818Author Commented:
If I stop the SSRS service and set the startup type to manual, will it start if we have to fail over to the passive cluster?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Deepak ChauhanSQL Server DBACommented:
you can disable instead of manual.
0
jat0818Author Commented:
But if it fails over to the secondary cluster, then I need that cluster to process the reports.  So disabling is probably not an option.  If I set it to manual, will it start when it fails over?
0
Deepak ChauhanSQL Server DBACommented:
Reporting service is not a cluster aware service. It will not start automatically if you are not using any startup stored procedure to call the startup of this service. If you will set it to manuall and failover happen to server2 , startup stored procedure will execute if you have configured.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jat0818Author Commented:
I will look into this option and post my results.
0
jat0818Author Commented:
It looks like this is working perfectly.  Our SSRS subscriptions are now running as scheduled.  We have not moved to the second cluster yet, so I have not seen the startup proc run in the live environment, but it worked in testing.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.