Avamar Data Domain Integration: SQL full backup change rate

Overview

This KB explains deduplication rates for Avamar full backups for Microsoft SQL plugin to a Data Domain.
 
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 can deliver 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 nondeterministic stream contributes to higher apparent change rates. The TSQL parameter maxtransfersize determines the size of chunks delivered on the stream. Larger values help transferred chunks be more coherent.

Data Domain CDSF

SQL database backup streams are written to a Data Domain in CDSF (Common Data Streaming Format). Each SQL stream is written to a different container. Data Domain deduplication 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. Better deduplication is eventually accomplished over 2 to 3 garbage collection cycles.

Conclusions

The nondeterministic nature of the SQL backup streams, compounded by the incomplete initial deduplication on the Data Domain 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 is 4MB. (Check avsql.cmd file on the SQL client and Dataset for the SQL backup)
• Use reasonable SQL backup policies databases see guides for full discussion.
• Match avtar block size to SQL by setting "--[avtar]blocksize=0x400000" in the SQL Dataset

             --[avtar]flag-name in avsql.cmd does not work.

Correct way to enable this flag: 

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

Or 

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

--maxtransfersize=65536 
(64K)

This adversely affected performance Removing the avsql.cmd improved performance 4x

--maxtransfersize=4194304 
(4MB)
This setting for the best performance