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?
“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.
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. 😉