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

Installation Steps

1. Download the scripts.
2. 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.
3. Open modifyRSJobs.sql
   • Replace all instances of wartung with your preferred database name.
4. 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.