Is it necessary to do a database integrity check (DBCC check) on mirrored databases?
Automatic page repair is supported by database mirroring and by Always On availability groups. After certain types of errors corrupt a page, making it unreadable, a database mirroring partner (principal or mirror) or an availability replica (primary or secondary) attempts to automatically recover the page.
What is you best practice?
No, that's one of the main benefits of mirroring is the page repair; it checks for corruption and then tries to address it if it finds any.
Q1
Is it necessary to do a database integrity check (DBCC check) on mirrored databases?
Comment reply by Denis Rubashkin
Mirrored databases are in restoring state so you wouldn't be able to run dbcc checkdb on them even if you wanted that.
Q2
Yes, right, but I mean principal database (which is mirrored) , not a mirror one
Yes, you should.
A few of many more reasons as to why
What if a page that is rarely used gets corrupted?
When do you want to find that out?
Consider a scenario when a disk suddenly became unresponsive when SQL Server was writing into it. This will obviously cause the data to become corrupt; however, this corruption will be detected only when we try to access the pages that had become corrupt; and this can be months after the corruption was actually introduced. Similarly, a bit flip while writing data to the disk subsystem, caused by a disk driver, can cause corruption, and this might be actually detected at a much later point in time.
Kernel Drivers and softwares like Antivirus, that have the ability to take Kernel Level Handle on files, are also known to cause database corruption issues. In case you have Antivirus Installed on the system, please ensure that you exclude the SQL Server Database Files from Antivirus scans.
In short
You should always run integrity checks on your (principal) database(s). If the database is too big the next best thing is offloading the dbcc checkdb to a restored backup. More on that here
Corruption can still occur due to hardware failure, an application such as antivirus getting access to the pages and causing corruption, ....
I am also not sure if there is any risk if mirroring does not repair pages in some cases
Another reason why you should be doing integrity checks.
You could use ola hallengren's solution for running integrity checks.
Great reply from Randi Vertongen. So, my understanding about DBCC - It does majorly CHECKALLOC, CHECKCATALOG and CHECKTABLE. For a VLDB, if i backup and restore the DB to another system it could be success. However, i'm supposed to run DBCC on the production DB which also checks underlying storage? Pl Correct me, i'm thinking DBCC also does some checks on the allocated storage too along with validating integrity of GAM,SGAM,IAM pages. Or it does not perform any checks on the storage? My Q is, if i restore the DB to a non-prod server and DBCC it may run without any issues but my production system may have issues?
