none
A few SQL related protection group Questions - DPM 2012 R2 RRS feed

  • Question

  • Hi - Here is my environment: DPM 2012 R2 backing up SQL 2012 DBs. Some of the DBs are simple recovery model, some are full recovery model in the same protection group.

    1) Regarding the fact that DPM only looks to the recovery model when the DB is first added to the protection group, if I ever changed the recovery model in the future, would stepping through the "Modify Protection Group" wizard without changing anything be the solution for DPM not changing its methodology on the backing up or not backing up the log files? Or would I have to remove the affected databases from the protection group and add them back in?

    2) I have disk protection configured for 30 Days, sync every 15 minutes, and application recovery points once per day. Online protection is configured for 90 Days. Does this mean my disk consumption on the DPM side for full model databases will be the full size of the databases, plus the amount of changes observed over 30 days? OR does it mean it will keep 30 full copies of the DB? I am fairly sure the former.

    3) If I do decide to reduce the retention range on the disk side of this protection group to say 14 days, are there any specific issues with changing that after the PG has been working for some time?

    4) My former backup solution had an option to run consistency checks after every SQL job. Is that still considered best practice? Would the "Run a daily consistency check" be a better choice than the "Only run if a replica becomes inconsistent" option that I currently have in place? Would there be any ramifications to enabling this now after this PG has been in place for some time?

    Thanks very much!

    Friday, April 17, 2015 5:35 PM

Answers

  • Hi

    Q1) Regarding the fact that DPM only looks to the recovery model when the DB is first added to the protection group, if I ever changed the recovery model in the future, would stepping through the "Modify Protection Group" wizard without changing anything be the solution for DPM not changing its methodology on the backing up or not backing up the log files? Or would I have to remove the affected databases from the protection group and add them back in?

    A1) Changing the Recovery Model of a Database

    To change the recovery model of a protected database to the simple recovery model

    1. Stop protection of the database, selecting the retain replica option.

    2. Change the recovery model on the SQL Server database.

    3. Add the database to a protection group.

    Q2) I have disk protection configured for 30 Days, sync every 15 minutes, and application recovery points once per day. Online protection is configured for 90 Days. Does this mean my disk consumption on the DPM side for full model databases will be the full size of the databases, plus the amount of changes observed over 30 days? OR does it mean it will keep 30 full copies of the DB? I am fairly sure the former.

    A2) Your disk consumption on the DPM will be for size of model databases plus the amount of changes observed over 30 days.

    Q3) If I do decide to reduce the retention range on the disk side of this protection group to say 14 days, are there any specific issues with changing that after the PG has been working for some time?

    A4) Not really, but to gain back the potential extra space allocated for the original 30 day retention - you can try shrinking the recovery point volume once the retention range is reduced and the older recovery points are pruned.  I would wait until after midnight to be sure the old RP's are deleted, then try to shrink.

    Q4) My former backup solution had an option to run consistency checks after every SQL job. Is that still considered best practice? Would the "Run a daily consistency check" be a better choice than the "Only run if a replica becomes inconsistent" option that I currently have in place? Would there be any ramifications to enabling this now after this PG has been in place for some time?

    A4)  A consistency check will only run if DPM detects the replica is inconsistent.  Even if you have the option selected to run nightly, if the replica is already consistent a consistency check will be skipped.


    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.

    Friday, April 17, 2015 10:24 PM
    Moderator

All replies

  • Hi

    Q1) Regarding the fact that DPM only looks to the recovery model when the DB is first added to the protection group, if I ever changed the recovery model in the future, would stepping through the "Modify Protection Group" wizard without changing anything be the solution for DPM not changing its methodology on the backing up or not backing up the log files? Or would I have to remove the affected databases from the protection group and add them back in?

    A1) Changing the Recovery Model of a Database

    To change the recovery model of a protected database to the simple recovery model

    1. Stop protection of the database, selecting the retain replica option.

    2. Change the recovery model on the SQL Server database.

    3. Add the database to a protection group.

    Q2) I have disk protection configured for 30 Days, sync every 15 minutes, and application recovery points once per day. Online protection is configured for 90 Days. Does this mean my disk consumption on the DPM side for full model databases will be the full size of the databases, plus the amount of changes observed over 30 days? OR does it mean it will keep 30 full copies of the DB? I am fairly sure the former.

    A2) Your disk consumption on the DPM will be for size of model databases plus the amount of changes observed over 30 days.

    Q3) If I do decide to reduce the retention range on the disk side of this protection group to say 14 days, are there any specific issues with changing that after the PG has been working for some time?

    A4) Not really, but to gain back the potential extra space allocated for the original 30 day retention - you can try shrinking the recovery point volume once the retention range is reduced and the older recovery points are pruned.  I would wait until after midnight to be sure the old RP's are deleted, then try to shrink.

    Q4) My former backup solution had an option to run consistency checks after every SQL job. Is that still considered best practice? Would the "Run a daily consistency check" be a better choice than the "Only run if a replica becomes inconsistent" option that I currently have in place? Would there be any ramifications to enabling this now after this PG has been in place for some time?

    A4)  A consistency check will only run if DPM detects the replica is inconsistent.  Even if you have the option selected to run nightly, if the replica is already consistent a consistency check will be skipped.


    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.

    Friday, April 17, 2015 10:24 PM
    Moderator
  • Thanks for the above answers, and now I have a followup question:

    Regarding changing the recovery mode of a database for one that is already in a Protection group, the above response (see #1)  provides the answer for changing the recovery mode from full to simple. I have the reverse scenario, I have a database in a protection group and I wish to change the recovery model from simple to full for a production database. Since the SQL recovery mode is set to simple, transaction log backups are not being performed and the log file is almost as large as the database file (20 GB). Are there any DPM caveats to changing to full?

    Also, MSDN recommends the below steps when changing the recovery model from simple to full (https://msdn.microsoft.com/en-us/library/ms189272%28v=sql.110%29.aspx). How do I translate the steps into DPM actions? In other words, how can I force DPM to perform an Express Full backup on this database while it is protected by the protection group?

      • Immediately after switching to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.

        NoteNote

        The switch to the full or bulk-logged recovery model takes effect only after the first data backup.

      • Schedule regular log backups, and update your restore plan accordingly.

        Important noteImportant

        If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space.

      Thanks!

    Tuesday, July 21, 2015 1:14 PM
  • Hi,

    Typically, truncation occurs automatically under the simple recovery model when database is backed up (express full) and under the full recovery model when the transaction log is backed up (synchronization)  However, truncation can be delayed by a number of factors. For more information, see Factors That Can Delay Log Truncation.

    Once you stop and restart protection after changing recovery model, DPM will automatically schedule the correct backups (express full / synchronizations) based on the recovery model.   You will not see synchronization jobs scheduled for DB's in simple recovery model.


    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, July 21, 2015 1:58 PM
    Moderator
  • Thanks, so then you recommend following the same steps as above for changing from simple to full? That is:

    1. Stop protection of the database, selecting the retain replica option. (Does the "stop protection of member" apply to just the one database, or will it stop protection of aall databases on the SQL server in the PG?)

    2. Change the recovery model on the SQL Server database.

    3. Add the database back to the same protection group.

    Tuesday, July 21, 2015 2:15 PM
  • Hi,

    Yes, you have the option as to what individual data source you want to stop protection for.  In step 3. if you are running DPM 2012 R2 +ur5 or later, be sure to click on the "refresh" button on the SQL server before expanding out the data sources so DPM does a full inquiry and gets the current recovery model for the databases.


    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, July 21, 2015 2:56 PM
    Moderator