Sql-server – SQL Server Always on DBCC Check DB and Backups startegey

sql serversql-server-2019

Can we run DBCC CheckDB on secondary and perform our database backups with checksum on Primary.
This will make sure if corruption happens on primary, backup will fail and Primary will use sys.dm_hadr_auto_page_repair to repair page from secondary node

Also running CheckDB on Secondary node will always keep DBCC related resource utilization on secondary only.

Best Answer

Brent Ozar has a good article regarding this here: Where to Run DBCC on Always On Availability Groups

Specifically these parts of the article are relevant to your question (his example is kind of the reverse of yours, but the same principal applies in regards to the importance of running DBCC CHECKDB on both replicas):

We could be backing up corrupt data every night as part of our full backups. If we experienced corruption on the primary server, and it tried to take care of business with automatic page repair by fetching a copy from the secondary server (which also happened to be corrupt), we’d be screwed.

The moral of the story: with Availability Groups, we need to run DBCCs on whatever server is doing our full backups, too. Doing them on the primary database server isn’t enough. In a perfect world, I’d run them regularly on any server that could serve a role as the primary database. Automatic page repair can only save your bacon if a replica is online and has a clean copy of the page.

In short: By backing up the Primary replica without running DBCC CHECKDB on it, you could be backing up corrupt data without realizing, especially more so when you've experienced a failover event that caused your replicas to switch places in the interim, for your specific scenario.

Related Question