none
How DPM 2010 scale to large SQL environment on disk protection RRS feed

  • General discussion

  • Hi,

    Background: We have a customer with large SQL environment and is in pilot phase at moment of deploying DPM agents to all SQL servers. At moment we only protecting the DEV and QA SQL servers.  In total we aim to protect more then 1160 databases on about 60 SQL servers with total of 48 TB in size. Only a few databases are bigger then 5 TB each most are avg sizes.   We have 4 DPM 2010 servers at production site and 4 at DR site.   Splitting the work load across the four DPM servers.  Each DPM server is a VM and the host connection is 2 x 1Gbps ethernet adapters channeled giving 2 Gbps.  Connection to the SAN is via 8 Gbps HBA adapters with multiple paths.

    Concerns:  

    • Consistency Checks - We backed up a 2.2TB SQL DB, the initial replica creation took just over 8 hours. We restored it back to its original location. However before we could resume normal recovery point creation we had to run a consistency check. The consistency check ran well over 7 hours and during that time we were unable to create recovery points for that DB.
    • SQL DB restore to latest - we want to do restore from Prod to Dev with latest. We can last full express, once restore complete few hours pasted and we cannot take latest sync (Diffenratial restore not possible).
    • Replica Volume utilization - Capacity is also issue because we taking backups to disk, but the allocated space and used space show pleanty of wasted/unused space in replica volume.

    Please give me your comments:  Can DPM scale to this size of SQL protection, and what is your suggestion to efficiently backup this environment?

    Thanks

    Juan

     


    JuanB
    Wednesday, January 19, 2011 11:52 AM

All replies

  • Hi Juan,

    Wow, that are some serious db's!

    in regards to your concerns

    i'll just give you my 2 cents.

    Consistency checks - that is normal operation, the replica needs to be consistent before we can create recoverypoints. i'm actually a bit surprised that your CC completed faster then you initial replica creation. a CC does a Compare and therefore it could take longer.

    diff restore - not sure what you mean here, you would like to continue DB protection while the restore is running?

    volume utilization - DPM uses a default allocation 1,5 times the protected storage for its replica volume. you might want to check out the storage calculators http://go.microsoft.com/fwlink/?LinkID=180658

    regards,

    Alex

    Wednesday, January 19, 2011 12:31 PM
  • Hi Alex

    Thanks for the responce.

    Do think it is possible to schedule the consistency check via powershell to only run once a week or turn the depency for it completely off?

    Differential restore; because our DB's are so big we want to get a copy of the DEV as close to the PROD. So currently with IBM Tivoli we restore our latest backup and then take diff of the changes that happend on the PROD during the full restore, and restore the Diff to the DEV after, thus giving us the closes to PROD as possible.  I think the DBA's are used to this feature and would have like to have this if DPM could do it.

    Volume utilization: example of a very small DB, Default retention 5 days | 15 minutes synchronization short term using disk uses on replica volume(co-located) 10GB allocates, 104.33 MB used.  The Recovery point volume (co-located); 7.58 GB allocated, 674.84 MB used.    When you attempt to schrink (Modify disk allocation) some of the data sources give me this message:   http://technet.microsoft.com/en-us/library/gg232569.aspx

    Here is my storage calculations for SQL some 14 day retention and other 28 Days:

    SQL Databases (14 Day retention)
    Number of SQL Servers 59
    Total size of used space by all SQL Database (12 Nov 2010) 42808.32  GB
    Days Retention 14  Days
    log change Unknown
    alert threshold Unknown
    Replica volume (SQL Server data = Data source size x (1 + log change) / (alert threshold - .05) * incomplete data
    Recovery point volume (2.5 x retention range in days x log change x data source size + 1600 MB) * incomplete data
    Estimate log change 6%
    Typical alert threshold  90%
    Estimated Replica volumes 53384.49  GB
    Estimated Recovery point volumes 89991.87  GB
    Total Storage required for SQL Databases *incomplete data
    Estimated Storage required for SQL Database 143.38   TB
    SQL Databases (28 Day retention)
    Number of SQL Servers 17
    Total size of used space by all SQL Database (12 Nov 2010) 24771.09  GB
    Days Retention 28  Days
    log change Unknown
    alert threshold Unknown
    Replica volume (SQL Server data = Data source size x (1 + log change) / (alert threshold - .05) * incomplete data
    Recovery point volume (2.5 x retention range in days x log change x data source size + 1600 MB) * incomplete data
    Estimate log change 6%
    Typical alert threshold  90%
    Estimated Replica volumes 30891.01 GB
    Estimated Recovery point volumes 104065.78 GB
    Total Storage required for SQL Databases *incomplete data
    Estimated Storage required for SQL Database 134.96  TB

     

     

    Juan

     

     


    JuanB
    Wednesday, January 19, 2011 12:58 PM
  • Hi Juan,

    you have an option to scchedule a CC when you create the PG, it will only run when the datasource is inconsistent with the protected data. in this case you resostored it to it's original location and thus for protection to continue you need a CC.

    you can restore a SQL db in an non operatable state and restore some additional log files to get to a later state. don't know if this will fit your situation, but worth a try.

    the message you recive is normal operation. it points out that data is near the end of the volume. this could clear up if older recovery points are pruned. thing to note is that you use a Colocated replica and recovery point volume. meaning that more backups are stored in in these volumes.

     do you volume sizes differ much from the calculated values?

    Alex

    Wednesday, January 19, 2011 1:35 PM
  • Alex,

    Thanks again for your comments.  I understand the CC process, was hoping for alternative to get away loosing so much time when it get inconsistent and CC must happen.

    The SQL in a non-operational state is option I will ask the DBA's to check if it works for them.

    My allocate volume sizes are very similar to my calculated values, but we have not added all SQL data source only select few for pilot/testing.

    I would like to hear from more people in community if there are similar case studies where SQL volumes of this size are protected?

     


    JuanB
    Thursday, January 20, 2011 8:36 AM