SQL Server AlwaysOn High Availability Maintenance Plans

Question

I have a SQL Server AlwaysOn High Availability with Primary & Secondary Database Servers

The Availability Group Backup Preferences is set to Primary.

Maintenance Plan is on Primary and does:

• Check Database Integrity - Include Indexes, Physical Only
• Backup Databases - Full
• Backup Databases - Transaction Logs
• Rebuild Indexes - Tables and views
• Maintenance Cleanup - Delete files older than 2 weeks

No errors are reported in the plan history but I find that only databases that do not participate in the Availability Group are backed up. There seems to be scant resources on maintenance plans on High Availability environments.

Any advice how I can get maintenance plan to work on the databases in the High Availability group?

Answer 1

The best way is to use Ola Hallengren's maintenance plans, because the ability to determine AGs in the maintenance is already "baked in". However, if for some reason you don't want to or can't use his maintenance plans, then you'll need to insert the deterministic function, If sys.fn_hadr_is_primary_replica ( @dbname ) <> 1
BEGIN
-- If this is not the primary replica, exit (probably without error).
END
-- If this is the primary replica, continue to do the backup., to figure out if the database resides in the primary node or not. The example comes from: https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-hadr-is-primary-replica-transact-sql

Answer 2

We found we had to use a SQL Agent job wrapper that checked if the job was being ran on the primary or the secondary. If on the primary, than the wrapper would call the named job associated with the maintenance plan.

If you want your AAG databases backed up separately from the NON-AAG databases, you need need a different sub-task in the maintenance plan that backups the NON-AAG databases, which in turn creates a separate job in SQL Agent.