Running DBCC checkdb in RDS

Question

In an RDS SQL Server multi-AZ configuration, as I understand it the customer is responsible for running consistency checks using DBCC checkdb command.   What are the recommendations for this with respect to the standby replica?   As this database is not in an available state is it the case that one should failover, run the check then failback to the primary if desired.  Or alternatively should one take a  native backup of the standby database and restore/recover it in order to run checkdb ? Or is it the case that the mode of operation means that a check on the primary implicitly confirms that the standby will also be consistent? 
 Any advice or best practice guidance would be welcomed.

Sanketh Salian · Accepted Answer

Hello,

Your understanding with shared responsibility is absolutely right. Customers are expected to execute DBCC checks on user databases, whereas AWS handles DBCC checks for system databases during the daily maintenance window.

As indicated in the query, the stand-by instance is read-only and does not allow modification or executing DBCC queries or running a SQL job. Due to this limitation, you will not be able to perform the DBCC check on the secondary instance of Multi-AZ setup.

Hence, if you would like to execute DBCC command on the secondary instance, then you would have to likely failover and perform the operation. Additionally, I would request you to review this document[1].

Please note, the storage utilized by primary and secondary instances are different. Hence, a I/O sub-system related corruption in primary would not be observed on secondary instance. Additionally, DBCC command is not replicated to secondary instance. Hence, a successful execution on primary instance does not indicate stand-by has similar behavior.

Based on this, if you would like to review the stand-by instance status, you can failover during the approved downtime window and execute the DBCC commands on user database to confirm further.

[1] https://sqlstarters.com/2018/06/26/offloading-dbcc-checkdb/

Running DBCC checkdb in RDS

Relevant content