January 7, 2013 at 9:22 am
Hi all,
I ran into an interesting question from a developer on how to handle subscriptions on 2 instances of SSRS on a cluster. Our setup is an Active - Active 2 node cluster with 2 instances. ON top of this we have 2 instances of SSRS. The second instance of SSRS is a copy of the first one....meaning the same reports are deployed on each instance and query the same db. The reason for this is redundancy and having the possibility of using a web service load balancer.
We have subscriptions setup on SSRS node1 but not node2. In the case I lose node1, I will not have the subscriptions email out daily. Setting up the subscriptions on both nodes would email the end user twice.
Any suggestions on how I would be able to configure subscriptions to be failure proof?
January 8, 2013 at 1:11 am
What edition of SQL are you running, Standard or Enterprise?
January 8, 2013 at 1:55 am
Standard Ed.
January 8, 2013 at 2:03 am
What is the setup?
Does each instance point to the same report server DB or do they point to different ones?
January 8, 2013 at 2:15 am
2 different reporting DBS and different URLs. Same reports are hosted. The 2nd SSRS is to be used if node 1 was to be unavailable
January 8, 2013 at 2:23 am
Sorry few more questions
Are both ReportServer DB's on the same SQL server?
Is SQL Server also installed in the cluster or is it a cluster dedicated to SSRS?
If the SQL server is also installed with SSRS, you could setup a job which runs at agent startup which checks to see what the current active node is then loop through that nodes ReportDB find the subscriptions and enable the jobs and disable the other nodes subscriptions.
Would mean that you have to create the same subscription twice, and ensure that you disable the correct job.
January 8, 2013 at 9:04 am
HI Anthony,
yes, the sql server hosting the dbs is clustered. Interesting thought on the agent job to verify the active node, thanks for the advice. I will look into this option and report back.
January 8, 2013 at 2:33 pm
Well to disable the subscriptions I can :
1. List all subscriptioins and its schedules being diabled.
Use ReportServerDatabase
go
select cl.ItemID,cl.Path,cl.Name,rs.ScheduleID from Catalog cl inner join ReportSchedule rs on cl.ItemID = rs.ReportID
2. Use the loop statement to enclose the following command to disable the schedules returned in the above step.
Use msdb
go
exec sp_update_job @job_name = 'jobname',@enabled = 0 -- by default, the jobname is equal to the scheduleID.
via an sql job. I need to come up with a way to trigger this if SSRS on the first node were to go down
January 9, 2013 at 1:25 am
Use powershell, do a check on the service, if its UP, dont do anything, if its down run a script.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply