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