Maintenance Plan excludes the AlwaysON database

  • Dear Friends,

    Want to ask if its normal for the Maintenance Plans to exclude the database(s) in Always On availability Group ? I setup one and saw it works this way...Thanks in advance for ur valuable comments.

     

    Thanks and Best Regards

    Arshad

  • Are you saying that the maintenance plan is excluding databases that are part of an availability group, or it's not backing up the databases on the secondary?

    I have not used a maintenance plan for years, but I just created one on one of my AG servers. It did include the databases in the AG, but there was a warning message that it could not be run on a secondary.

    Also have you considered using something like Ola Hallengren's maintenance solution?

    https://ola.hallengren.com/

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • This was removed by the editor as SPAM

  • @michael-2 , No its not backing the Primary Up...It ignores it (Though I unchecked the option 'Ignore Databases that are  Not Online')..I get the warming you mentioned above , but it just doesn't backup the primary (Full and Trans log both).

     

    THanks...Arshad

    • This reply was modified 1 week, 3 days ago by  Arsh.
  • What did you set the backup preference as for that availability group?

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server?view=sql-server-ver15

    The maintenance plan should contain logic that uses that preference to determine where to perform the backups.  It is possible you set it to secondary only so even though it runs through the list of databases - those in the AG cannot be backed up because it is the primary node.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Arsh wrote:

    @Michael , No its not backing the Primary Up...It ignores it (Though I unchecked the option 'Ignore Databases that are  Not Online')..I get the warming you mentioned above , but it just doesn't backup the primary (Full and Trans log both).

    THanks...Arshad

     

    Dumb question.  When you created the maintenance plan, did you actually create a schedule, and did it in turn create SQL Agent jobs?

    Have you attempted to run the code within the job, which may give you exact errors?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • It backups the other databases. I asked it not to ignore any , but still leaves out the one in the Availability Group?  thank you

  • Arsh wrote:

    It backups the other databases. I asked it not to ignore any , but still leaves out the one in the Availability Group?  thank you

    So I will ask again - what is the backup preference defined for that AG?  If it is NOT set to primary only - then the maintenance plan will 'skip' any databases that exist in that AG on the primary node and instead use the MP on the secondary to perform the backups.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    Arsh wrote:

    It backups the other databases. I asked it not to ignore any , but still leaves out the one in the Availability Group?  thank you

    So I will ask again - what is the backup preference defined for that AG?  If it is NOT set to primary only - then the maintenance plan will 'skip' any databases that exist in that AG on the primary node and instead use the MP on the secondary to perform the backups.

    I agree. How is the AG set up??

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply