SSRS 2008 - How to handle subscriptions on 2 instances of SSRS on a cluster?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » SSRS 2008 - How to handle subscriptions on 2...

SSRS 2008 - How to handle subscriptions on 2 instances of SSRS on a cluster?

Rate Topic

Display Mode

Topic Options

Author

Message

Vinny-262946

Posted Monday, January 07, 2013 9:22 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:31 PM
Points: 11, Visits: 301

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?

Post #1403700

anthony.green

Posted Tuesday, January 08, 2013 1:11 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

What edition of SQL are you running, Standard or Enterprise?

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1404027

Vinny-262946

Posted Tuesday, January 08, 2013 1:55 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:31 PM
Points: 11, Visits: 301

Standard Ed.

Post #1404052

anthony.green

Posted Tuesday, January 08, 2013 2:03 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

What is the setup?

Does each instance point to the same report server DB or do they point to different ones?

Post #1404055

Vinny-262946

Posted Tuesday, January 08, 2013 2:15 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:31 PM
Points: 11, Visits: 301

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

Post #1404064

anthony.green

Posted Tuesday, January 08, 2013 2:23 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1404068

Vinny-262946

Posted Tuesday, January 08, 2013 9:04 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:31 PM
Points: 11, Visits: 301

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.

Post #1404305

Vinny-262946

Posted Tuesday, January 08, 2013 2:33 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:31 PM
Points: 11, Visits: 301

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

Post #1404452

anthony.green

Posted Wednesday, January 09, 2013 1:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Use powershell, do a check on the service, if its UP, dont do anything, if its down run a script.

Post #1404594

« Prev Topic | Next Topic »

Permissions