Hi all,
I'm configuring SQL backups for SQL2016 AlwaysOn availability group.
SQL version is 13.0.4422.
I have configured 2 identical maintenance plans on 2 AlwaysOn nodes and added backup database tasks. Inside these tasks I have select DBs from my AO Availability group. Availability group backup preferences property is set to 'Primary'.
I have set up same schedule for these two maintenance plans subplans.
On primary node backup should be done and on secondary node all DBs from AG should be skipped. After failover DBs should be skipped on previous primary (current secondary) and backups should be taken on new primary (previous secondary).
Now I get SQL agent job failure with following message on secondary node. Backup on primary is ok.
Code: 0xC0024104 Source: Back Up Database Task Description: The Execute method on the task returned error code 0x80131501 (Failed to connect to server (local).). The Execute method must succeed, and
indicate the result using an "out" parameter.
If I generate T-SQL from backup database task I can successfully run it on secondary and AG DBs are skipped.
SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('AG_Name'))
IF (@preferredReplica = 1)
BEGIN
BACKUP DATABASE ....
Is this a bug in SQL2016? I have already configured such backup tasks before and I have no such failures.
Does anyone know how to fix these errors?
Thanks in advance!
