Trabalhos e grupos de disponibilidade do SQL Server Agent

Em seu trabalho do SQL Server Agent, tenha alguma lógica condicional para testar se a instância atual está atendendo à função específica que você está procurando em seu grupo de disponibilidade:

if (select
        ars.role_desc
    from sys.dm_hadr_availability_replica_states ars
    inner join sys.availability_groups ag
    on ars.group_id = ag.group_id
    where ag.name = 'YourAvailabilityGroupName'
    and ars.is_local = 1) = 'PRIMARY'
begin
    -- this server is the primary replica, do something here
end
else
begin
    -- this server is not the primary replica, (optional) do something here
end

Tudo o que isso faz é puxar a função atual da réplica local e, se estiver na PRIMARYfunção, você pode fazer o que seu trabalho precisa fazer se for a réplica primária. O ELSEbloco é opcional, mas é para lidar com a lógica possível se sua réplica local não for primária.

Obviamente, altere 'YourAvailabilityGroupName'a consulta acima para o nome real do seu grupo de disponibilidade.

Não confunda grupos de disponibilidade com instâncias de cluster de failover. O fato de a instância ser a réplica primária ou secundária de um determinado grupo de disponibilidade não afeta os objetos no nível do servidor, como trabalhos do SQL Server Agent e assim por diante.

Em vez de fazer isso por trabalho (verificando cada trabalho quanto ao estado do servidor antes de decidir continuar), criei um trabalho em execução nos dois servidores para verificar em que estado o servidor está.

• Se for primário, habilite qualquer trabalho que tenha uma etapa direcionada a um banco de dados no AG.
• Se o servidor for secundário, desabilite qualquer trabalho direcionado a um banco de dados no AG.

Esta abordagem fornece uma série de coisas

• ele funciona em servidores onde não há bancos de dados em AG (ou uma mistura de Db's in/out of AGs)
• qualquer um pode criar um novo trabalho e não precisa se preocupar se o banco de dados está em um AG (embora tenha que lembrar de adicionar o trabalho ao outro servidor)
• Permite que cada trabalho tenha um e-mail de falha que permanece útil (todos os seus trabalhos têm e-mails de falha, certo?)
• Ao visualizar o histórico de um trabalho, você realmente consegue ver se o trabalho realmente foi executado e fez algo (sendo o principal), em vez de ver uma longa lista de sucessos que realmente não executou nada (no secundário)

o script verifica o banco de dados no campo abaixo

Este proc é executado a cada 15 minutos em cada servidor. (tem o bônus adicional de anexar um comentário para informar às pessoas por que o trabalho foi desativado)

/*
    This proc goes through all SQL Server agent jobs and finds any that refer to a database taking part in the availability Group 
    It will then enable/disable the job dependant on whether the server is the primary replica or not   
        Primary Replica = enable job
    It will also add a comment to the job indicating the job was updated by this proc
*/
CREATE PROCEDURE dbo.sp_HADRAgentJobFailover (@AGname varchar(200) = 'AG01' )
AS 

DECLARE @SQL NVARCHAR(MAX)

;WITH DBinAG AS (  -- This finds all databases in the AG and determines whether Jobs targeting these DB's should be turned on (which is the same for all db's in the AG)
SELECT  distinct
        runJobs = CASE WHEN role_desc = 'Primary' THEN 1 ELSE 0 END   --If this is the primary, then yes we want to run the jobs
        ,dbname = db.name
        ,JobDescription = CASE WHEN hars.role_desc = 'Primary'  -- Add the reason for the changing the state to the Jobs description
                THEN '~~~ [Enabled] using automated process (DBA_tools.dbo.sp_HADRAgentJobFailover) looking for jobs running against Primary Replica AG ~~~ '
                ELSE '~~~ [Diabled] using Automated process (DBA_tools.dbo.sp_HADRAgentJobFailover) because the job cant run on READ-ONLY Replica AG~~~ ' END 
FROM sys.dm_hadr_availability_replica_states hars
INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id
INNER JOIN sys.Databases db ON  db.replica_id = hars.replica_id
WHERE is_local = 1
AND ag.Name = @AGname
) 

SELECT @SQL = (
SELECT DISTINCT N'exec msdb..sp_update_job @job_name = ''' + j.name + ''', @enabled = ' + CAST(d.runJobs AS VARCHAR) 
                + ',@description = ''' 
                + CASE WHEN j.description = 'No description available.' THEN JobDescription -- if there is no description just add our JobDescription
                       WHEN PATINDEX('%~~~%~~~',j.description) = 0 THEN j.description + '    ' + JobDescription  -- If our JobDescription is NOT there, add it
                       WHEN PATINDEX('%~~~%~~~',j.description) > 0 THEN SUBSTRING(j.description,1,CHARINDEX('~~~',j.description)-1) + d.JobDescription  --Replace our part of the job description with what we are doing.
                       ELSE d.JobDescription  -- Should never reach here...
                    END 
                + ''';'
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s
INNER JOIN DBinAG d ON d.DbName =s.database_name     
ON j.job_id = s.job_id
WHERE j.enabled != d.runJobs   -- Ensure we only actually update the job, if it needs to change
FOR XML PATH ('')
)
PRINT REPLACE(@SQL,';',CHAR(10))
EXEC sys.sp_executesql @SQL

Não é infalível, mas para cargas noturnas e trabalhos por hora, ele faz o trabalho.

Ainda melhor do que ter esse procedimento executado em um agendamento, execute-o em resposta ao Alerta 1480 (alerta de alteração de função AG).

Estou ciente de dois conceitos para conseguir isso.

Pré-requisito: Com base na resposta de Thomas Stringer, criei duas funções no banco de dados mestre de nossos dois servidores:

CREATE FUNCTION [dbo].[svf_AgReplicaState](@availability_group_name sysname)
RETURNS bit
AS
BEGIN

if EXISTS(
    SELECT        ag.name
    FROM            sys.dm_hadr_availability_replica_states AS ars INNER JOIN
                             sys.availability_groups AS ag ON ars.group_id = ag.group_id
    WHERE        (ars.is_local = 1) AND (ars.role_desc = 'PRIMARY') AND (ag.name = @availability_group_name))

    RETURN 1

RETURN 0

END
GO

CREATE FUNCTION [dbo].[svf_DbReplicaState](@database_name sysname)
RETURNS bit
AS
BEGIN

IF EXISTS(
    SELECT        adc.database_name
    FROM            sys.dm_hadr_availability_replica_states AS ars INNER JOIN
                             sys.availability_databases_cluster AS adc ON ars.group_id = adc.group_id
    WHERE        (ars.is_local = 1) AND (ars.role_desc = 'PRIMARY') AND (adc.database_name = @database_name))

    RETURN 1
RETURN 0

END

GO

1. Fazer um trabalho terminar se não for executado na réplica primária

   Para este caso, cada trabalho em ambos os servidores precisa de um dos dois trechos de código a seguir como Etapa 1:

   Verifique pelo nome do grupo:

   IF master.dbo.svf_AgReplicaState('my_group_name')=0
     raiserror ('This is not the primary replica.',2,1)
   

   Verifique pelo nome do banco de dados:

   IF master.dbo.svf_AgReplicaState('my_db_name')=0
     raiserror ('This is not the primary replica.',2,1)
   

   Se você usar este segundo, tenha cuidado com os bancos de dados do sistema - por definição, eles não podem fazer parte de nenhum grupo de disponibilidade, portanto, sempre falhará para eles.

   Ambos funcionam fora da caixa para usuários administradores. Para usuários não administradores, você deve adicionar permissões extras, uma delas sugerida aqui :

   GRANT VIEW SERVER STATE TO [user];
   GRANT VIEW ANY DEFINITION TO [user];
   

   Se você definir a ação de falha para Sair do relatório de trabalho com sucesso nesta primeira etapa, você não obterá o registro de trabalho cheio de sinais de cruz vermelhos feios, para o trabalho principal eles se transformarão em sinais de aviso amarelos.

   De nossa experiência, isso não é o ideal. A princípio, adotamos essa abordagem, mas rapidamente perdemos a noção de encontrar trabalhos que realmente tinham um problema, porque todos os trabalhos de réplica secundárias enchiam o log de trabalhos com mensagens de aviso.

   O que nós fomos então é:

2. Trabalhos de proxy

   Se você adotar esse conceito, na verdade precisará criar dois trabalhos por tarefa que deseja executar. O primeiro é o "trabalho de proxy" que verifica se está sendo executado na réplica primária. Em caso afirmativo, ele inicia o "trabalho de trabalho", caso contrário, ele termina normalmente sem sobrecarregar o log com mensagens de aviso ou de erro.

   Embora eu pessoalmente não goste da ideia de ter dois trabalhos por tarefa em cada servidor, acho que é definitivamente mais sustentável e você não precisa definir a ação de falha da etapa para Quit job reporting success , o que é um pouco desajeitado.

   Para os trabalhos, adotamos um esquema de nomenclatura. O trabalho de proxy é apenas chamado {put jobname here}. O trabalho do trabalhador é chamado {put jobname here} worker. Isso possibilita automatizar o início do trabalho do trabalhador a partir do proxy. Para fazer isso, adicionei o seguinte procedimento a ambos os dbs mestres:

   CREATE procedure [dbo].[procStartWorkerJob](@jobId uniqueidentifier, @availabilityGroup sysname, @postfix sysname = ' worker') as
   declare @name sysname
   
   if dbo.svf_AgReplicaState(@availabilityGroup)=0
       print 'This is not the primary replica.'
   else begin
       SELECT @name = name FROM msdb.dbo.sysjobs where job_id = @jobId
   
       set @name = @name + @postfix
       if exists(select name from msdb.dbo.sysjobs where name = @name)
           exec msdb.dbo.sp_start_job @name
       else begin
           set @name = 'Job '''+@name+''' not found.'
           raiserror (@name ,2,1)
       end
   end
   GO
   

   Isso utiliza a svf_AgReplicaStatefunção mostrada acima, você pode facilmente alterar isso para verificar usando o nome do banco de dados chamando a outra função.

   De dentro da única etapa do trabalho de proxy, você o chama assim:

   exec procStartWorkerJob $(ESCAPE_NONE(JOBID)), '{my_group_name}'
   

   Isso utiliza Tokens conforme mostrado aqui e aqui para obter o id do trabalho atual. O procedimento então obtém o nome do trabalho atual do msdb, anexa  workera ele e inicia o trabalho do trabalhador usando sp_start_job.

   Embora isso ainda não seja o ideal, mantém os logs de trabalho mais organizados e fáceis de manter do que a opção anterior. Além disso, você sempre pode executar o trabalho de proxy com um usuário sysadmin, portanto, não é necessário adicionar permissões extras.

Se o processo de carregamento de dados for uma consulta simples ou chamada de procedimento, você poderá criar o trabalho em ambos os nós e deixá-lo determinar se é o nó primário com base na propriedade Updateability do banco de dados, antes de executar o processo de carregamento de dados:

IF (SELECT CONVERT(sysname,DatabasePropertyEx(DB_NAME(),'Updateability'))) != 'READ_ONLY'
BEGIN

-- Data Load code goes under here

END

Outra forma é inserir um passo em cada job, que deve ser executado primeiro, com o seguinte código:

IF (SELECT ars.role_desc
    FROM sys.dm_hadr_availability_replica_states ars
    INNER JOIN sys.availability_groups ag
    ON ars.group_id = ag.group_id
    AND ars.is_local = 1) <> 'PRIMARY'
BEGIN
   --We're on the secondary node, throw an error
   THROW 50001, 'Unable to execute job on secondary node',1
END

Defina esta etapa para continuar com a próxima etapa em caso de sucesso e para encerrar o trabalho relatando sucesso em caso de falha.

I find it cleaner to add an extra step instead of adding extra logic to an existing step.

It is always better to create a new Job Step which checks if it is a Primary Replica then everything is fine to continue with the job execution else if it is a Secondary Replica then Stop the job. Do not fail the job else it will keep sending unnecessary notifications. Instead stop the job so that it is cancelled and no notifications are sent out whenever these jobs are executed on the Secondary Replica.

Below is the script to add a first step for a specific job.

Note to execute the script:

• Replace 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' with Job_ID
• Replace 'YYYYYYYYYYYYYYYYYYYYYYYYYY' with Job_Name

• If there are multiple Availability Groups, then set the AG name in the variable @AGNameToCheck_IfMoreThanSingleAG as to which AG should be checked for its replica state.

• Also note that this script should work well even on those servers which do not have availability groups. Will execute only for SQL Server versions 2012 and beyond.

          USE [msdb]
          GO
          EXEC msdb.dbo.sp_add_jobstep @job_id=N'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX', @step_name=N'CheckForSecondaryReplica', 
                  @step_id=1, 
                  @cmdexec_success_code=0, 
                  @on_success_action=3, 
                  @on_fail_action=2, 
                  @retry_attempts=0, 
                  @retry_interval=0, 
                  @os_run_priority=0, @subsystem=N'TSQL', 
                  @command=N'
          DECLARE @AGNameToCheck_IfMoreThanSingleAG VARCHAR(100)
          SET @AGNameToCheck_IfMoreThanSingleAG = ''AGName_IfMoreThanOneAG'' -- If there are Multiple AGs, then a single server can have Primary of one AG and Secondary of other. So Job creator has to define as to which AG needs to verified before the job is automatically run on Primary.
  
          DECLARE @NumberofAGs INT
          SELECT @NumberofAGs = COUNT(group_id) FROM sys.availability_groups ags
  
  
          IF(@NumberofAGs < 2)
              IF EXISTS(Select * FROM sys.dm_hadr_availability_replica_states hars WHERE role_desc = ''Secondary'' AND hars.is_local = 1)                 
                                  EXEC msdb.dbo.sp_stop_job N''YYYYYYYYYYYYYYYYYYYYYYYYYY'' ;
                                  --RAISERROR(''This is a Secondary Replica'',16,1)
  
          IF(@NumberofAGs >= 2)
              IF EXISTS(SELECT 1 FROM sys.availability_groups WHERE name = @AGNameToCheck_IfMoreThanSingleAG)
              BEGIN
                          IF EXISTS(Select * from  sys.availability_groups ag
                                          JOIN sys.dm_hadr_availability_replica_states hars
                                                      ON ag.group_id = hars.group_id
                                                      Where role_desc = ''Secondary''
                                                      AND hars.is_local = 1
                                                      AND ag.name = @AGNameToCheck_IfMoreThanSingleAG)
                          BEGIN
                                  EXEC msdb.dbo.sp_stop_job N''YYYYYYYYYYYYYYYYYYYYYYYYYY'' ;
                                  --RAISERROR(''This is a Secondary Replica'',16,1)
                          END
              END
              ELSE
                          BEGIN
                                  RAISERROR(''The Defined AG in the Variable is not a part of this Server. Please Check!!!!!!!!!!!'',16,1)
                          END', 
                  @database_name=N'master', 
                  @flags=0
          GO
  

Another, newer option, is using master.sys.fn_hadr_is_primary_replica('DbName'). I have found this super helpful when using SQL Agent to do database maintenance (coupled with a cursor I've used for years) and also when executing an ETL or other database specific task. The benefit is that it singles out the database instead of looking at the whole Availability Group...if that's what you need. It also makes it much more improbable that a command will be executed against a database that "was" on the primary, but let's say an automatic failover happened during the job execution, and it is now on a secondary replica. The above methods that look at the primary replica take one look and don't update. Keep in mind, this is just a different way to achieve very similar results and give more granular control, if you need it. Also, the reason this method wasn't discussed when this question was asked is because Microsoft didn't release this function until after SQL 2014 was released. Below are some samples of how this function can be used:

   IF master.dbo.fn_hadr_database_is_primary_replica('Admin') = 1
    BEGIN 
        -- do whatever you were going to do in the Primary:
        PRINT 'Doing stuff in the Primary Replica';
    END
ELSE 
    BEGIN 
        -- we're not in the Primary - exit gracefully:
        PRINT 'This is not the primary replica - exiting with success';
    END

Se você quiser usar isso para manutenção do banco de dados do usuário, é isso que eu uso:

/*Below evaluates all user databases in the instance and gives stubs to do work; must change to get anything other than print statements*/
declare @dbname varchar(1000)
declare @sql nvarchar(4000)

declare AllUserDatabases cursor for
    select [name] from master.sys.databases
    where database_id > 4 --this excludes all sysdbs; if all but tempdb is desired, change to <> 2
    and [state] = 0

open AllUserDatabases
fetch AllUserDatabases into @dbname

while (@@FETCH_STATUS = 0)
    begin
    --PRINT @dbname
        set @sql = '
            IF master.sys.fn_hadr_is_primary_replica(''' + @dbname + ''') = 1
                BEGIN 
                    -- do whatever you are going to do in the Primary:
                    PRINT ''Doing stuff in the Primary Replica''
                END
            ELSE 
                BEGIN 
                    -- not in the Primary - exit gracefully:
                    PRINT ''This is not the primary replica - exiting with success''
                END             
        '
        exec sp_executesql @sql
        fetch AllUserDatabases into @dbname
    end
close AllUserDatabases
deallocate AllUserDatabases

Espero que seja uma dica útil!

Eu uso isso:

if (select primary_replica from sys.dm_hadr_availability_group_states) = @@SERVERNAME begin
... paste your t-sql here ...

end