Reporting Services using AlwaysON Availability Group

Environment

Blog28_1.PNG

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

Blog27_3

Blog28_2.PNG

Blog28_3.PNG

Blog27_6

Blog27_7

Blog28_4.PNG

Blog27_9.PNG

Blog28_5.PNG

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

Blog28_6.PNG

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

Blog28_7.PNG

Blog28_8.PNG

Blog28_9.PNG

Blog28_10.PNG

Blog28_11.PNG

-> 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,

Blog28_12.PNG

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

Blog28_13.PNG

Blog28_14.PNG

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

Blog28_15.PNG

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

Blog28_13

Blog28_16

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.

 

 

 

Advertisements

One thought on “Reporting Services using AlwaysON Availability Group

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s