none
DPM taking so long time to backup SQL Databases. RRS feed

  • General discussion

  • Hi Folks

    I am facing an issue with backing up one SQL server. We have one application deployed for 2 different customers and I am backing up these 2 databases server in same protection group. 

    Customer A's Database server is very busy because they upload heavy files into application which mackes databases busy continously. 
    Customer B's Database server isn't much more activ e but is been in general use since they are uploading small and average files. 

    Now when DPM backing up this protection group, Database B server is backing up normally. But Server A databases are taking longtime (15-18 Hours) to complete syncing and Backing up. 
    This is excpected to take time since there are lot of changes in databases but not expecting such a long time where next scheduled backup jobs are overlapping with old ones. 

    I just want to know How DPM track SQL changes on protected Server like through voume filter or bitmap? What precuation I should take to resolve this issue!

    Here are few points related to these databases. 

    1. Databases sizes are from 30 GB to 300 GB
    2. These are Simple mode databases but files are spreaded through .ndf files. 
    3. Application is loading heavy files interacting those databases. 
    4. I have set Sync Frequency 4 hours and Express full backups 2 times in a day 10AM, 10PM
    5. Support guys are taking manual backups randomly in .bak format. 

    Also during DB backups, Sysem file is creating and growing upto 50-60 GB.




    Please help on this. 
    Thursday, April 16, 2015 6:25 PM

All replies

  • 1. I just want to know How DPM track SQL changes on protected Server like through voume filter or bitmap?

    2. What precaution I should take to resolve this issue!

    3. These are Simple mode databases but files are spreaded through .ndf files. 

    4. I have set Sync Frequency 4 hours and Express full backups 2 times in a day 10AM, 10PM

    Also during DB backups, Sysem file is creating and growing upto 50-60 GB.

    1. Bitmap level tracking happens only for File-servers. Not applicable for Application level data.

    2. If possible, implement SQL AG and start taking backup from the 'passive node'.

    3. Make sure you have "Full recovery model" in SQL.

    4. I'm wondering why are you taking Express full backups. You said the database is accessed heavily and info changed is too much. So, you need to implement "INCREMENTAL backups: must faster solution". Express full is 'bound' to take time and also putting slight impact on performance of SQL as well.

    After this increase the sync frequency and you should be good.

    Hope this helps.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. This posting is provided "AS IS" with no warranties, and confers no rights.

    Thursday, April 16, 2015 9:27 PM
  • Hi Abhijeet 

    1. If Bitmap level tracking is for File-Servers, then how DPM track sql changes for SIMPLE mode databases!

    2. These are two separate independednt SQL servers, not in cluster. 

    3. Simple recovery mode is suggested by application developer, this isn't in our hand to change it. 

    Thursday, April 16, 2015 9:36 PM
  • Hi,

    Block level tracking is enabled for all protected workloads except client protection not just file servers.  Most of the time backup performance is related to disk performance, and from what I can tell there is a lot of IO occurring while there is an active snapshot, and that causes a lot of copy-on-write (COW).  For a write to a sql db, there is another read and another write before the write goes to the sql DB.   You can try adding a new disk, then move the shadow copy storage to that volume to eliminate the extra write on the SQL volume for the COW.

     c:\>vssadmin add shadowstorage /for=E: /on=F: /maxsize=unbounded


    You can also run a performance monitor.


    For some basic perfmon counters to help narrow down the possible bottleneck:


    Perf Counters for DPM

    ******************


    Logical Disk/Physical Disk

    \%idle

    • 100% idle to 50% idle = Healthy
    • 49% idle to 20% idle = Warning or Monitor
    • 19% idle to 0% idle = Critical or Out of Spec


    \%Avg. Disk Sec Read or Write

    • .001ms to .015ms  = Healthy
    • .015ms to .025 = Warning or Monitor
    • .026ms or greater = Critical or Out of Spec


    Current Disk Queue Length (for all instances)

    80 requests for more than 6 minutes.

    • Indicates possibly excessive disk queue length.


    Memory

    *******

    \Pool Non Paged Bytes*

    • Less that 60% of pool consumed=Healthy
    • 61% - 80% of pool consumed = Warning or Monitor.
    • Greater than 80% pool consumed = Critical or Out of Spec.


    \Pool Paged Bytes*

    • Less that 60% of pool consumed=Healthy
    • 61% - 80% of pool consumed = Warning or Monitor.
    • Greater than 80% pool consumed = Critical or Out of Spec.


    \Available Megabytes

    • 50% of free memory available or more =Healthy
    • 25% of free memory available = Monitor.
    • 10% of free memory available = Warning
    • Less than 100MB or 5% of free memory available = Critical or Out of Spec.


    Processor

    *******

    \%Processor Time (all instances)                                                                  

    • Less than 60% consumed = Healthy
    • 51% - 90% consumed = Monitor or Caution
    • 91% - 100% consumed = Critical

    *************************************************************


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.


    Thursday, April 16, 2015 10:46 PM
    Moderator
  • Thanks Michael 

    I will follow these instruction and will update it here. 

    Thursday, April 16, 2015 10:56 PM
  • Thanks Mike. 

    Adding another drive as shadow copy volume really made progress there, now these databases are backing up successfully. however the time consumed is decreased from 10-15 hours to 3-4-5 hours. 

    Now I have enabled 1 express full backup per day and consistency check just before recovery point. These are simple mode databases (having multiple .ndf files). by enabling 2 times express full backups will make any progress there in time consumption??

    Monday, April 20, 2015 10:48 PM
  • Hi,

    You mean you set the protection group to synchronize right before recovery point ?  If the DB's are simple recovery mode then that will not have any effect.  Making two express full recovery points / day may help - it depends on the sql db churn pattern.  If the same tables change through out day, then you are just transmitting different versions of the same tables twice a day, but if different tables change over a longer period of time, then you could see smaller / faster express full backup times.   Only time will tell.  


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, April 21, 2015 1:47 AM
    Moderator
  • Thanks for Help Mike

    Issue resolved after adding another drive as Shadow copy volume and dividing Express full backup into two per day. 

    Wednesday, April 22, 2015 5:10 PM
  • Hi,

    Nice - thanks for the update.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, April 22, 2015 6:30 PM
    Moderator