SQL Server 2014 maintenance plan backups

Question

SQL Server 2014 maintenance plan backups

rummings

SSCarpal Tunnel

Points: 4575
More actions
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

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

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

sunder.bugatha Ten Centuries Points: 1227 More actions · Answer 1

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.

rummings SSCarpal Tunnel Points: 4575 More actions · Answer 2

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

kevaburg SSCoach Points: 18131 More actions · Answer 3

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.

hcip.77 Ten Centuries Points: 1067 More actions · Answer 4

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

Joern Boeger Old Hand Points: 364 More actions · Answer 5

I use the maintenance solution from Ola

https://ola.hallengren.com/

For me, the best solution I've found.

Supports HAG!

rummings SSCarpal Tunnel Points: 4575 More actions · Answer 6

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