Avamar-Data Domain Integration: SQL full backup change rate


   Article Number:     503359                                   Article Version: 3     Article Type:    How To 




Avamar Plug-in for SQL,Avamar Plug-in for SQL 7.4.101-58







    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.   


    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.   


  •         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   
    * Use --blocksize=0x4000000 in avtar.cmd