March 30, 2015 at 6:05 am
Hey,
I have a brand new SQL 2014 AAG server (pair of servers actually). What is the best thing to do for backups: a Full Backup on the primary server, or a copy-only backup on the replica? What are the pros and cons?
Thanks,
March 30, 2015 at 9:33 am
The primary (sadly, pun intended) advantage of running a normal full backup is that you can leverage differential backups to cut down on backup time and resources used when you run differentials instead of fulls.
Running a copy-only on the secondary has the wonderful advantage of not taxing resources on your primary production server, but doesn't reset the differential base (and differentials aren't allowed against secondaries anyway).
Copy-only fulls against the secondary are great for backups that aren't used for data protection (refreshing data in a dev/test/sandbox server, for example), since you can take them without hitting your primary server.
For protecting your data, though, it's probably best to run regular full backups on the primary, just so you can actually use differential backups.
Just my $0.02.
Cheers!
March 30, 2015 at 1:38 pm
I investigated and came to this conclusion too....
Thanks for your $0.02!
March 30, 2015 at 2:56 pm
But if you're not using differentials, you can use the secondaries for backups. Also, the log backups can, roughly, be taken anywhere and then combined later. I wrote a chapter on backups with Availability Groups for the Pro SQL Server 2012 Practices book. It has a bunch more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2015 at 4:10 am
Grant Fritchey (3/30/2015)
Although, ideally, you'd store them all in a central location so they're available immediately upon requirement for a recovery
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 1, 2015 at 4:18 am
Perry Whittle (4/1/2015)
Grant Fritchey (3/30/2015)
Also, the log backups can, roughly, be taken anywhere and then combined later.[\quote]Although, ideally, you'd store them all in a central location so they're available immediately upon requirement for a recovery
Oh yeah, absolutely!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply