HA SQL AlwaysOn backup using Copy Only + Logs from secondary replica

availability-groupsbackupresource-governortimeout

I'm hoping to elicit some guidance from your collective DBA experiences.

Here's our problem:

Full backups of our larger databases (around 3TB) are consuming too much resources such that we are suffering lengthy timeouts. It appears to be mostly I/O bound.

Full backups are done once a week, with regular 15min transaction log backups in between. We have to take full backups at some point.

Options we are considering:

Upgrade from SQL 2016 Standard to 2016 Enterprise edition and configure the Resource Governor to limit I/O caused by backups. Basically choke the backups a little to allow servicing of application requests better.
Go the HA (High Availability) route and deploy a secondary node with SQL AlwaysOn perform backups from the secondary replicas. This would relieve the primary replica of backup duties and remain available for application requests, potentially resolving our timeouts issue.

Pro's & Con's:

The cost of Enterprise edition is not trivial and while it offers numerous other benefits, it's a hard pill to swallow at this time.

For a similar or even lesser cost, we can deploy a second SQL 2016 Standard edition server and benefit from a HADR-based database deployment. This option is very appealing to us as we currently only have a cold standby for recovery.

We would likely favor the benefits from HADR over Enterprise, provided we can resolve the timeouts issue during backups.

Some Lab Findings:

For Enterprise edition, some labs tests show we can control the backup resources reasonably well, but it doesn't feel ideal. We'll need to tweak the numbers to the hardware to find the "sweet spot" for achieving the required level of restriction for backups without adversely slowing it down too much.

For AlwaysOn, we've done some lab tests and found that we cannot perform typical "Full" backups on the secondary replica, but we can make COPY_ONLY "Full" backups. This makes sense as it preserves the log chain of the primary replica.

We are able to make transaction log backups on the secondary replica which too is fine since that log chain is actually a "slave" from the primary and should not cause any harm when made on the secondary replica.

Have confirmed we are able to restore from the CopyOnlyBackup+TransactionLogs, so this could be a viable data recovery strategy.

Findings and Questions:

Has anyone had a similar issue and resolved it with either option, confirming viability of the option for us?
In the "CopyOnlyBackup+TransactionLogs from the secondary replica" scenario, any full backup on the primary replica would break the log chain and invalidate the CopyOnlyBackup of the secondary replica for subsequent transaction log backups. The remediation is to make another CopyOnlyBackup on the secondary replica, or to not make full backups on the primary.
What is the implication of not making normal full backups on the primary and only ever making COPY_ONLY full backups. Is this OK in the long term?

Any guidance would be greatly appreciated. Hopefully we not barking up the wrong tree.

Best Answer

Im actually using a similar setup with 1TB of the database.

Has anyone had a similar issue and resolved it with either option, confirming viability of the option for us?

Im using backup on secondary only.Even in few outages, I tried to use copy only full backups and log backups to recover them.

No issues that using copy only backups.

The above statement will give a clarity for your 2 and 3'rd questions.

Related Solutions

Sql-server – AlwaysOn Secondary Replica DB-size

.mdf and .ldf files do not show the last modification date in the way you think it does.

The file-system date reflects the last time the instance started the database. Modifications to data continue while the instance has the database open, without the instance actually updating the file-system dates for the files in use by the database.

Can you add the output of sp_helpdb 'dbname' for both instances to your question? Also add output from:

SELECT d.name
    , mf.name
    , mf.type_desc
    , mf.size * 8192E0 / 1048576 AS FileSizeMB
    , mf.state_desc, mf.max_size * 8192E0 / 1048576 AS MaxFileSizeMB
    , mf.growth  * 8192E0 / 1048576 AS FileGrowthMB
FROM master.sys.databases d
    INNER JOIN master.sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name = 'dbname';

The following query, when run from the database in question, will show the number of megabytes allocated and used:

SELECT sum(total_pages) * 8192E0 / 1048576 as TotalMB
    , sum(used_pages) * 8192E0 / 1048576 as UsedMB
FROM sys.allocation_units;

Sql-server – How AlwaysOn Availability Group secondary replica catches up with primary after secondary server long downtime

If the secondary is up and running, when the log block is flushed to disk (either because it is full or a commit), the record gets pushed to the log writer on the primary and to the log scanner (log reader) process on the primary simultaneously. Then the log scanner communicates with the secondary and the secondary then pulls the transaction from the log scanner on the primary to the secondary and processes the log record. The primary log writer doesn't push transactions across, it just communicates with the secondary, it only does that to see if it is up so that it knows it doesn't have to mark the replica as NOT SYNCRONIZED.

When the secondary is not up, then the log writer cant communicate with the secondary so it marks it as NOT SYNCHRONIZED and stores the records in the tran log on the primary. If you look at sys.databases.log_reuse_wait_desc column it should show AVAILABILITY_REPLICA which means the primary is hanging on to all the records.

Once the secondary is up, it will communicate with the primary to request a log scan, it then processes the transactions and communicates with the primary using progress messages to indicate the hardened LSN, presumably the primary is then adjusting its MinLSN, which in turn means the records prior to MinLSN will get deleted as checkpoints happen and hence VLFs will get truncated releasing space when you do a log backup.

But yes short answer is, if your secondary is down you need as big a log file as you need for as long as it is down. Once it is backup and synched at some time you may need to remove the db from the always on group to shrink the log if it is humungous and you dont want it that big.

Best Answer

Related Solutions

Sql-server – AlwaysOn Secondary Replica DB-size

Sql-server – How AlwaysOn Availability Group secondary replica catches up with primary after secondary server long downtime

Related Question