SQL 2019 High Availability Stored proc issue on secondary server

Question

I've just upgraded my High Availability environment from SQL 2017 to 2019 version 15.0.4236.7 I have same SQL Agent Jobs on both servers with a initial clause to determine if the job should run (if the server is the primary). This has always worked in the past but I've run into an error. Now, the same job on the secondary server fails if it calls a stored procedure. If the job has an inline select to a table that is read only, the Job runs with no issues but the proc throws an error that "The target database is currently....". It's as if the procedure is read before the execution whereas the in line select is not read till the line is invoked. Here is a simple test that works on 2017 secondary server but not on 2019. Not sure if its a bug or a feature.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
use master; 
Declare @AGName varchar(20)  = 'MY-SQLAGNAME'

declare @ret bit

    SELECT
        @ret = count(AGC.name)
    FROM
     sys.availability_groups_cluster AS AGC
      INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id
      INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id
    WHERE
     ARS.role_desc = 'PRIMARY' and AGC.name = @AGName and RCS.replica_server_name = @@ServerName 


 if @ret = 1
 begin;

 Select 'Shouldt go in here'

 select top 1 * from MyDby.dbo.MyTable -- This works
 
 exec MyDb.dbo.AnyProcedure -- This Bombs 

 end

Answer 1

I recommend a change in your process.

1. Move the Primary check logic out of the procedure into the first step of the job. If the step fails, quit the job reporting success. This way, no other steps are run, and you don't have to repeat the logic.

2. Instead of querying the DMV, you can use the function sys.fn_hadr_is_primary_replica.

A database can only belong to a single AG. So here's an example of the first step logic. Then, of course, you can also wrap it in a procedure.

IF sys.fn_hadr_is_primary_replica ('MyDb') <> 1
    RAISERROR('Not a primary replica.', 16, 1)