Environment

->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"
}
else
{
"Failover occurred"
#Delete the SQL Server agent jobs related to Subscription

Invoke-Sqlcmd "DECLARE @TSQL NVARCHAR(MAX) = N'';
SELECT
@TSQL = @TSQL + 'EXEC msdb.dbo.sp_delete_job @job_name = N''' + j.name + '''' + CHAR(13)
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.syscategories AS c
ON j.category_id = c.category_id
WHERE c.name = 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

Disclaimer:
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.