How to Setup a Standalone SSRS Server As Part of an Availability Group

SQL Server Reporting Services (SSRS) is a useful tool that can be installed on a standalone server (a license is required), or it can be installed on an existing SQL Server host (this shares the existing license of SQL Server). This blog post covers how SSRS is installed on a standalone server and how to add the Reporting Server DBs to an existing AG. Enterprise Edition of SQL Server is required to follow along for this guide since the standard edition does not support more than one database per AG.

When you create an SSRS report that runs on a schedule, it is called a subscription; SSRS will create a job in SQL server with a GUID name (lots of letters and numbers) as the job name, and the category will be “Report Server.” These job(s) are created on the primary node for that availability group, which would be where the ReportServer and ReportServerTempDB DBs are located. The process I will show you will make it so you do not have to manually create the job(s) on the secondary or develop your own solution for getting it to work in your High Availability environment. SSRS drops and recreates Report Server jobs when it is restarted or stopped. If they do not, then you need to figure out why by using the troubleshooting steps I outline at the end of the post.

SSRS data sources will have to be changed to use the AG listener name, or if you are using a CNAME in DNS that is pointing to the listener that works as well. You do not want to use the servername when configuring data sources because in a failover scenario, your reports will no longer run successfully.

SQL Server Reporting Services Configuration

This section has three assumptions.

1. Your availability group is already set up.
2.  SSRS is installed on a separate server and configured using DBs ‘ReportServer’ and ‘ReportServerTempDB’.
3.  ReportServer and ReportServerTempDB DBs are added to the AG.

Here are the steps to set up SSRS.

Step 1: Create a directory called AGscripts on the E:\ drive or whatever location you choose on all nodes of the AG nodes. Give the account(s) that starts SQL Server service and SQL Agent FULL permissions to the directory.

Step 2: Copy the PowerShell script (SSRSfailoverCheck.ps1) file to E:\AGscripts.

• Download and rename the .txt to .ps1. This is VERY important that you do this.
• If you change the file, it must be changed on all nodes.
• If you ever need to change the location, set permissions for the new location (whatever starts SQL Server service and SQL Agent to have FULL permissions on the directory) and change the job that we create later to point to the new location.

$AGReplica = "servername\instance";

$Database = "DB_Administration";

$SSRSDBname = "ReportServer";

$AG_ROLE_Previous = Invoke-Sqlcmd -Database $Database -Query "select * from AG_role_SSRS;" -ServerInstance $AGReplica;

$AG_ROLE_Current = Invoke-Sqlcmd -Database $Database -Query "select sys.fn_hadr_is_primary_replica(N'$SSRSDBname') 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</h6>

    Invoke-Sqlcmd -Database $Database -Query "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 restart SSRS"

$SSRSServers = Invoke-Sqlcmd -Database $Database -Query "If (SELECT sys.fn_hadr_is_primary_replica(N'$SSRSDBname')) = 1 SELECT MachineName FROM [$SSRSDBname].[dbo].[Keys] WHERE MachineName IS NOT NULL" -ServerInstance $AGReplica;

Foreach ($SSRS in $SSRSServers)

{

Get-Service -Name "SQLServerReportingServices" -ComputerName $SSRS.MachineName | Restart-Service;

}

    }

 

    Invoke-Sqlcmd -Database $Database -Query "delete from AG_role_SSRS;" -ServerInstance $AGReplica;

    Invoke-Sqlcmd -Database $Database -Query "insert into AG_role_SSRS SELECT sys.fn_hadr_is_primary_replica(N'$SSRSDBname');" -ServerInstance $AGReplica;

 

}

Step 3: Modify – SSRSfailoverCheck.ps1

• Change $AGReplica = “servername\instance
  • ”Replace servername\instance
    • For default instance, it will only be your servername.
    • For named instance, it will be servername\instancename .i.e. named instance: Node01\Prod on one server and Node02\Prod on second server.
    • This will be different on each node.
• Change $Database = “DB_Administration”
  • Replace DB_Administration with a database you will use to create a table that the job will look at. This should be an administrative DB that does not get deleted or added to the AG.
• If you are using a Reporting Server DB other than ReportServer, change ReportServer to the correct report server database name you configured within SSRS Configuration Manager.
  • Change $SSRSDBname = “ReportServer”;

• Change “SQLServerReportingServices” to the correct SQL Server Reporting Services service Name (NOT the DisplayName)
  • To find the correct service Name on the server that is running SSRS, open PowerShell either remotely (File, New Remote PowerShell Tab…) and type the SSRS server name to connect to, or RDP into the SSRS server and open PowerShell and run the following.
    • Get-Service -Displayname “SQL Server*” | format-list -Property Name, Displayname, Status

• Change “SQLServerReportingServices” to the Name you gathered from above in the PS1 file if needed.
  • Get-Service -Name “SQLServerReportingServices” -ComputerName $SSRS.MachineName | Restart-Service;
• Save the PS1 file. Copy the PS1 from this first node you made changes for to each replica and modify accordingly.
  • Change $AGReplica = “servername\instance” to the correct name for that new node.

Step 4: Create the AG_role_SSRS table in your chosen administrative database and insert one record into it using the script below. 

USE [DB_Administration]

GO

 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[AG_role_SSRS](

[Role] [int] NULL

) ON [PRIMARY]

GO

 

 

/*

-- Initially manually insert a record into the table. For Primary node, add a record of 1. For secondaries add a 0.

-- Only one record needs to be added.

 

USE [DB_Administration]

GO

-- Primary node

INSERT INTO [dbo].[AG_role_SSRS]

([Role])

     VALUES

(1)

GO

 

SELECT * from [dbo].[AG_role_SSRS]

GO

 

-- Secondary nodes

INSERT INTO [dbo].[AG_role_SSRS]

([Role])

     VALUES

(0)

GO

 

SELECT * from [dbo].[AG_role_SSRS]

GO

*/

Step 5: Create the job – _MAINT_SSRS_CheckForFailover

• This job checks to see if a failover occurred which then restart SSRS so the subscription jobs get created on the new primary.
• Run the script job__MAINT_SSRS_CheckForFailover.sql.If you change the location where the AGScripts directory (E:\AGScripts) was created in step 1, modify the job before running to the new location or change it after you created the job. This job runs every minute. It causes SSRS to restart after a failover.

USE [msdb]

GO

 

/****** Object:  Job [_MAINT_SSRS_CheckForFailover]    Script Date: 6/3/2020 12:02:12 PM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 6/3/2020 12:02:12 PM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

END

 

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'_MAINT_SSRS_CheckForFailover',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'No description available.',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Failover check]    Script Date: 6/3/2020 12:02:12 PM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Failover check',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'PowerShell',

@command=N'powershell.exe -NoProfile -File "E:\AGscripts\SSRSfailoverCheck.ps1"',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every Minute',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=2,

@freq_subday_interval=15,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20191002,

@active_end_date=99991231,

@active_start_time=31,

@active_end_time=235959,

@schedule_uid=N'4669c313-3ca8-47db-b1e4-f0a03d71ad86'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'When Agent Starts',

@enabled=1,

@freq_type=64,

@freq_interval=0,

@freq_subday_type=0,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20191002,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959,

@schedule_uid=N'd9ada857-e5bd-49a9-9d43-d22f961ee876'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Step 6: Set permissions to all the SQL Server Agents Services to allow the restart of SSRS.

• Make sure the account that is running SQL AGENT has local admin rights on the SSRS server so it can restart the service or, using the principal of least privilege, just grant that permission to allow that account to restart the service. Giving local admin rights to the accounts that starts the SQL Server service.

Step 7: Account that starts SSRS

• Make sure the account that starts SSRS is a domain account! It can’t be a local account.
• Ask a system administrator to create a new domain account that will be used for SSRS only. Make sure you have the password. Within SSMS, create a new login for this new domain account, don’t set permissions. This login needs to be created on all nodes that are part of the AG. Since it is a domain account, you do not need to worry about SIDs within SQL.
• If it is using a local account, use SSRS configuration manager and change it to the newly created domain account. This will set the permissions that are needed within SQL.

Patching SSRS

1. Make sure the patch level for SSRS is at the latest. If running 2016, you have to be at or higher than SP2, otherwise the job will go through a recreate process and if the job does not exist first, you will fill up your SSRS logs with bogus errors. You will need to manually create that job so SSRS can delete and recreate. Use profiler or extended events to find the job name.
2. If running SSRS 2016 or lower, SSRS patches are included in SQL Server patches.
3. If running SSRS 2017 or higher, it is a completely different patching experience. Go to https://sqlserverbuilds.blogspot.com/2020/09/sql-server-reporting-services-ssrs.html Click on the Download  icon to download the latest patch. It looks like a new install when you patch but it is not.

Testing SSRS

Verify SSRS drop and recreate of jobs is working like you expected.

• This portion should be done during a maintenance window if this is in your Production environment.
• Stop SSRS within SSRS Configuration Manager.
• Connect via SSMS to the primary node of the AG. Verify all category “Report Server” jobs are gone. You can sort by category in the Job Activity Monitor window of SSMS or by querying msdb (query below). Do this on all nodes that are part of the AG. If a SSRS job exists, delete it.

MSDB query : SELECT a.name, b.name as [Category] from msdb..sysjobs a JOIN msdb..syscategories b ON a.category_id = b.category_id WHERE b.name LIKE '%Report Server%'

• Start SSRS and make sure the SSRS subscription job(s) are created.
• Failover, verify on new Primary the SSRS job(s) are created and the now new secondary the jobs are gone. If everything looks good, you can either leave the AG on this node or fail back. (does the check for failover job automatically update the table? I am unable to open the PowerShell file, so embedding it as code in the post may be beneficial)

Troubleshooting SSRS

Here are a couple of issues that I came across and fixed.

SSRS jobs do not delete when SSRS is stopped

SSRS controls the creation and deletion of SSRS jobs. This is true for SSRS that uses an Availability Group or standalone SQL Server on a separate server or on the same server as SQL Server. This is expected behavior for SSRS to delete its own jobs when the SSRS service is stopped.

Check if the job owner is the account that starts SSRS. You do not want to give that account sysadmin rights.

• Try manually deleting the jobs.
• Start SSRS and see if the job(s) get created.
• Stop SSRS and see if the job(s) were deleted.

SSRS job run errors with “Message: The job failed. The owner (domain\ssrsaccount) of job XXX-XXX-XXX-XXX does not have server access.”

Even if you set the domain account that starts SSRS as sysadmin, the job will fail with the above error. And, even if you verified the permissions for this account are correct you will still have this issue.

If you change the job owner to SA, it runs fine. But this is a problem if you have an AG, since the job gets dropped and recreated during a failover.

• If you do not know the password of the domain account that starts SSRS, then get a new domain account created or change the password of that domain account.
• Log into SSRS configuration manager, click on Service Account.
  • If you are going to be using a different domain account, just change the domain account and password to the new account and go through the steps described above in the main body of this post.
  • If you are going to be using the same account, change SSRS to use the built-in account, go through the steps again, and then change back to using “another account” option for the domain account. This process resets something within SQL and helped resolve the error when I saw it. Stop and restart SSRS.
  • Make sure the SSRS job owner is the account that starts SSRS and run the job. It should run fine now.

Summary

In conclusion, having your SSRS databases within an availability group is a great way to ensure your reporting services instance is always running with the primary node of your AG. The danger is making sure you have configured it correctly. This post allows you to have a process that is reliable, rather than in-house code to get SSRS subscription jobs running after a failover.

Acknowledgment: Thanks to Vivek Janakiraman for authoring the scripts in this post.