Dedup for TDP SQL?

[robinsonjas]

We are running TSM server 6.2.2.3 and our Windows BA Cleints are 6.2.3, TDP SQL are 5.5.4
I would like to find out how to setup dedup for the TDP data. We have setup our BA client's dsm.opt for dedup, but I'm not sure how to setup the TDP side.
Here's an example of our BA dsm.opt file, we only dedup C: and SYSTEMSTATE

PASSWORDACCESS GENERATE
TCPSERVERADDRESS TSMCORP5
SCHEDLOGRET 30
domain all-local

compression yes
compressalways yes

DEDUPLICATION yes
dedupcachesize 1024
enablededupcache yes

skipmissingsyswfiles yes
************************************************** ************************
include.backup c:\...\* windd_default
include.systemstate ALL windd_default
include.systemobject ALL windd_default

include.dedup C:\*\* ieobjtype=file
include.dedup ALL ieobjtype=systemobject
include.dedup ALL ieobjtype=systemstate
include.dedup ALL ieobjtype=ASR


As for TDP data, we only want to dedup the FULL backups and not the logs. Any help is appreciated. I can't find anything in documents that point to specific settings for TDP for SQL.

[totalstu]

Hopefully someone can/will answer robinsonjas's question. I have a related question. Can we client side dedupe or must any SQL dedupe be server side?

Thanks

[robinsonjas]

I'm having some success...Will keep thread updated, we're basically using trial and error.
So far we used the above dsm.opt in baclient directory and added the include statement for the dedup mgmt class in the tdpsql.opt file.
I would like to know if anyone has used SATA storage for the diskpool for dedup when doing 'client-side' dedup.

[Jeff_Jeske]

We are finally deploying SQL databases that are large enought to require the TDP. I'd like to try client side dedup via the TDP and back it up to our sata file device class.

Do any of you have additional input or even rough dedup rates for what you are seeing?

[robinsonjas]

Our SQL group does full backups daily, so that skews our rates a bit, but none the less we are getting 85-95% dedup rate on our pool.

[Jeff_Jeske]

Hmmm.... are they doing full flat file backups to disk or are they doing full TDP with dedup enabled?

Are you using client side dedupe?

[robinsonjas]

client side dedup - full tdp backups

[Jeff_Jeske]

EDIT: Nevermind I see they are listed in the first thread. Thanks.
TSM server 6.2.2.3 and our Windows BA Cleints are 6.2.3, TDP SQL are 5.5.4

[Jeff_Jeske]

So I am still playing around with this..... below is a cut-n-paste of my DSM.OPT file located in the TDPSQL folder:

CLUSTERNODE NO
TCPServeraddress SERVER10.sentry.com
TCPport 1501
NODEname SERVER02-s
compression yes
compressalways no
TCPWindowsize 63
TCPBuffSize 32
passwordaccess generate
deduplication yes
dirmc dedup16_42
INCLUDE * dedup16_42

This is my attempt to bind everything the TDP backs up to the dedupe16_40 managment class. Does this pass the smell test?

[Jeff_Jeske]

I "think" I'm still missing something with my client side configuration.....

How can you tell that your client side dedupe is working? Does the ACT log session report provide dedupe stats? If so mine still isn't deduping as it appears I send the full amount each run of the job. Can someone post a screen shot of what they are seeing upon completion of a SQL TDP CLIENT SIDE DEDUPE backup job?

Right now I am getting DEDUP but I believe it is on the server side only.

******************* UPDATE FOR THOSE NEEDING THIS INFO **********************
Verifying that data deduplication is taking place in Tivoli Storage Manager, V6.1 or earlier Data Protection clients do not display deduplication reduction statistics in the interface. You can verify that data deduplication is taking place:
1. Add the following statements to the dsm.opt file:
TRACEFILE api.txt
TRACEFLAGS dedup api
2. Locate a trace entry for the tsmEndSendObjEx API call. The entry looks like the following example:
03/05/2010 13:41:16.286 : dsmsend.cpp (2052): tsmEndSendObjEx: Total bytes
send 0 2105534, encryptType is NO encryptAlg is NONE compress is 0, dedup
is 1, totalCompress is 0 0 totalLFBytesSent 0 0 totalDedupSize 0 15001

The dedup parameter in the preceding trace extract indicates whether the objects were processed for client-side data deduplication. The totalDedupSize parameter in the preceding trace extract reports how many bytes were sent to the server after deduplication. Like image and system-state backups, with client-side data deduplication you can have more frequent full backups of the application data (databases, storage groups, and so on).

******************* ANOTHER UPDATE FOR THOSE NEEDING THIS **********************
Use the following select statement to getter a better feel for how dedup is workign for you:
tsm: TSMSERVER>select * from occupancy where node_name='DIXTREIDBS02-S'

NODE_NAME: DIXTREIDBS02-S
TYPE: Bkup
FILESPACE_NAME: DIX-T-REIDBS-02\data\0001
STGPOOL_NAME: DEDUP_POOL
NUM_FILES: 5
PHYSICAL_MB: 0.00
LOGICAL_MB: 139764.88
REPORTING_MB: 164869.34
FILESPACE_ID: 16

[Jeff_Jeske]

I'm back.... after a few days of testing and tracing I have it all figured out. Bottom line... dedupe doesn't really work for crap on databases!

I have a 320GB database that will compress down to 60GB with client side compression. When I add in dedupe and run a few pretty much identical backups the jobs are always 60GB in size. When I turn off compression and use dedupe alone the jobs end up being 120GB time and time again. I'm going to grab and test a few more production databases but I'm not expecting too much.

I'm betting those of you that claim you are getting 99% dedup on full TDP backups need to take a closer look at your occupancy!

[daleto]

I have never seen a good reduction of SQL Server data with TDP for Databases on SQL Server.
It varies around 20% reduction with TSM Server deduplication.

I have not tested client deduplication yet, but I have read that client dedupliction might reduce it more efficient (!??)
I can agree if it is in combination with TSM API compression.

We have reported to IBM many years ago that the TSM API compression algorithm is not efficient enough compared to other compression algorithms (Eg gcc)
They have agreeded to change this in version 6.3. (a maint level), but as this has not yet been released...

If the SQL Server compresses data, the deduplication ratio will vary around 1% !!

In our environment, the SQL Server team is constantly reorganizing the databases, and rebuilding indexes, etc.
This has a big negative impact on reduction sizes (such as deduplication ratio).

Maybee you should check if this is the same in your case?