1. Community Tip: Please Give Thanks to Those Sharing Their Knowledge.
    If you receive helpful answer on this forum, please show thanks to the poster by clicking "LIKE" link for the answer that you found helpful.
  2. Community Tip: Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING)
    Click the link above 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 notice will disappear after you have made at least 3 posts.

TDP for SQL - doubled backup

Discussion in 'Backup / Archive Discussion' started by Dikajos, Jul 17, 2017.

  1. Dikajos

    Dikajos ADSM.ORG Member

    Joined:
    Aug 9, 2016
    Messages:
    10
    Likes Received:
    0
    Hi,
    We noticed that in our environment there is doubled very big backup of SQL.
    It looks like each command tdpsqlc backup xxxx full/log/diff is performing backup twice.
    From SQL level we can see that there are two restore points available.

    When we are checking FS information there is second FS in below convention:

    NodeName\data\0001
    NodeName\data\0002

    Of course 0002 shouldn't be existing.
    When we are performing below command:
    select FILESPACE_NAME, STATE, TYPE, HL_NAME, LL_NAME, BACKUP_DATE from backups where node_name='NodeName.SQL' and FILESPACE_ID='X' and HL_NAME like 'XXXX%'
    We are receiving the same output for both Filespaces, even the time so it looks like backup is done once but sent to two FileSpaces.


    From SQL Logs we can see few things which are not normal in our opinion:
    1. Number of dump devices: 2
    2. Type=VIRTUAL_DEVICE('TDPSQL-00003368-0000',TDPSQL-00003368-0001')


    We have done below tests:
    1. Backup was performed for TestDB to newly created node but it's the same situation.
    2. SQL Dump was performed to local disk and only one .bak file was created.


    Take a look in to below version information:
    BA Version 7.1.6.5
    TDPSQL 7.1.6.2 FlashCopyManager 4.1.6.2
    Windows Server 2008 R2 x64
    SQL Server 2008 SP4 - 10.0.6535.0


    Did any one of you have this kind of situation? Or maybe you know where to search the reason of doubled FS during each backup.
     
  2.  
  3. Dikajos

    Dikajos ADSM.ORG Member

    Joined:
    Aug 9, 2016
    Messages:
    10
    Likes Received:
    0
    I forgot to mention about TSM Version:
    Storage Management Server for Windows - Version 7, Release 1, Level 3.0
     
  4. RecoveryOne

    RecoveryOne ADSM.ORG Member

    Joined:
    Mar 15, 2017
    Messages:
    34
    Likes Received:
    4
    For comparison TDPSQL 7.1.6.0, FCM 4.1.6.0
    Server on AIX is 7.1.7.0
    W2k8 R2 64bit with SQL Server 2008 SP4 10.0.6241.0

    Now, I'm just starting to dig into Data Protection for SQL on a few of our servers and by no means an expert.

    From one of my TDP MSSQL nodes I have a meta filespace and a data filespace. For example:
    Code:
    KDBTEST_SQL      KDBTEST\meta\0000        1       TDP MSSQL Win64 API:SqlData     No      0 bytes 0.0
    KDBTEST_SQL      KDBTEST\data\0001        2       TDP MSSQL Win64 API:SqlData     No      0 bytes 0.0
    Also, in the FCM properties what setting do you have for Performance?
    Right now, all the ones I have set are default.
    DP Buffers =3
    DB Buffer size =1024
    SQL Buffers =0
    SQL Buffer size =1024
    Stripes=1
     
  5. Dikajos

    Dikajos ADSM.ORG Member

    Joined:
    Aug 9, 2016
    Messages:
    10
    Likes Received:
    0
    Hi,
    Thank you for your respond.

    For my case, we have double one meta FS and double Data FS (\0001 and \0002).

    Node Name Filespace Name FSID Type Platform Filespace Is Filespace Unicode? Capacity Pct Util
    --------------- ----------- ---- -------- --------- ---------- ----------- -----
    XXXXX.SQL XXXXX\meta\0000 1 WinNT API:SqlData No 0 KB 0.0

    XXXXX.SQL XXXXX\data\0001 2 WinNT API:SqlData No 0 KB 0.0

    XXXXX.SQL XXXXX\data\0002 3 WinNT API:SqlData No 0 KB 0.0

    We were checking our parameters, read about it and we didn't find anything not correct. Please take a look in to our settings:

    SQLAUTHentication INTegrated
    MOUNTWaitfordata Yes
    BACKUPMethod LEGACY
    DIFFESTimate 20
    BUFFers 5
    BUFFERSIze 1024
    STRIPes 2
    SQLBUFFers 2
    SQLBUFFERSIze 1024
    LOGPrune 60
    LANGuage ENG
    BACKUPDestination TSM
    REMOTEDSMAgentnode
     
  6. hogmaster

    hogmaster ADSM.ORG Senior Member

    Joined:
    Nov 9, 2002
    Messages:
    536
    Likes Received:
    24
    Occupation:
    Storage engineer
    Hello,
    Change stripes to 1 and repeat the test.
    Currently you are sending 50% of the db backup on "session" 1 and the rest on "session" 2.

    Havinge more stripes will increase your backup performance. (However you restore may be slow if you migrate the data to tape, thne you may run in to a contention problem)

    After your test confirm that all of the data is sent to only one filespace. then chnage the values back to 2 or even 3 and check the backup performance.
     
  7. Dikajos

    Dikajos ADSM.ORG Member

    Joined:
    Aug 9, 2016
    Messages:
    10
    Likes Received:
    0
    Hi,
    We tested your proposition and it looks like after changing Stripes parameter it's using only one stream and writing only to one FS. It's very good as it looks like it will resolve our problem.

    In the tests the backup was much longer with one stripe but it's creating only one file so it's saving space in our case. When we once again change to 2 strips, then it's once again used two FS's so we will use for some time 1 Stripes and redundant data will disappear.

    We read about Stripes parameter and it's about using more streams, splitting backup. We still don't know why this option is not only splitting data for two FS, but it's consuming twice the size.

    In our case we are using Full backup for 1TB database. During Backup Job is creating two FS and putting 1TB file in each so together one backup is consuming 2TB. Does anyone know why it's happening?


    Once again thank you for your comment !!! We will test it on production (need to wait till weekend), and return with output.
     
  8. marclant

    marclant ADSM.ORG Moderator

    Joined:
    Jun 16, 2006
    Messages:
    2,533
    Likes Received:
    354
    Occupation:
    Accelerated Value Specialist for Spectrum Protect
    Location:
    Canada
    What data are you using to come up with that conclusion? It might logically appear to use 2TB, but physically its' not. Did you look at QUERY OCCUPANCY for that node?
     
  9. Dikajos

    Dikajos ADSM.ORG Member

    Joined:
    Aug 9, 2016
    Messages:
    10
    Likes Received:
    0
    Database size is 1TB.
    From TSM Site we checked occupancy(physical/logical). We also checked it by special commands which were counting size of each files and these numbers are also doubled.
     
  10. marclant

    marclant ADSM.ORG Moderator

    Joined:
    Jun 16, 2006
    Messages:
    2,533
    Likes Received:
    354
    Occupation:
    Accelerated Value Specialist for Spectrum Protect
    Location:
    Canada
    I'm not convinced of that. Because you have two streams, you have parts of the object sent in one stream and another part of the same object in the other stream. Logically, each object is reported twice, but that doesn't necessarily mean that it's physically occupying 2 TB.

    Which commands?
     
  11. Dikajos

    Dikajos ADSM.ORG Member

    Joined:
    Aug 9, 2016
    Messages:
    10
    Likes Received:
    0
    Hi,
    Please take a look in to below command:
    select cast(float(sum(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count(bk.bfsize) as number_of_objects from backups b, backup_objects bk where b.object_id=bk.objid and b.type='FILE' and b.node_name='XXXXX.SQL'

    The output for above command is 2.4 TB.

    We also checked and counted amount of backups from the TDPO for SQL (MMC) and summary of the data is 1.2 TB.
     
  12. marclant

    marclant ADSM.ORG Moderator

    Joined:
    Jun 16, 2006
    Messages:
    2,533
    Likes Received:
    354
    Occupation:
    Accelerated Value Specialist for Spectrum Protect
    Location:
    Canada
    That query is probably counting it logically, not physically. Because of the two streams, there the data is split in two, and stored in 2 filespaces (1 for each stream, so that it can restore the same way). However, it's reporting the size of the object in each filespace, rather than the size of the amount of data that as included in that stream.

    That's how much data is stored on the Spectrum Protect Server. It would make no sense to split the data in two, send it in two streams, re-assemble both streams back on the Spectrum Protect Server to store two copies of the same data. The Spectrum Protect Server is not aware of the data itself, it's just blobs of bytes, it would not know how to re-assemble it. Only the client knows because it's the one that split it into two streams.
     
  13. Dikajos

    Dikajos ADSM.ORG Member

    Joined:
    Aug 9, 2016
    Messages:
    10
    Likes Received:
    0
    Hi,
    We were using this command for other cases and from what we know it's counting bytes of the files located on the TSM Storage, so exactly the space which is consumed. This command was also used together with TSM Support as PMR was created for other case where the command was used.

    Yes, exactly. 1.2 TB is the space which should be used by this database. From TSM and TDP for SQL site we see only one version of these files and it's counting it as 1.2 TB. But from SQL site, we checked it and there are available two restore points done on the same time.

    In our scope we have small Disk storage so we see exactly how many volumes are used and it should be 1.2 TB consumed, but it's consuming 2.4 TB of space, exactly doubled.

    After changing Stripes parameter, we compared information, and now the newest log backup wasn't duplicated, the space usage is decreasing. But it's small amount as it's log backups. The biggest difference we will notice after weekend (Full bcakup).

    I agree with you that description of the Stripes option saying about spiting streams, not doubling data. But in our case unfortunately it was doubling used storage. And we still don't know why ;/
     
  14. marclant

    marclant ADSM.ORG Moderator

    Joined:
    Jun 16, 2006
    Messages:
    2,533
    Likes Received:
    354
    Occupation:
    Accelerated Value Specialist for Spectrum Protect
    Location:
    Canada
    I can't explain it.
     

Share This Page