none
New to backing up SQL with DPM RRS feed

  • Question

  • Hi all.   I am not an sql guy and I was just gifted some SQL 2008 servers to back up.   With hyper-v or files servers I would know what to backup, how often, how to restore and want not.   That's old hat. 

    With SQL I don't now what to do.   Can some one give me some simple rules to start with, in he lessionsed learned.

    Plus some pointers to reading materials for sql backup/recover of sql with DPM.

    I can do the protection group wizard, but I don't know what I am doing in the sql section.


    Eric (Trying to be truly Wise by leaning form the mistakes of others, my success rate says I need to work harder at that.)

    Friday, July 20, 2012 8:35 PM

Answers

  • See below screenshot.


    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.

    • Marked as answer by Eric Hosmer Monday, July 23, 2012 3:42 PM
    Monday, July 23, 2012 3:25 PM
    Moderator

All replies

  • Hi,

    Tip-1) SQL 2008 supports parallel backups for up to 8 DB's per SQL server at one time.  Depending on the number of DB's you need to backup and how often, you may want to think about spreading the SQL protection across serveral protection groups.  DPM serializes SQL backups withing a PG, but if you have 2 or more PG's with SQL databases in each, then you can acheive parallel backups.

    See the following: http://technet.microsoft.com/en-us/library/hh757781.aspx

    TiP-2)  Depending on the SQL recover model on a database, DPM will either schedule incremental sync backup + express full, or only schedule express full backup.  You can see more about this at http://technet.microsoft.com/en-us/library/hh757802.aspx.   So get with the database owners and find out what there SLA is. If they only need a single backup per day, but it's in full recovery mode, then select to synchronize ever 24 hrs and schedule a single express full backup.  If your SLA is 1 hr and are in full recover mode, then schedule hourly syncronizations and X number of express full.  If you have lots of different SLA's place databases with common SLA in the same PG.

    NOTE: There is a 4200 express full backup limit per protected SQL Server per week. So take 4200/7 = 600/#DB's on the protected SQL Server  and that will give you the [Max number of EF backups per day] to avoid the alert.

    Tip-3)  By default DPM will want to co-locate SQL databases on the same replica in a PG. 

    We can co-locate up to 2000 small SQL databases to overcome the 300 limit data source limit (non-collocated limit).
    We allow up to 200 SQL DB’s / PG.
    We allow up to 30DB’s to be collocated on a single replica volume.

    The default SQL Co-located replica volume that DPM creates by default is 10GB meaning we will co-locate as many SQL databases that fit on that 10GB volume up to 30 before we create another 10GB volume.

    Depending on your average size database, you can modify that registry key so you can take better advantage of the co-location feature.

    The key is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Data Protection Manager\Collocation\SQL
      Valuename= CollocatedReplicaSize and should be a multiple of 1GB  (1073741824 bytes decimal)

    Restores are like any data source, you select a point in time to restore to and when it completes that is the state of that database.  If you choose the restore from "Latest" - that will always restore on top of the current database, then replay the latest SQL log on the PS and bring the database up to the very last transaction.

    TIP-4) You can configure SQL Server Recovery Tool (SSRT) to allow DB administrators to recover their own databases. That gives them direct control over recoveries without involving a DPM administrator. 


    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.

    Saturday, July 21, 2012 2:48 PM
    Moderator
  • Mike

    Lots of good stuff.    Where do I "schedule a single express full backup".   I don't see that in DPM just the syncronization frequency.


    Eric (Trying to be truly Wise by leaning form the mistakes of others, my success rate says I need to work harder at that.)

    Monday, July 23, 2012 3:06 PM
  • See below screenshot.


    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.

    • Marked as answer by Eric Hosmer Monday, July 23, 2012 3:42 PM
    Monday, July 23, 2012 3:25 PM
    Moderator