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