Reporting Services using AlwaysON Availability Group



->SQL Server Reporting Services present in JBSERVER4 is configured using Database Server JBSERVER1 initially.

-> SQL Server Reporting Services databases are later placed on the Availability Group JBS_SSRSAG.

-> Database [ReportServerTempDB] recovery model will be changed to Full from Simple.

-> Adding the [ReportServer] and [ReportServerTempDB] database to the Availability group JB_SSRSAG.


-> Checking the new Availability group in SQL Server management Studio,


-> Reconfiguring the SQL Server Reporting Services on JBSERVER4 to use the Availability group listener,


-> Executing a Sample report that uses Availability group Listener JBS_APP that has read-only routing configured.

-> Please refer article “Configuring Read Only Routing List for AlwaysON Availability Group” to get more details on Availability group JBS_AG and its Listener JBS_APP.

-> The data source on the report doesn’t use Application Intent,


-> Executing the report and checking the profiler trace. The queries are executed on the Primary JBSERVER1.


-> The data source on the report uses the Application Intent to ReadOnly,


-> Executing the report and checking the profiler trace. The queries are executed on the Secondary JBSERVER3.


SQL Server Reporting Services Subscription Jobs

-> Execute the below query only once on database server JBSERVER1, JBSERVER2 and JBSERVER3. The table stores the details whether that particular replica is a primary or secondary replica.

create table AG_role (Role int)
insert into Ag_Role select sys.fn_hadr_is_primary_replica(N'ReportServer')

-> Save the below script as a powershell script file on Server JBSERVER1, JBSERVER2 and JBSERVER3. Please make sure that $AGReplica is changed appropriately in the script on each of the server before saving.

$AGReplica = "JBSERVER1";
$AG_ROLE_Previous = Invoke-Sqlcmd "select * from AG_role;" -ServerInstance $AGReplica;
$AG_ROLE_Current = Invoke-Sqlcmd "select sys.fn_hadr_is_primary_replica(N'ReportServer') as Role;" -ServerInstance $AGReplica;
If ($AG_ROLE_Previous.Role -eq $AG_ROLE_Current.Role)
"No Failover occurred"
"Failover occurred"
#Delete the SQL Server agent jobs related to Subscription

@TSQL = @TSQL + 'EXEC msdb.dbo.sp_delete_job @job_name = N''' + + '''' + CHAR(13)
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.syscategories AS c
ON j.category_id = c.category_id
WHERE = N'Report Server';
EXEC sp_executesql @TSQL;" -ServerInstance $AGReplica;
If ($AG_ROLE_Current.Role -eq 1)
"Failover Occurred"
$SSRSServers = Invoke-Sqlcmd "If (SELECT sys.fn_hadr_is_primary_replica(N'ReportServer')) = 1 SELECT MachineName FROM [ReportServer].[dbo].[Keys] WHERE MachineName IS NOT NULL" -ServerInstance $AGReplica;
Foreach ($SSRS in $SSRSServers)
Get-Service -Name "ReportServer" -ComputerName $SSRS.MachineName | Restart-Service;
Invoke-Sqlcmd "delete from AG_Role;" -ServerInstance $AGReplica;
Invoke-Sqlcmd "insert into AG_Role SELECT sys.fn_hadr_is_primary_replica(N'ReportServer');" -ServerInstance $AGReplica;

-> On the Secondary replica, the script basically deletes all the jobs related to SSRS subscription in the SQL Server Agent when it detects a failover.

-> On the primary replica, the script deletes all the jobs related to SSRS subscription in the SQL Server Agent and 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.

-> Create a SQL Server agent job to call the Powershell script saved in the previous step under a job step with type PowerShell. The job should run every 1 minute and also when the SQL Server Agent starts.

Thank You,
Vivek Janakiraman

The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

10 thoughts on “Reporting Services using AlwaysON Availability Group

    • I have implemented and configured 3 node SSRS scale-out deployment for SSRS high availability solution on our production environment, but I am worried regarding SSRS subscription jobs. Currently the subscription jobs on primary server is working fine and we have 4000 subscriptions jobs. Last time manually i failed back to the secondary server and all the subscription jobs recreated on the new primary server which was secondary previously. Then I failed over back to the previous state and disabled all the subscription jobs manually. So I want solve this subscriptions issues instead of disabling the subscription based jobs in agent manually please help me? I just saw your blogs regarding powershell script I have to make sure to run the script on production environment.

      • Thank you, it works fine I tested in my lab environment for 1 subscription. On our prod environment we have more than 4000 subscription jobs how many time it will take to recreate the subscription jobs on new primary replica and delete the subscription jobs on secondary replica when failover happens for 4000 subscription jobs? I want to make sure this before before applying on production environment.

  1. Thanks Vivek for your Article on SSRS DB Migration. Can we follow the same steps to migrate the SSRS DB (on a dedicated server) used with SCCM CB to AON AG? Also I’m not getting the section “SQL Server Reporting Services Subscription Jobs”, is it relevant for SCCM SSRS?

    • Hello LMS,

      Yes you can follow the same procedure. SQL Server Reporting Services Subscription Jobs are only applicable if you have scheduled SSRS reports to execute as per a schedule. Refer this article “” for more details.

  2. Pingback: Fail-over or switching the Reporting server database
  3. Pingback: How to Setup a Standalone SSRS Server As Part of an Availability Group - SQL Server Consulting - Straight Path Solutions

Leave a Reply