1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

Dedup for TDP SQL?

Discussion in 'Microsoft SQL Server' started by robinsonjas, Aug 18, 2011.

  1. robinsonjas

    robinsonjas New Member

    Joined:
    Nov 1, 2006
    Messages:
    19
    Likes Received:
    0
    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.
     
  2.  
  3. totalstu

    totalstu New Member

    Joined:
    May 7, 2009
    Messages:
    111
    Likes Received:
    0
    Location:
    Toronto, Canada
    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
     
  4. robinsonjas

    robinsonjas New Member

    Joined:
    Nov 1, 2006
    Messages:
    19
    Likes Received:
    0
    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.
     
  5. Jeff_Jeske

    Jeff_Jeske Senior Member

    Joined:
    Jul 17, 2006
    Messages:
    485
    Likes Received:
    7
    Occupation:
    Storage Engineer - DR Coordinator
    Location:
    Stevens Point, WI
    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?
     
  6. robinsonjas

    robinsonjas New Member

    Joined:
    Nov 1, 2006
    Messages:
    19
    Likes Received:
    0
    so far...

    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.
     
  7. Jeff_Jeske

    Jeff_Jeske Senior Member

    Joined:
    Jul 17, 2006
    Messages:
    485
    Likes Received:
    7
    Occupation:
    Storage Engineer - DR Coordinator
    Location:
    Stevens Point, WI
    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?
     
  8. robinsonjas

    robinsonjas New Member

    Joined:
    Nov 1, 2006
    Messages:
    19
    Likes Received:
    0
    client side dedup - full tdp backups
     
  9. Jeff_Jeske

    Jeff_Jeske Senior Member

    Joined:
    Jul 17, 2006
    Messages:
    485
    Likes Received:
    7
    Occupation:
    Storage Engineer - DR Coordinator
    Location:
    Stevens Point, WI
    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
     
    Last edited: Feb 8, 2012
  10. Jeff_Jeske

    Jeff_Jeske Senior Member

    Joined:
    Jul 17, 2006
    Messages:
    485
    Likes Received:
    7
    Occupation:
    Storage Engineer - DR Coordinator
    Location:
    Stevens Point, WI
    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?
     
  11. Jeff_Jeske

    Jeff_Jeske Senior Member

    Joined:
    Jul 17, 2006
    Messages:
    485
    Likes Received:
    7
    Occupation:
    Storage Engineer - DR Coordinator
    Location:
    Stevens Point, WI
    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
     
    Last edited: Mar 29, 2012
  12. Jeff_Jeske

    Jeff_Jeske Senior Member

    Joined:
    Jul 17, 2006
    Messages:
    485
    Likes Received:
    7
    Occupation:
    Storage Engineer - DR Coordinator
    Location:
    Stevens Point, WI
    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!
     
    Last edited: Apr 2, 2012
  13. daleto

    daleto New Member

    Joined:
    Apr 10, 2005
    Messages:
    10
    Likes Received:
    0
    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?
     

Share This Page