
Power BI Report Server: Handling Agent Jobs in an Availability Group
Deploying Power BI Report Server in a SQL Server Availability Group (AG) is a great way to boost resilience and uptime. But there’s a catch: Power BI Report Server itself isn’t AG-aware. That means when a failover happens, things can get messy—especially with SQL Agent jobs.
The Problem with Failover
When the AG fails over to a secondary node:
- Power BI Report Server doesn’t automatically recognize the change.
- SQL Agent jobs on the secondary keep running—but they can’t access the ReportServer database, leading to errors.
The Fix: Add a Primary Check Step to Agent Jobs
To avoid this, you can add a check step to each SQL Agent job that ensures it only runs on the primary node of the AG.
Here’s a simple example:
IF ( SELECT DISTINCT role_desc FROM sys.dm_hadr_availability_replica_states WHERE is_local = 1 ) = 'PRIMARY' BEGIN PRINT 'The job is being started because the server is currently PRIMARY in the Availability Group.' END ELSE BEGIN SELECT 1/0; -- Force error PRINT 'The job will not be executed because the server is currently SECONDARY in the Availability Group.' END
This logic prevents jobs from running on the secondary node, avoiding unnecessary failures.
Automating the Setup
To make this scalable, I’ve created a stored procedure:Fixdbo.pr_checkRSJobsAddStepAGPrimary
It does the following:
- Finds all SQL Agent jobs in the “Report Server” category.
- Adds a check step to ensure the job only runs on the AG primary.
- Optionally changes the job owner to
NT SERVICE\SQLSERVERAGENT
if the parameter@changeOwnerToSQLAgent
is set to1
.
Installation Steps
- Download the scripts.
- Open
pr_checkRSJobsAddStepAGPrimary.sql
- Adjust the database name in line 1. I use
wartung
as my admin DB—you can choose your own. - Run the script to create the procedure.
- Adjust the database name in line 1. I use
- Open
modifyRSJobs.sql
- Replace all instances of
wartung
with your preferred database name.
- Replace all instances of
- Run the job and monitor execution.
This setup ensures your Report Server jobs behave correctly during failovers and only run where they’re supposed to. I recommend to also sync all Agent Jobs in a classic AG using dbatool’s PowerShell cmdlet Sync-DbaAvailabilityGroup. It’s a small tweak that saves a lot of headache. If you are already running with a Contained Availability Group then congratulations…Microsoft already takes care of this.
Disclaimer
This article was created based on my personal notes with support from Microsoft Copilot. While Copilot assisted in structuring and refining the content, all technical details have been carefully reviewed and developed by me.