:::: MENU ::::

SQL Server 2012 AlwaysOn backups and checkdb

The other day I read the marketing fluff on the new possibillities to backup from a secondary replica, decided to find out more about it.

After reading up on what it can and can’t do my conclusion is this:

Full backup – Only on primary replica

Full backup with copy_only – Primary and secondary replicas

Differential – Only on primary replica

Log backup – Primary and secondary replicas

So what does this mean then? Well, if diff backups are in your plan now and you don’t want to change it, then stick with full backups and diff backups on primary replica, consider offloading log backups to secondary replica.  Because diff backups can not be based on a full backup with copy_only and a secondary replica can only do full backups with copy_only.

If you’re running full backups and log backups do consider running them on a secondary replica, I’ve tested restoring a full backup with copy_only leaving it with norecovery and then restoring a log backup after and it worked. But do your own testing please!

Also, if you’re backing up from the secondary replica all the time, do some restores every now and then on a testserver and run dbcc checkdb on it. Just to make sure.

Also 2, you can still backup from a secondary replica even if it is not readable. Still only full with copy_only and log backups but it’s better than nothing.

On another note, but related, today I ran a DBCC CheckDB() on a non-readable database, the secondary replica in an Availability Group, setup with synchronous mirroring, and to my coworkers surprise; it worked!

 

So to sum it up: On a secondary replica that is non-readable we can do:

  • Full backup with copy_only
  • log backup
  • dbcc checkdb

 

Source: http://msdn.microsoft.com/en-us/library/hh710053.aspx

//R

 


2 Comments

  • Matt White |

    Hi,

    Just a note to say you mention running CheckDB on a Secondary, but it should be pointed out this does not tell you if your Primary Database has Physical Corruption or not; only a CheckDB run on the Primary or a Full Database backup which has been restored will tell you this. Physical corruption does not go through the logs that are replayed on the Secondary’s; this is why automatic Page Recovery can work by grabbing the corrupt page from the Secondary and fixing the Primary. You should always be running at least the Physical only option on the Primary often, or offloading this to a restore server, along with the full logical checks often as well on the restore server.

    Thanks,
    Matt.

  • Richard |

    Hi Matt!

    Yes you’re right about checkdb, and in my opinion it should be run on the primary replica, I just wanted to point out that it is possible to run it on a non-readable secondary replica, not that you should only run it there.
    Maybe best solution is to run it on both replicas if it’s possible.

    Thanks for your comment!
    Richard

So, what do you think ?