March 16, 2015 at 5:17 pm
I have never worked with SQL Server availability groups. I was told we have to go to SQL Server 2014 and will be using Always On Availability groups. I was going to use SSMS maintenance plance for the backups, but I cannot get them to complete successfully.
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
March 17, 2015 at 7:18 am
A few things worth noting, posting this question in either of the SQL 2012 or 2014 parts of the forum is more likely to see responses than in SQL 2008 as that functionality doesn't exist in 2008.
You need to do your backups in T-SQL rather than a maintenance plan.
To save me typing lots of information, this blog pretty much covers it:
Lastly, if you are struggling with this concept so far, you really need to spend much more time planning your environment. the always on solution is very difficult to get setup correctly unless you really know what your doing.
MCITP SQL 2005, MCSA SQL 2012
March 17, 2015 at 8:20 am
Thank you for the information. I actually did post in the 2014 forum.... but did not get a reply.... thought maybe the "New to" section may get a reply. I am new to AAG's. Our engineers are designing the architecture.... but I need to supply the backup strategy. We are having an emergency meeting tomorrow to discus. The Oracle guys feel they can write a script to solve this issue. I was hoping the SSMS tools would work. Thanks again for replying.
March 17, 2015 at 4:02 pm
March 18, 2015 at 7:19 am
if you're going to be performing any of the backups against readable secondarys your backup scripts will need to employ a certain degree of logic to check which backups already exist and which databases are part of an AG
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply