SQL Server代理作业和可用性组

#1 楼

在您的SQL Server代理作业中，有一些条件逻辑可以测试当前实例是否正在您正在可用性组上寻找的特定角色：

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

全部这确实是拉本地副本的当前角色，并且如果它是PRIMARY角色，则如果它是主副本，则您可以执行工作需要执行的所有操作。 ELSE块是可选的，但是如果您的本地副本不是主要副本，它可以处理可能的逻辑。

当然，请将上述查询中的'YourAvailabilityGroupName'更改为您的实际可用性组名称。

不要将可用性组与故障转移群集实例混淆。实例是给定可用性组的主要副本还是辅助副本都不会影响服务器级对象，例如SQL Server代理作业等。

#2 楼

我不是在每个作业的基础上执行此操作（在决定继续之前检查每个作业的服务器状态），而是在两个服务器上创建了一个作业，以检查服务器处于什么状态。


如果是主要作业，则启用任何具有针对AG中数据库的步骤的作业。
如果服务器是辅助服务器，则在AG中禁用针对数据库的任何作业。

这种方法提供了许多功能


它可以在AG中没有数据库的服务器（或Db进/出AG的混合）
任何人都可以创建新作业，而不必担心数据库是否在AG中（尽管他们确实要记住将作业添加到其他服务器）
允许每个作业都有一封仍然有用的失败电子邮件（您的所有作业都有失败电子邮件吗？）
查看作业的历史记录时，实际上查看作业是否真正运行并执行了某些操作（这是主要操作），而不是看到一长串实际上没有执行任何操作的成功列表（在次要操作上）

脚本检查数据库中的以下字段


此proc在每台服务器上每15分钟执行一次。 （具有添加注释以告知人们为什么该工作被禁用的额外好处）。

/*
    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

它不是万无一失的，但是对于过夜的工作和每小时的工作，它可以完成工作。

比按计划运行此过程要好，而应根据警报1480（AG角色更改警报）运行它。

#3 楼

我知道实现此目的的两个概念。

先决条件：基于Thomas Stringer的回答，我在两个服务器的主数据库中创建了两个函数：

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：

按组名检查：

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

按数据库名检查：

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

如果您使用第二个数据库，请提防系统数据库-根据定义，它们不能成为任何可用性组的一部分，因此对于那些可用性组来说，它总是会失败的。

管理员用户可以直接使用这两种功能。对于非管理员用户，您必须添加额外的权限，此处建议其中之一：

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

如果将失败操作设置为“退出此作业报告成功”第一步，您不会获得充满丑陋的红色十字标志的作业日志，对于主要作业，它们将变成黄色警告标志。

根据我们的经验，这并不理想。我们最初采用这种方法，但很快就找不到真正有问题的作业，因为所有辅助副本作业都在警告信息中打乱了作业日志。

我们追求的是：


代理作业

如果采用此概念，则实际上需要为要执行的每个任务创建两个作业。第一个是“代理作业”，用于检查它是否正在主副本上执行。如果是这样，它将启动“工人工作”，如果不是，它将正常结束而不会在日志中显示警告或错误消息。

我个人不喜欢每个工人有两个工作的想法。每个服务器上的任务，我认为它显然更加可维护，您不必将步骤的失败操作设置为“退出作业报告成功”，这有点尴尬。

对于工作，我们采用了命名方案。代理作业仅称为{put jobname here}。工人的工作称为{put jobname here} worker。这使从代理服务器自动启动工作者作业成为可能。为此，我向两个主数据库添加了以下过程：

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

这利用了上面显示的svf_AgReplicaState函数，您可以轻松地将其更改为使用数据库名称，而不是调用其他函数。

在代理作业的唯一步骤中，您可以这样命名：

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

它利用此处和此处所示的令牌来获取当前作业的ID。然后，该过程从msdb获取当前作业名称，将其附加 worker并使用sp_start_job启动辅助作业。

虽然这仍然不理想，但它使作业日志比上一个更整洁和可维护选项。另外，您始终可以使代理作业与sysadmin用户一起运行，因此不需要添加任何额外的权限。

#4 楼

如果数据加载过程是一个简单的查询或过程调用，那么您可以在执行数据加载过程之前，在两个节点上创建作业，并使其基于数据库的Updateability属性确定其主节点是否为主要节点：

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

-- Data Load code goes under here

END

#5 楼

另一种方法是在每个作业中插入一个步骤，该步骤应首先运行，并使用以下代码：

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

设置此步骤以继续执行成功的下一步，并在失败时退出报告成功的作业。

我发现添加一个额外的步骤而不是在现有步骤中添加额外的逻辑会更干净。

#6 楼

始终最好创建一个新的作业步骤，以检查它是否是主副本，然后一切都可以继续执行该作业，否则，如果它是第二副本，则停止该作业。不要使作业失败，否则它将继续发送不必要的通知。而是停止该作业，以便取消该作业，并且在辅助副本服务器上执行这些作业时，不会发送任何通知。

下面是为特定作业添加第一步的脚本。

执行脚本的注意事项：


用Job_ID替换'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
用Job_Name替换'YYYYYYYYYYYYYYYYYYYYYYYYYY'
如果有多个可用性组，然后在变量@AGNameToCheck_IfMoreThanSingleAG中设置AG名称，以检查应检查哪个AG的副本状态。

还要注意，即使在那些没有可用性的服务器上，此脚本也应能正常工作组。仅对SQL Server 2012及更高版本执行。

        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

#7 楼

我用这个：

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

end

#8 楼

另一个较新的选项是使用master.sys.fn_hadr_is_primary_replica（'DbName'）。当使用SQL Agent进行数据库维护（再加上我使用了多年的游标）以及执行ETL或其他特定于数据库的任务时，我发现此超级有用。好处是，它只选择数据库，而不是查看整个可用性组...如果您需要的话。这也使得对在主数据库上“存在”的数据库执行命令的可能性大大降低，但是可以说在执行作业期间发生了自动故障转移，并且该命令现在位于辅助副本上。上面查看主副本的方法只是一看而已，不会更新。请记住，这只是获得非常相似的结果并根据需要提供更精细控制的另一种方式。另外，在询问此问题时未讨论此方法的原因是因为Microsoft直到SQL 2014发布后才发布此函数。
以下是如何使用此函数的一些示例：

   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

如果要使用它进行用户数据库维护，这就是我使用的方法：

/*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

我希望这是一个有用的提示！