A notable feature released in Backup Exec 21.1 release is support for SQL Server’s Always On Availability Groups. Backup Exec can backup and restore databases that are part of Microsoft SQL Server Always On Availability Groups.
The backups will adhere to the backup settings defined in the Microsoft SQL Server Availability Group properties section by database administrators.
What is Microsoft SQL Server Always On Availability Group?
Microsoft SQL Always On Availability Groups is the answer for both High Availability (HA) and Disaster Recovery (DR). It provides mirroring functionality and automatic failover for HA and replication to remote sites to achieve DR requirements.
Always On Availability Groups—fondly known as AG—can also be used as a load balancing mechanism to reduce your primary database load.
Environment Details
The following image shows an overview with SQL AG achieving both High Availability and Disaster Recovery.
- SQL AG environment typically consists of one to eight secondary replicas. Each node participating in the cluster can act as a Primary node for an Availability Group and act as a secondary replica for another.
- Applications accessing databases that are part of an Availability Group need to address them through a dedicated DNS name, referred to as a ‘SQL Listener for Availability Group’. SQL AG configuration allows only one listener per availability group.
- Availability Group configuration also allows for both synchronous and asynchronous replication between primary and secondary replicas.
Backup specific settings in Microsoft SQL AG
- SQL AG offers the following backup preferences
- Prefer Secondary
- Secondary only
- Primary
- Any Replica
- Backup Priority
- Database Admin can prioritize the order in which the replicas should be contacted for backup.
- Exclude replica from backup
- Database Admin can mark any replica node as ‘Exclude’ from backup. It is helpful to mark remote site replicas to be excluded from backups.
Protecting SQL AG database with Backup Exec
You can now protect the SQL AG database with Backup Exec. Backup Exec honors the backup specific settings configured by database administrators.
Features of SQL AG protection in Backup Exec
- During a backup job, Backup Exec will connect to the SQL AG Listener to retrieve the backup preferences for this Availability Group. This setting will select the node used for backup.
- If the failover happens and primary replica changes, Backup Exec handles it automatically. No change in the backup job is needed.
- Backup Exec will read the ‘Backup Priority’ and will attempt to backup from the readable replica which is set at the highest priority.
- If any secondary replica is configured non-readable, then Backup Exec will look for the next readable replica in the environment.
- Backup Exec will not attempt to back up from any secondary replica which is configured to be excluded from backup.
- The Backup Exec Job log will contain information about which replica node was selected for backup and the reason for its selection.
How to add SQL AG resource to Backup Exec
- Add the SQL AG listener to Backup Exec through the Add Server wizard.
- It will detect all nodes hosting replicas for the specified availability group and initiate a remote agent push installation.
- The SQL AG Listener will appear as a Server in the Backup and Restore section of Backup Exec’s interface.
- You can now proceed to configure the backup job by selecting the specific databases or the entire node.
