availability groups - checking for primary server - cannot call a procedure with parameters

Question

In my servers that are involved in alwayson availability groups if there are jobs running, inside the jobs I always test whether the current server is the primary in the availability group.

So the job only runs on the primary server of the AG.

something like this is working fine:

If sys.fn_hadr_is_primary_replica ('JUNOReporting') =1  
BEGIN 
        declare @reportYear int, @reportMonth int, @reportMonthPrevious int

        SELECT @reportYear  = CASE WHEN month(getdate()) = 1 THEN year(getdate()) - 1 ELSE year(getdate()) END, 
               @reportMonth = CASE WHEN month(getdate()) = 1 THEN 12 ELSE month(getdate()) - 1 END

        SELECT @reportMonthPrevious  = @reportMonth - 1
        EXEC JUNOReporting.dbo.usp_some_other_procedure_without_parameteres 
END

However, if instead of the procedure dbo.usp_some_other_procedure_without_parameteres that lives in the database called JunoReporting, I want to call a different procedure that has parameters, then SQL Server does not like it.

I think he is putting the cart before the horses, but how can I tell him?

when I have the following code within a job, the job fails:

declare @reportYear int, @reportMonth int, @reportMonthPrevious int

SELECT @reportYear  = CASE WHEN month(getdate()) = 1 THEN year(getdate()) - 1 ELSE year(getdate()) END, 
       @reportMonth = CASE WHEN month(getdate()) = 1 THEN 12 ELSE month(getdate()) - 1 END

SELECT @reportMonthPrevious  = @reportMonth - 1

If sys.fn_hadr_is_primary_replica ('JUNOReporting') =1  
begin
    -- this server is the primary replica, do something here
    print 'yes, primary'

            IF @reportMonthPrevious >= 1
            BEGIN
                EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth @reportYear, @reportMonthPrevious

            END
            EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth @reportYear, @reportMonth
END

with the boring and obvious error message - that's exactly why I was testing if the current server is in the primary role anyway:

Msg 976, Level 14, State 1, Line 22

The target database, 'JUNOReporting', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

Any ideas how to work around this one? I prefer not to add that piece of code inside the procedure, so avoiding the parameters issue sql server seems to have on the occasion.

Just to clear any misunderstanding:

On the seconday server of my Availability Group (consists of 2 servers only) the databases are not accessible as you can see on the picture below.

Answer 1

This happens because the procedure has parameters and the procedure is also called from a job step.

When you execute the query outside of the job context (ssms), it should execute just fine. The exact reason why this happens is still unclear to me, I tried getting the commands through the profiler and recreate it in my query window, but no dice there.

However, I do know that when the agent executes job steps, NON-Default behaviour can happen. An example of this is the fact that quoted_identifier gets turned off inside the job step context:

From Microsoft:

SET QUOTED_IDENTIFIER is ON (default)

Source

Anyhow, the workaround I used for your problem is putting it in dynamic SQL.

declare @reportYear int, @reportMonth int, @reportMonthPrevious int,@sql varchar(max)

SELECT @reportYear  = CASE WHEN month(getdate()) = 1 THEN year(getdate()) - 1 ELSE year(getdate()) END, 
       @reportMonth = CASE WHEN month(getdate()) = 1 THEN 12 ELSE month(getdate()) - 1 END

SELECT @reportMonthPrevious  = @reportMonth - 1

If sys.fn_hadr_is_primary_replica ('JUNOReporting') =1  
begin
    -- this server is the primary replica, do something here
    print 'yes, primary'

            IF @reportMonthPrevious >= 1
            BEGIN
               set @sql = 'EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth '+ cast(@reportYear as varchar(20)) +', '+ cast(@reportMonthPrevious as varchar(20))
               exec(@sql)
            END
                set @sql = 'EXEC JUNOReporting.dbo.usp_ins_feesDuePaid_ForMonth '+ cast(@reportYear as varchar(20)) +', '+ cast(@reportMonth as varchar(20))
                exec(@sql)
END

Answer 2

I know this is waaay old but instead of doing the above (aka all-in-one step) why not simply create a Step 1 that all it does is check if the Job is the primary replica and if it is not then fail with success otherwise advance to the next step which is whatever the job is meant to do normally.

This has two advantages,

1. You do not have to get funky with your code for a specific job step and
2. If later on you want to skip that process to just test some change to the job you and have it start at step 2 instead of step 1