Partilhar via

SQL16 Full Backup Size Difference on Primary Replica and Secondary Replica

  • Artigo

Database full backup could be taken from Always On Availability Groups Active Secondary with COPY_ONLY option. In a recent SQL2016 project, we observe that the copy only full backup size are different when taking backup from Primary Replica and Secondary Replica.

Symptom

We configure 3-nodes AlwaysOn AG with SQL Server 2016 CU1 64bit. Synced mode is used for every replica. The status of each replica is synchronized. Before full backup task, there are many data modification. There is no other workload during the whole test. The backup file size and backup duration are different. When we analyze the backup statement output, we find out the number of processed page for log on Primary is much larger than on Secondary.

Copy_only Full Backup Statement Output on Primary Replica
Processed 6216 pages for database 'testing_db', file 'testing_db_data' on file 1.
Processed 3979919 pages for database 'testing_db', file 'testing_db_dir' on file 1.
Processed 16333281 pages for database 'testing_db', file 'testing_db_log' on file 1.
BACKUP DATABASE successfully processed 20319416 pages in 170.540 seconds (930.839 MB/sec).

Copy_only Full Backup Statement Output on Secondary Replica
Processed 6216 pages for database 'testing_db', file 'testing_db_data' on file 1.
Processed 3973037 pages for database 'testing_db', file 'testing_db_dir' on file 1.
Processed 1334436 pages for database 'testing_db', file 'testing_db_log' on file 1.
BACKUP DATABASE successfully processed 5313689 pages in 40.881 seconds (1015.464 MB/sec).

 

Why the Processed Pages of Log are Different

Data pages and log pages are copied into the backup set when full backup are being executed. The main reason of copying log is to make sure we could restore a backup file to get a transactionally consistent database. And a full database backup contains more than the transaction log from the start of the backup to the end of the backup. After reviewing the code of current SQL Server, here is how SQL Server 2016 decides which range of log a full backup will contain:

Full Backup Type: The start LSN of the log included in a database backup is the minimum of:
Normal Full Backup on Primary Replica: m_ckptLSNm_OldestPageLSNm_oldActXactm_replLSNm_dbMirroringLSNm_HostLogLSNm_HkRecoveryLSNm_HADRonLSN
COPY_ONLY Full Backup on Primary Replica: m_ckptLSNm_OldestPageLSNm_oldActXactm_replLSNm_dbMirroringLSNm_HostLogLSNm_HkRecoveryLSNm_HADRonLSN
COPY_ONLY Full Backup on Secondary Replica: m_ckptLSNm_OldestPageLSNm_oldActXactm_replLSNm_dbMirroringLSNm_HostLogLSNm_HkRecoveryLSN

 

SQL Server will calculate the start LSN of the log included in a database backup by above table. Normal full backup and copy_only full backup count same set of internal LSN. Comparing to copy_only full backup on Secondary, m_HADRonLSN is included on Primary. This is the reason why backup size on Primary is larger than Secondary.

 

m_HADRonLSN is mainly to record the min LSN that has not been shipped to replicas. m_HADRonLSN is the min of all the secondary truncation points which include all the truncation points. From SQL 2016, m_HADRonLSN is also used in the Secondary fast seeding. Taking log backup will also move m_HADRonLSN forward when all the secondary replicas are in synced status.

Suggestion

Basing on the finding, if there is no differential backup in your backup plan, you can take COPY_ONLY full backup on Secondary to get better backup size. If the full backup is scheduled on Primary (when you need differential backup or have no license for readable secondary feature), you could to also schedule the log backup first to move m_HADRonLSN forward.

 

 

Posted by Shiyang Qiu, Oct 16, 2016
Special thanks to Simon Su for the SQL source code review.