locked
SSRS Subscriptions and Always On RRS feed

  • Question

  • Hi

    I'm searching the web but i didn't found a good solution.

    What is the best way to maintain the SSRS Subscription Jobs if i have the Reporting database in a Always on availability Group.

    Our SSRS is running as a SharePoint Service Application

    The Problem is now that the SQL Jobs get created localy on the Primary node, if a Failover occours the SQL-Jobs will fail and no report subscriptions will work.

    I'm thankful for any help

    Ueli

    Tuesday, November 6, 2018 1:51 PM

All replies

  • Hi uekur,

    You said when the Primary mode didn't work, the subscription also didn't works. If so, I think you could try to synchronize agent job in second mode.

    In addition, after the database failover completes and after the report server service is re-started, SQL Server Agent jobs will be re-created automatically. Until the SQL agent jobs are recreated, any background executions associated with SQL Server Agent jobs will not be processed. This includes Reporting Services subscriptions, schedules, an snapshots.

    You could refer to Reporting Services with Always On Availability Groups for details.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 7, 2018 5:36 AM
  • Hi uekur,

    You said when the Primary mode didn't work, the subscription also didn't works. If so, I think you could try to synchronize agent job in second mode.

    In addition, after the database failover completes and after the report server service is re-started, SQL Server Agent jobs will be re-created automatically. Until the SQL agent jobs are recreated, any background executions associated with SQL Server Agent jobs will not be processed. This includes Reporting Services subscriptions, schedules, an snapshots.

    You could refer to Reporting Services with Always On Availability Groups for details.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    I did not have a report server Service, i only have a Service instance in SharePoint, if i Restart the Service instance no Jobs get created automaticly, how can i reproduce this behavior in SharePoint mode?
    Wednesday, November 7, 2018 6:51 AM
  • Hi uekur, 

    You could synchronize report server database( which SSRS information and content stored in ) in second node, then configure SSRS to use report server database from second node. Then re-start the service, the SQL Agent will automatically create jobs based on exist subscriptions, then subscription will work.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 8, 2018 3:22 AM
  • Hi Zoe

    My ReportServer Service Instance Databases are in the Availability Group and get synchronized to the second node, with SharePoint Integrated SSRS i did not have a service i could restart, i tried to restart the SharePoint SSRS Service Instance, but no Jobs are created automatically.

    Best Reagards,

    Ueli

    Thursday, November 8, 2018 5:35 AM
  • Hi uekur,

    Did you re-specify the your SSRS instance to use report server database in second node? If not, please check it and re-start your sharepoint SSRS instance.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 9, 2018 6:19 AM
  • Hi Zoe

    I don't know exactly what you mean, the ReportServer Service Instance Databases are part of the AlwaysOn Availabilty Group, the Service Instance connects via the Always On Availabilty Group Listener to the databases, if a failover ocur i did not need anything to change.

    Best Regards,

    Ueli

    Friday, November 9, 2018 8:10 AM
  • Hi uekur,

    What I mean is Reporting Service Configuration Manager(reconfigure report server database in RSCM)  or Reporting Services service application(s). Update the database connection to use the Lister DNS Name. for native mode report servers, change the Report Server Database Name in Reporting Services configuration manager. For SharePoint mode, change the Database server name for the Reporting Services service application(s). 

    You could refer to Reporting Services with Always On Availability Groups (SQL Server) and this blog for details.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 12, 2018 3:20 AM
  • Hi Zoe

    Yes, it is all configured correct via the Listener.

    What i'm struggeling About is the part with the recration of the subscriptions:

    "restarts the SQL Server Reporting Services on JBSERVER4 if it detects a failover. This ensures that the subscription jobs are recreated on the primary replica."

    This does not happen on my System, does this also work if the SSRS is in SharePoint mode?

    Best Regards,

    Ueli

    Monday, November 12, 2018 7:57 AM
  • Hi uekur,

    I didn't have sharepoint environment currently. In native mode, you could re-specify report server database in Reporting Service Configuration Management. In sharepoint mode, did you change it to corresponding server and database?

    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 13, 2018 8:35 AM
  • Hi Zoe

    Yes, see my config:

    And on the SQL Server:

    Best Regards,

    Ueli



    • Edited by uekur Tuesday, November 13, 2018 9:07 AM Pic Problem
    Tuesday, November 13, 2018 9:04 AM
  • Hi uekr,

    You could check whether  SQL Agent in second node server is start or not(you could restart it to see whether it works or not). In addition, you said the subscription didn't work, is there any other error information in log file?

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 14, 2018 6:28 AM
  • Hi Zoe

    The SQL Agent is running, the subscriptions works, the only part which is not working is the automatically recreation of the subscriptions after a Failover as you described above:

    Then re-start the service, the SQL Agent will automatically create jobs based on exist subscriptions

    Best Regards,

    uekur

    Wednesday, November 14, 2018 7:03 AM
  • Hi uekur,

    As I know, the SSRS will store information in report server database(including subscription information). So if you  synchronize report server database, the subscription won't change. When you in new node, there is no job in Agent, it will based on subscription and its schedule to create corresponding job, then it will file the job to send email(this is in Native mode), I think this will be similar to Sharepoint mode.

    And I am not clear about your part("the only part which is not working is the automatically recreation of the subscriptions after a Failover as you described above"), you said your subscriptions work well, so which part didn't work in your scenario?

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 14, 2018 9:44 AM
  • Hi Zoe

    On the Primary node, i have SQL Agent Jobs (SSRS - Subscriptions)

    On the secondray node, there are no SQL Agent Jobs.

    Then a Failover ocur, on the new Primary i have still no SQL Agent Jobs (SSRS - Subscriptions), you tell me i have to Restart the Service Instance and they will automatically gets created -> But this part does not work, i didn't get the SSRS Jobs automatically created!

    Wednesday, November 14, 2018 10:07 AM
  • It seems still use agent in primary node, try to stop Agent in primary and start it in second node test it.
    Friday, November 16, 2018 1:20 AM
  • Hi

    SQL Server Agent is running on both nodes, thats not the problem

    Friday, November 16, 2018 6:05 AM
  • Hi 

    It(SQL server agent) might not recognize which node is primary when switch. You could need to tigger it.Refer to  https://dba.stackexchange.com/questions/45137/sql-server-agent-jobs-and-availability-groups for query.

    Monday, November 19, 2018 8:05 AM
  • Hi

    That's not the Problem, i did not have the Jobs.

    Then a Failover ocur, on the new Primary i have still no SQL Agent Jobs (SSRS - Subscriptions), you tell me i have to Restart the Service Instance and they will automatically gets created -> But this part does not work, i didn't get the SSRS Jobs automatically created!

    Monday, November 19, 2018 9:58 AM