Avamar-Data Domain Integration: SQL full backup change rate

Overview

This KB explains de-duplication rates for Avamar full backups for Microsoft SQL plugin to a Data Domain system.
 
In certain scenarios, customers experience higher  new  bytes than expected, for their SQL full backups. This article explores the architecture of the SQL backup streams and its impact on the Data Domain CDSF stream storage format. It also aims to make recommendations towards best practices.

SQL Streams

Microsoft SQL with SMO is capable of delivering single or multiple streams of data for backup. However, the stream content is not deterministic (The sequence of bytes delivered for unchanged source data can be different). The content, in both the multi, and single stream case is delivered in the order it is read from disk, and parallel reads are performed. This non-deterministic stream contributes to higher apparent change rates. The TSQL parameter  maxtransfersize  determines the size of chunks delivered on the stream. Larger values will help transferred chunks be more coherent.

Data Domain CDSF

SQL database backup streams are written to a Data Domain system in CDSF (Common Data Streaming Format). Each SQL stream is written to a different container. Data Domain de-duplication between containers is less than perfect due to a design emphasis on locality during initial data ingestion. This can result in an initial 6 to 8% duplicate segments relative to a previous backup. Much better de-duplication is eventually accomplished over 2 to 3 garbage collection cycles.

Conclusions

The non-deterministic nature of the SQL backup streams, compounded by the incomplete initial de-duplication on the Data Domain system can result in a ~5% change rate reported for the backup even for the case when there has been no change.

Recommendations

• Verify that the default SQL maxtransfersize value (since 7.0) is 4MB. (Check avsql.cmd file on the SQL client and Dataset for the SQL backup)
• Use SQL incremental backups instead of all FULL s , for large databases
• Match avtar block size to SQL by setting  --[avtar]blocksize=0x400000 in the SQL Dataset

Note : the SQL plugin does not support assigning flags to downstream plugins via the avsql.cmd file
             --[avtar]flag-name in avsql.cmd will not work

Correct way to enable this flag: 

* Use the --[avtar]blocksize=0x400000 in the SQL Dataset

or 

* Use --blocksize=0x4000000 in avtar.cmd