1

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?

  • 3
    I suggest you use the maintenance plans of OIa hallengren. It is used by 99% of the dba's in the world. ola.hallengren.com – Stijn Wynants Aug 4 '17 at 14:04
  • Please add your Always On AG configuration to the question. – SqlWorldWide Aug 4 '17 at 15:02
  • 1
    Are you sure that was your current primary at the time? – Sean Gallardy Aug 4 '17 at 15:32
  • 1
    99%? Really? Hyperbole much? – Nic Aug 7 '17 at 18:09
2

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://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-hadr-is-primary-replica-transact-sql

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.