|Article Number: 503359||Article Version: 3||Article Type: How To|
Avamar Plug-in for SQL,Avamar Plug-in for SQL 7.4.101-58
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.
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