Post reply

SQL Server 2014 maintenance plan backups

  • March 10, 2015 at 5:31 am

    #398153

    We are planning to migrate from a SQL Server 2008 R2 Active\Active two node cluster to a SQL Server 2014 two node cluster using Availability Groups. I have been told that we have two choices for backups. We can use DPM or SQL Server 2014 Maintenance plans. I was told if we go with Maintenance Plans.... we will need the same plans on both nodes and one node (the secondary) will always log failures.... is this true? We have used SQL Server maintenance plans for 10 years on our Failover Cluster Instances and we have never had any issues. I am very new to Availability Groups and would greatly appreciate any and all advice/information.

    Thank you in advance,

    Charlie

  • March 10, 2015 at 6:42 am

    #1782866

    Create backup jobs on both the nodes and add tsql code to check for the primary replica as first step before the maintenance plan step .

    If node = primary then step1 succeeds goes to Step 2 to execute the plan otherwise step1 fails which inturn fails the job.

  • March 10, 2015 at 6:51 am

    #1782873

    Thank you... this information is very helpful. I will discuss with my manager at my One on One today.

  • March 10, 2015 at 8:18 am

    #1782937

    rummings (3/10/2015)

    We are planning to migrate from a SQL Server 2008 R2 Active\Active two node cluster to a SQL Server 2014 two node cluster using Availability Groups. I have been told that we have two choices for backups. We can use DPM or SQL Server 2014 Maintenance plans. I was told if we go with Maintenance Plans.... we will need the same plans on both nodes and one node (the secondary) will always log failures.... is this true? We have used SQL Server maintenance plans for 10 years on our Failover Cluster Instances and we have never had any issues. I am very new to Availability Groups and would greatly appreciate any and all advice/information.

    Thank you in advance,

    Charlie

    I would use DPM if you have it available. For backup and recovery I have very rarely seen anything more capable.

  • March 10, 2015 at 9:21 am

    #1782969

    1)You mean you trying to migrate SQL Cluster 2008 Active\Active to SQL Cluster 2012/2014 with Availability group ?

    2) for maintenance plan Please follow the link. This might clear your doubts.

    http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/02/maintenance-plan-does-not-backup-database-or-log-of-database-that-belongs-to-availability-group.aspx

  • March 11, 2015 at 3:20 pm

    #1783453

    I use the maintenance solution from Ola

    https://ola.hallengren.com/

    For me, the best solution I've found.

    Supports HAG!

  • March 16, 2015 at 1:48 pm

    #1784342

    I tried to use SSMS 2014 maintenance plans to backup databases I am testing in an Availability group. I am failing badly.

    I have the Availability Groups set to "Prefer Secondary" on both the primary and the secondary. I created the same Full Backup Maintenance plan on both the Primary and Replica nodes. I tried executing the maintenance plans from both nodes.... they fail on both. I looked in the Server Log and see this message:

    From Primary

    ========

    BackupDiskFile::OpenMedia: Backup device 'J:\Backup\forms\forms_backup_2015_03_16_154013_0667087.bak' failed to open. Operating system error 2(The system cannot find the file specified.).

    From Secondary

    ==========

    BACKUP failed to complete the command BACKUP DATABASE forms. Check the backup application log for detailed messages.

    The Oracle guys feel you need to write a script and it cannot ne performed from SSMS.... has anyone completed this process. If so, please let me know what I am missing.

    Thanks!

    Charlie

Viewing 7 posts - 1 through 6 (of 6 total)

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