1

What is the best way to schedule DBCC check on all the nodes in Always On availability group. Can we just schedule the same job on all the nodes at the same time including all the secondaries. In my environment all the back ups are happening on the Primary Node only. Any expert advice will be highly appreciated. I intend to use the Ola hallengren scripts.

1

Checkdb should be run on both primary and secondary replicas. Normally for big databases and where you have busy system and you cannot afford to run checkdb on both replicas in one go specially on primary replica, people run checkdb on all the secondary replica and failover the primary to secondary making secondary new primary. Now you have new secondary on which you can run checkdb. This allows you to run checkdb on all the nodes without affecting primary workload.

When you run checkdb on secondary primary do gets affected because secondary has to create snapshot and run recovery on it so always run checkdb(even on secondary replica) when load on your complete AG is relatively very less or during downtime.

If you can run checkdb on all the replicas once in a week that would be best thing to do. There are options like physical_only which minimises the impact of checkdb but remember if you use this option the checkdb does not do complete consistency check

Must read:

4
  • Thanks for the advice. Instead of failing over to the secondary and running checkdb, will it be advisable to run checkdb on secondary just on different date/time instead of the same time when it runs on the primary. Feb 22 '19 at 18:50
  • I mean to say you can choose a period of time when load is very less and run checkdb on secondary replica, if that finishes without error this means your database on secondary replica is free of corruption. But the task is half done so to complete the task failover to secondary(on which you ran checkdb) making it new primary. Now again at some suitable time run checkdb on new secondary(which were previously primary. Got it ?
    – Shanky
    Feb 22 '19 at 18:53
  • Thanks for the suggestion. What I did was Primary had a fixed time/date and rest of the secondaries were scheduled at different time/date other then the primary. This enables me as you said to do the corruption check on all the nodes but without having to failover. All the jobs were scheduled during off hours .. Feb 22 '19 at 18:59
  • 1
    Failover is only required if you are not able to do primary and still want to make sure the database is corruption free.If you have downtime then go as you have mentioned
    – Shanky
    Feb 22 '19 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.