Backup fails on forwarder in Distributed Availability Group

[fkoene]

We're (almost) using Distributed Availability Groups. The backup commands fail on the forwarder, the primary server of the secondary availability group. Though it's role is primary, the databases in the availability group are (at least in our case) inaccessible which causes SET @CurrentCommand07 to fail. I've worked around it a bit, don't need the forwarder to backup databases so added @CurrentServerIsForwarder and used that somewhere in the "AND NOT" list around line number 1972. For me I think it's okay this way, but it's not very flexible this way I think.

Be this as it may, thanks for the MaintenanceSolution as such.

Regards,

Fred Koene

SQL Server version and edition Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64)

Version of the script Version: 2019-01-06 19:21:46

What command are you executing? DatabaseBackup

What output are you getting? Database is not accessible, part of availability group (don't have the exact sentence anymore)

[olahallengren]

@fkoene, could you share the code that you are using to populate @CurrentServerIsForwarder?

[fkoene]

Dear Ola,

I've written an UDF which in our environment makes it clear that a peticular server is the primary of the distributed availability group. Obviously when it returns zero and the other function (deciding whether or not it's a primary of an Availability Group) returns 1 you can only decide it's a Forwarder when you know there's a DAG active in your environment. In a ordinary AG it will also return 0, which is as expected. On the other hand, when there's an entry with is_distributed = 1 in sys.availability_groups and you know it's the primary server of a local Availability Group you can be pretty sure I think that it's a Forwarder.

In our new environment the issue of the backup job of the maintenance solution is no longer applicable. We use CommVault now. Which also doesn't recognize a Forwarder yet by the way. In order to avoid messages about failing back-ups we have to manually disable the back-ups on the secondary site. Which is in our case no rpoblem, a site failover will always be done manually, so we will always know that we have to enable or disable a particular site.

Down below are the scripts for both UDF's, the one to check whether it's a AG primary and one to check whether it's a DAG primary.

Kind regards,

Fred Koene

USE [beheer] GO

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE FUNCTION [dbo].[udf_check_is_primary_ag] () RETURNS BIT AS BEGIN DECLARE @is_primary_ag SYSNAME;

SELECT @is_primary_ag = rs.role_desc FROM sys.dm_hadr_availability_replica_states rs INNER JOIN sys.availability_groups ag ON rs.group_id = ag.group_id INNER JOIN sys.availability_replicas ar ON rs.replica_id = ar.replica_id WHERE rs.role_desc = 'PRIMARY'

IF @is_primary_ag = 'PRIMARY' RETURN 1;

RETURN 0;

END; GO

USE [beheer] GO

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE FUNCTION [dbo].[udf_check_is_primary_dag] () RETURNS BIT AS BEGIN DECLARE @is_primary_dag SYSNAME;

SELECT @is_primary_dag = rs.role_desc FROM sys.dm_hadr_availability_replica_states rs INNER JOIN sys.availability_groups ag ON rs.group_id = ag.group_id INNER JOIN sys.availability_replicas ar ON rs.replica_id = ar.replica_id WHERE ag.is_distributed = 1 AND rs.role_desc = 'PRIMARY'

IF @is_primary_dag = 'PRIMARY' RETURN 1;

RETURN 0;

END; GO

Ola Hallengren schreef op 2020-12-05 23:50:

@fkoene [1], could you share the code that you are using to populate @CurrentServerIsForwarder?

-- You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub [2], or unsubscribe [3].

Links:

[1] https://github.com/fkoene [2] https://github.com/olahallengren/sql-server-maintenance-solution/issues/384#issuecomment-739426734 [3] https://github.com/notifications/unsubscribe-auth/ANDDK2PWOTWQTU5POIZDTTDSTK2L5ANCNFSM4NBLXF2A

[CommanderBond]

I confirm that

SELECT @is_primary_dag = rs.role_desc FROM sys.dm_hadr_availability_replica_states rs INNER JOIN sys.availability_groups ag ON rs.group_id = ag.group_id INNER JOIN sys.availability_replicas ar ON rs.replica_id = ar.replica_id WHERE ag.is_distributed = 1 AND rs.role_desc = 'PRIMARY'

results in "PRIMARY" on global primary but nowhere else in a Distributed AG environment, i.e. it works in both AGs: On all replicas of primary AG and all replicas of secondary AG.