none
Distributed Availability Groups and Backups

    Question

  • I have set up a distributed availability group over two separate Availability groups. I have maintenance process that performs daily full backups on each server. On the primary it works perfectly, but on the forwarder we encounter an error:

    This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. [SQLSTATE 42000] (Error 3059)   BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)   BACKUP DATABASE successfully processed 2548 pages in 1.139 seconds (17.475 MB/sec). [SQLSTATE 01000] (Error 3014)   BACKUP DATABASE [xxxdb] TO DISK='\\location\xxxdb_20180710210008.BAK' WITH INIT, FORMAT, COMPRESSION [SQLSTATE 01000] (Error 0)   Complete backup of xxxdb failed [SQLSTATE 01000] (Message 50000) 

    If there a way on the forwarder to identify that this database is functioning as a secondary replica. All the information that I find regarding the distributed availability groups state that you can monitor the distributed AG on the primary AG then monitor the availability group on the secondary AG.

    Wednesday, July 11, 2018 6:54 PM

All replies

  • I think that the forwarder (primary in AG2) is acting like the same as the secondary replica on which the backup will not work unless you set the secondary replica to ready-only.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, July 11, 2018 7:07 PM
  • Actually the backup on the secondary replica can be performed, but there are some limitations. See the part of "Backup Types Supported on Secondary Replicas" in the article "Active Secondaries: Backup on Secondary Replicas (Always On Availability Groups)".


    A Fan of SSIS, SSRS and SSAS

    Wednesday, July 11, 2018 7:35 PM
  • I have set up a distributed availability group over two separate Availability groups. I have maintenance process that performs daily full backups on each server. On the primary it works perfectly, but on the forwarder we encounter an error:

    The bottom line is, the replicas that directly communicate with the global primary replica (primary replica of AG1 in your case) should allow backups. Hence, the Forwarder should actually allow the backups since it's in direct contact with the main primary replica of AG1.

    As an example, let's say you have AG1 in a two-node WSFC and AG2 in a separate two-node WSFC. You configured DAG spanning AG1 and AG2. Now, the backups should be allowed on node 1(primary) and node 2(secondary) of AG1 (WSFC 1) and also on node 1(primary) of AG2 (WSFC2) (Forwarder).

    On secondaries, FULL backups are allowed only with "COPY_ONLY" option so try the same full backup but this time add COPY_ONLY to the command and see if it works.

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Thursday, July 12, 2018 12:45 AM
  • the failure is occurring on node 1(primary) of AG2 (WSFC2) (Forwarder). The backup jobs run on a scheduled to backup both system and user database. The forwarder is readonly and is working properly. The issue is the backup process looks for databases available on server and attempts to back them up. I dont want to bypass database, since if there is a manual fail over this db is now primary and  I dont want to forget about backups. Trying to determine if there is a way to identify that this db is part of Distributed Availability Group,  but according to documentation this information is only available on primary.

    Joe Tigeleiro

    Thursday, July 12, 2018 6:44 PM
  • it is read only and cannot be updated. it is primary in its own AG which is a requirement of a Distributed Availability Group

    Joe Tigeleiro

    Thursday, July 12, 2018 6:53 PM
  • Don't you have the following function check in your backup jobs to perform backups only if the current replica is a preferred replica for backups? 

    I see that your preferred replica for backups is primary. If you incorporate this check on all replica backup jobs, the backups should then occur only on respective primary replicas i.e. primary of AG1 and primary of AG2 (forwarder). On forwarder, this check returns 1 for AG2 databases but should return 0 for AG1 databases and hence you avoid backing up AG1 databases on forwarder since they're already been backed up on primary of AG1. 

    Run this function against DBs of AG1 on forwarder and see if they return 0 or 1.

    If sys.fn_hadr_backup_is_preferred_replica( @dbname ) <> 1
    BEGIN
    -- If this is not the preferred replica, exit
    END
    -- If this is the preferred replica, continue to do the backup.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Thursday, July 12, 2018 7:52 PM