With SQL Server 2012′s new AlwaysOn Availability Groups, we have the ability to run queries, backups, and even DBCCs on database replicas. But should we?
Paul Randal (Blog – @PaulRandal) covered this issue in today’s SQLskills Insider newsletter, and he says:
“It’s not checking the I/O subsystem of the log shipping primary database – only that the log backups are working correctly…. Log shipping only ships transaction log backups – so any corruptions to the data files from the I/O subsystem on the primary will likely not be detected by the offloaded consistency checks…. I’ve heard it discussed that the SQL Server 2012 Availability Groups feature can allow consistency checks to be offloaded to one of the secondary copies – no – by the same argument.”
Actually – brace yourself – I’m about to suggest that Paul’s DBCC advice needs to be even stronger. Take the following configuration:
- SQLPRIMARY – primary replica where users connect. We run DBCC here daily.
- SQLNUMBERTWO – secondary replica where we do our full and transaction log backups.
When Bacon Flies
In this scenario, the DBCCs on the primary server aren’t testing the data we’re backing up. 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. We wouldn’t have a clean backup of the page, and our data would be permanently lost.
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.
One group I’m working with has taken a rather unique approach to running DBCCs. They can’t afford the performance overhead of running DBCC or backups on the primary replica (ServerA), so every night they run backups and DBCC on a secondary (asynchronous) replica in the same datacenter (ServerB). On Saturday night during their regularly scheduled outage, they switch into synchronous, get the boxes in sync, and then fail over to ServerB. Then they run on ServerB all week long, and run DBCCs on ServerA every night. It’s more manual work, but the payoff is a blazing fast and safe primary node. (And in case you’re wondering about lost transactions in async mode, they’ve thought about that too – when the server becomes mission-critical, they plan to add a ServerC instance acting as a synchronous replica as well.)
And no, I never thought I’d write that Paul Randal isn’t telling you to run DBCC often enough. 
Brent Ozar
Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.
Follow Me:
[...] Where to Run DBCC on AlwaysOn Availability Groups - Running CHECKDB on a large database can be a real administrative pain. Brent Ozar (Blog|Twitter) considers how you might want to go about doing this in SQL 2012. [...]
Hi Brent.
This approach seems a bit dangerous to me. If data file corruptions on the primary cant (or are unlikely to be) be detected on a secondary. Then any corruption wont be found until failover upto six days later!
It also doesnt feel right to rely on copy only backups for DR.
Hi, Brian. Can you elaborate a little more about exactly what part seems dangerous? Corruption is automatically repaired in the background with AlwaysOn Availability Groups whenever it’s detected, and you can have up to 4 replicas all with uncorrupted versions of the data. Corruption repair no longer takes downtime. What’s dangerous about that?
I do want to know fairly quickly when storage starts to get corrupted on any given replica (like within a week) but I can’t usually afford to run DBCC on every replica, every night. As long as I’ve got one clean replica, I can always use that as my primary.
Hi Brent
I was thinking along the lines that that the auto page repair cant repair all page types (file header, boot, GAM, SGAM or PFS). Therfore you would dbcc the secondary, getting the all clear each night, when in fact the primary is in trouble.
But when you say “the primary is in trouble,” what’s our traditional mode of repairing those types of pages? We restore the database or do page level restores. The point of AlwaysOn Availability Groups is that you’ve got a replica to fail over to now, with independent data page storage. When you detect corruption, fail over. I’m missing the danger here.
Yes, sooner or later the primary will corrupt, and we’ll need to fail over. It’ll happen automatically when SQL tries to read the corrupted page – given that we’ve properly set up AGs and failover. Running DBCC frequently on the primary doesn’t change the corruption – it just means an earlier failover to the secondary.
Ahhh.. thanks Brent. The penny/cent has finally dropped (I’m having a bad hair day or something).
I’m implementing AlwaysOn availability groups within the next 8-10 weeks and the scenario is simliar. I only started seriously looking at the technology yesterday. So I’m still at the stage where p45′s are looming in every shadow
!