May 31, 2012 at 11:33 am
We are looking into AlwaysOn secondary replicas as a way of offloading database backups from the principal (prod) servers to the replicas.
Problem is, AlwaysOn supports only full backups that are copy-only and normal log backups.
We are not using differential backups in our environment: just full and log backups.
I don't see any issue with running copy-only (full) and regular log backups on secondary replicas.
We should have no trouble recovering a database from a sequence of copy-only full backups followed by log backups.
At least that is what my tests have shown.
Does anyone have any concerns with this approach?
Am I missing something? 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 31, 2012 at 11:37 am
Unless something has changed with COPY ONLY full backups, it was my understanding that they could not be used as the base for a recovery.
May 31, 2012 at 12:08 pm
Lynn Pettis (5/31/2012)
Unless something has changed with COPY ONLY full backups, it was my understanding that they could not be used as the base for a recovery.
They can.
I was not sure either about this, based on what I was reading.
However, I did some tests yesterday, wihch confirmed that one can recover from a sequence of copy-only and log backups.
This could be huge; it strenghtens the case for using AlwaysOn secondary replicas.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 3, 2012 at 10:23 am
Hi yes this is ok as a solution.
If you need to recover to a point in time, you can restore the full copy-only backups and subsequent log files to get to that point-in-time.
What you cannot do is restore a differential backup on top of that full copy-only backup. The differential backups depends on a differential base which is essentially a full backup (not copy-only). In an AlwaysOn configuration, you cannot take differential backups anyway,
This makes AlwaysOn a great option for offloading the pressure of the backup process - especially if using 3rd Compression tools like Litespeed that are heavy on CPU (only heavy of course if you choose high rate of compression and want faster times 🙂
Hope that helps.
Rich
July 3, 2012 at 10:48 am
I think this will work fine, just be sure that you have all the log backups with the copy only backup. The one thing I'd be worried about is the log backup before or during the copy_only backup. It shouldn't matter, but I'd be nervous that the LSNs wouldn't match up somewhere in there.
IIRC, the copy only backup just doesn't reset the diff map, but otherwise looks like a full backup, so it ought to be in sync with any log backups as long as you have a sequence of unbroken LSNs from the full onwards.
July 19, 2012 at 8:27 am
We use this exact strategy and it works great! Give it a go.
January 28, 2014 at 2:36 pm
What about transaction logs on the primary replica? Will these will need to be manually truncated or will backing up a transaction log on a ROSR truncate the log on the primary? Seems unlikely. Any insights?
January 28, 2014 at 2:46 pm
It looks like this is, in fact, the case; Backing up the transaction log on the read-only secondary replica truncates the transaction log on the primary.
January 28, 2014 at 5:32 pm
dave-L (1/28/2014)
It looks like this is, in fact, the case; Backing up the transaction log on the read-only secondary replica truncates the transaction log on the primary.
It's supposed to, otherwise there isn't a lot of value to doing them on the secondary.
However, make sure you have all logs from all replicas since they form a single chain. That's something a few people forget.
January 29, 2014 at 4:56 am
Thank you all for your input.
In our environment we are using AlwaysOn AGs with the async option, to prevent latencies on the secondary-replica side from affecting performance on the primary. We are still doing all full and log backups on the primary replica - no diffs.
Primary and secondary replicas are on different subnets (VLANs).
We were recently asked by our infrastructure team if we could move all our backups to the secondary replica to reduce the load on resources on the primary-replica side. (Apparently someone from their team did some reading on AlwaysOn 🙂 ).
My dilemma is that if we move both our full and log backups to the secondary, our log backups will not reflect the latest transactions in prod, as there is always some latency in synchronization between primary and secondary replicas (from a few minutes to 1-2 hrs when the primary is under heavy load). As a result, taking the log backups on the secondary will violate our RPO policy.
If, on the other hand, we move only the full (copy-only) backups on the secondary and keep the log backups on the primary, to maintain our RPO, we might end up with a broken backup chain (?). This last point I am not sure about.
Any thoughts on this scenario of splitting full and log backups between primary and secondary replicas?
(This may have been covered in the previous postings, but just want to be sure I understand this fully).
Thank you all!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply