2

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.

enter image description here

  • I don't think the parameters are the issue, although I'm not sure what is tbh. What database is the job set to run in? – LowlyDBA Nov 9 '18 at 16:39
  • This has nothing to do with whether parameters are passed or not. It has to do with what the procedure does. The reporting procedure will work because it is reading data. The others won't because they are trying to write data. You can't write to a secondary. – Aaron Bertrand Nov 9 '18 at 17:12
0

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:

enter image description here

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
0

I learned from my mistakes and found the true culprit of this issue.

The culprit is

sys.fn_hadr_is_primary_replica

that accesses the database in question.

A colleague of mine gave me a better function to work with, checking the AG instead of the DB.

CREATE FUNCTION [dbo].[fn_hadr_group_is_primary] (@AGName sysname)

RETURNS bit

AS

        BEGIN



                DECLARE @PrimaryReplica sysname;



                SELECT @PrimaryReplica = hags.primary_replica

                FROM

                        sys.dm_hadr_availability_group_states hags

                        INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id

                WHERE

                        ag.name = @AGName;



                IF UPPER(@PrimaryReplica) =  UPPER(@@SERVERNAME)

                        RETURN 1; -- primary



                RETURN 0; -- not primary



        END;



GO

And then calling the function in a job step with:

IF [dbo].[fn_hadr_group_is_primary] 'AG1' = 1
BEGIN
EXEC DBO.TestProc @dbname = 'test'
END

Which no longer gave the same issue on my job that calls a procedure, even with parameters.

While the sql agent still has some non-default settings, in this case the workaround is not accessing the db with the AG check, but the availability group instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.