locked
SQL 2016 maintenance plan backup AlwaysOn database problem RRS feed

  • Question

  • 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!

    Friday, April 7, 2017 2:11 AM

Answers

  • Quickly update. Just figured out.

    Readable secondary has been configured for read-intent only at time of read-only routing setup. After changing readable secondary to Yes problem disappeared.


    Friday, April 7, 2017 2:26 AM