none
SQL Express full backup backup RRS feed

  • Question

  • Hello

    We have several databases of approximately 400GB in size and 10-15% daily changes. Express full backup takes a really long time to complete. Can anyone please tell me, what actually happens, when EFB is performed? Does DRA on the SQL server read the whole MDF file and compare changed blocks to what DPM has or is there a different approach?

    Thanks, Grega

    Wednesday, June 15, 2011 7:24 AM

All replies

  • Hi Grega78,

    An "express full" uses the SQL Server VSS Writer to identify which blocks have changed in the entire production database, and send just the updated blocks or fragments.

    So... when an express full occurs for these databases, how much data is being transferred and how long EFB is taking for each database?

    BTW, which DPM/SQL version are you running on your environment?

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Thursday, June 16, 2011 4:34 AM
    Moderator
  • Hello Wilson

    Thanks for the explanation. What I don't understand is that when there are only few changes in the database (couple of MBs transfered to DPM) it is a matter of seconds or minutes, but when 40GB of data gets copied, it takes hours (from 2,5 to 4 hours) which makes the transfer rate of approximately 4 MB/s. For comparison: time required for initial replica copy was 2,5 hours (40 MB/s transffer rate).

    Is there maybe a threshold or something, where the VSS writer performs the full DB scan because of to many changes?

    Thanks, Grega

    Friday, June 17, 2011 7:50 AM
  • So what is the SQL version (build - run select @@version) and OS (including service pack if any) you have on your SQL server?
    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, June 17, 2011 3:39 PM
    Moderator
  • Hello Wilson

    SQL: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46

    OS: Windows Server 2008 R2 Datacenter, Version 6.1 (Build 7601: Service Pack 1)

    DPM: Version 3.0.7706.0

    DPM Agent: 3.0.7706.0

    Kind regards,

    Grega

    Monday, June 20, 2011 7:50 AM
  • Hello Wilson

     

    I apologize for pushing it, but we really need a solution for this one (or change the backup approach).

    Do you think we can do something about it?

    Thanks, Grega


    Grega
    Saturday, June 25, 2011 2:41 PM
  • I'd check for:

    1.       Disable TCP offload/TCP chimney on both DPM Server and SQL server if they are enabled:

    netsh int tcp show global – (to verify Receive-Side Scaling State and Chimney Offload State are set to disabled). If they are not, you can disable them by running the commands below

    netsh int tcp set global chimney=disabled

    netsh int tcp set global rss=disabled

     

    2.       Disable TCP offload in the driver configuration. To do that, type in NCPA.CPL to bring network Connections.

    Right click the network adapter (s) that is (are) being used between DPM and SQL and select Properties.

    Click on Configure and then Advanced.

    In advanced, look for Large Send Offload (IPv4, IPv6),  Receive Side Scaling, Received Side Scaling Queues, TCP/IP Offloading, or something that recall these names. Disable them all.

     

    3.       Update Network Driver.

    Note: If you disabled options in step 2 before the update, certify that these settings weren’t re-enabled by the update.


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Monday, June 27, 2011 5:14 AM
    Moderator
  • Hello Wilson

    I've changed above mentioned settings on 2 servers. I will check tomorrow morning if there are any changes in backup times.

    Thanks, Grega


    Grega
    Monday, June 27, 2011 9:34 PM
  • Hello Wilson

    I've checked and it was even worse tonight. It wasn't able to transfer 15GB of changes in 7 hours.

    We are purhasing new SAN in the near future, and hopefully that will solve the problem, bit the mistery of performance still remains (for me).

    Thanks a lot for your help.

    Grega


    Grega
    Tuesday, June 28, 2011 5:43 AM
  • Interesting.

    That change should not affect for the worse unless the server is in high CPU stress.

    The setting we changed is to let the CPU to deal with the network traffic instead of the network adapter.


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Tuesday, June 28, 2011 5:48 AM
    Moderator
  • Both of these servers are virtualized and no signs of high CPU utilization this night. Only up to 25% says SCOM. I am sure that disks are a bottleneck, but what confuses me is that consistency check is performed much faster then the Express Full...

    Kind regards,

    Grega


    Grega
    Tuesday, June 28, 2011 5:51 AM
  • If you do babysit an Express Full, do you see data start being transferred few minutes after the job started or it stays @ 0 MB transferred for a long time until it starts sending data over quickly?

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Tuesday, June 28, 2011 5:54 AM
    Moderator
  • I'll be monitoring the process tonight and let you know.

     

    Kind regards, Grega


    Grega
    Tuesday, June 28, 2011 5:58 AM
  • Hello Wilson

    The following table shows the Express Full backup progress for one of the 400GB databases.

    Time elapsed Transferred data (MB)
    0 0
    0:02:19 0
    0:03:19 27
    0:03:39 52
    0:04:19 80
    0:04:39 93
    0:05:10 110
    0:08:19 229
    0:10:49 332

    Resource utilization on both servers (DPM and SQL) is the following:

    Average disk activity is 2MB/sec,  100% Highest active time and disk queue length almost 0.

    Available memory = 8 GB

    Average CPU utilization = 20%

     

    Kind regards,

    Grega


    Grega
    Tuesday, June 28, 2011 10:19 PM
  • So it doesn't matter if it is a huge amount of data to be transferred or small ones...

    In your example it took 10m49s to transfer 332 MB of data.

    Do you have any other type of data protection for this SQL server?

    The DPM Database is local to the DPM Server?


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Tuesday, June 28, 2011 10:42 PM
    Moderator
  • Hello Wilson

    At the end it took 6 hours 31 minutes for 30GB. When we established the same situation in the test environment (but on the same HW) it took only few minutes for the express full backup for this same database. We didn't chenge anything in the database on purpose and it only transferred 20MB.

    Only 5 databases are under protection on this server.

    DPM database is installed locally.

    Kind regards, Grega 


    Grega
    Wednesday, June 29, 2011 5:20 AM
  • Thank you for the update Grega,

     

    Let me look into this......


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Wednesday, June 29, 2011 5:21 AM
    Moderator
  • Can you check if this key is present on your SQL Server?

     

    HKLM\Software\Microsoft\Microsoft Data Protection Manager\Agents\2.0 

     ForceFixup:Dword

    If so, what is its value?


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Wednesday, June 29, 2011 11:24 PM
    Moderator
  • Hello Wilson

    No such key on the SQL server. Should it be there?

    Kind regards, Grega


    Grega
    Wednesday, June 29, 2011 11:28 PM
  • No. If it was there it could give us an explanation for the express full slowness....
    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Wednesday, June 29, 2011 11:29 PM
    Moderator
  • Grega, can you suply us the network card and driver version for both SQL and DPM Servers?

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, July 1, 2011 4:31 PM
    Moderator
  • Hello Wilson

     

    Both servers are on the VMware ESX 4.0 server.

    DPM server network: vmxnet3 network driver, C:\windows\system32\drivers\vmxnet3n61x64.sys, file version 1.1.6.0

    SQL server network: Intel Pro/1000 MT Network Connection, c:\windows\system32\DRIVERS\E1G6032E.sys, file version 8.4.1.0, built by: Winddk

     

    Kind regards, Grega


    Grega
    Friday, July 1, 2011 11:23 PM
  • Hello Wilson

    Have you maybe found something on the network configuration. Is there a way to enable some additional logging, to see what's happening during the backup?

    Kind regards,

    Grega


    Grega
    Thursday, July 21, 2011 8:03 PM
  • Grega,

    I was wondering if you ever found a fix for this?  I've been dealing with almost the exact same scenario; approximately 400GB database on SQL 2008 with a high level of daily changes, incremental backups run fine but express full backups only see 3-5 MB/s transfer.  I've had a case open with MS for months with no resolution.

    Friday, August 5, 2011 2:14 PM
  • Hello Jeremy

    No, unfortuantely I haven't found a solution for this. We are waiting for test SAN to see if there is any improvement.

    Kind regards,


    Grega
    Friday, August 5, 2011 4:41 PM
  • Hello Jeremy

    I was in a hurry when I was writting previous post.

    We did some detailed performance monitoring, and found out, that there was a really high latency on our SAN. For transaction logs (synchronization) we mounted a dedicated volume to a folder, to which DPM extracts transaction logs before copying them to storage pool. This way, we gained some performance.

    But for data file, we couldn't find any way to improve performance (beside those above, but they didn't help). I am still looking for an article explaining, how the data file is being compared to what DPM holds - is is block by block comparison, ...

    We even started thinking about going back to SQL server native backup for this database, because time spent was much shorter and less disk space was consumed holding these files compared to what DPM was doing before.

    But just like I said, we are waiting for a new SAN, to see what happens there.

    Kind regards,Grega


    Grega
    Friday, August 5, 2011 6:32 PM
  • Grega,

    Mind if I ask what SAN solution you're using, and what you're moving to?

    We've gone through a lot of stuff with MS, looking at performance data.  One of the things that they noticed was that our disk performance looked really bad.  They suggested creating a separate LUN in the storage pool, creating two custom volumes on that LUN, and moving the DB replica and recovery point volumes to them using http://technet.microsoft.com/en-us/library/dd282970.aspx.  While we don't have enough free space to do this right now, we simulated it by adjusting the backup schedules on other data sources that share the same storage hardware as our SQL database to skip a night, so that the SQL express full backup would run by itself.  It didn't solve the issue, but it did show a significant improvement in the performance;  about 3:30 hours instead of 5:00 hours.  Something you might want to consider on your new setup.

    Tuesday, August 16, 2011 7:22 PM
  • Hello Jeremy

    Thanks for the advice.

    At the moment we use HP MSA for the DPM. There is one disk array spanning over 24 disks and several LUNs presented to ESX servers used by VMs. The problem is that many VMs are accessing those disks at the same time. Creating separate LUNs wouldn't help, because the number of IOps is limited with the number of disks. What we expect with the new SAN is to spread the load among different SANs or among more disks.

    Kind regards,


    Grega
    Wednesday, August 17, 2011 7:26 AM