Sql-server – find guidelines for building a SQL Server machine specifically for DBCC CHECKDB work

According to Erland's Sommarskog's article about dynamic sql, the use of parameterized queries should be the best point in fighting against sql injection. About performance, I also see no problem, because the execution plan of dynamic sql is reused since MSSQl 2005, if I remember correctly. Only in sql 2000 and older there was a problem with plan reuse.

His points in taking over sql injection are: "

* Never run with more privileges than necessary. Users that log into an application with their own login should normally only have EXEC permissions on stored procedures. If you use dynamic SQL, it should be confined to reading operations so that users only need SELECT permissions. A web site that logs into a database should not have any elevated privileges, preferably only EXEC and (maybe) SELECT permissions. Never let the web site log in as sa!

* For web applications: never expose error messages from SQL Server to the end user.

* Always used parameterised statements. That is, in a T-SQL procedure use sp_executesql, not EXEC().

The first point is mainly a safeguard, so that if there is a injection hole, the intruder will not be able to do that much harm. The second point makes the task for the attacker more difficult as he cannot get feedback from his attempts."

The following is a compilation of results that I read up on. You will find vastly more information in the linked blogs and documents.

First, it can happen that DBCC CHECKDB won't detect inconsistencies if you turn off checksum or torn_page verification. A quote from Paul Randal:

You're right - if torn-page or checksum isn't turned on then there's nothing that can be detected as far as page protection options are concerned. CHECKDB may still pick up on corruptions that it finds from doing all the consistency checks that it does - but it won't see corruptions in the middle of data values, for instance.

Ha - that's the bummer about turning on page checksums - nothing happens until a page is read in, changed, and written back out. The only way to force pages to get checksums is to make them change - e.g. through rebuilding all your indexes, whcih may be unpalatable - there's no 'touch' tool out there at all.

The above situation can hit you, if you upgraded a database from SQL Server 2000 or before to 2005 or later. You then need to manually enable page checksums with ALTER DATABASE to get them active. But then the 2nd paragraph of the above quote kicks in and might trouble you.

BACKUP WITH CHECKSUM will detect checksum inconsistencies, but only if the page already had a checksum written to it, when it is being backed up. Normally DBCC CHECKDB also detects these errors, so it's not a good idea to "use BACKUP WITH CHECKSUM to replace DBCC CHECKDB".

Now there is a second possibility for DBCC CHECKDB not to show any inconsistencies, even if there are some. For this I'm just quoting again Paul Randal in "Misconceptions around corruptions: can they disappear?":

So what about the disappearing corruptions? This gets into how consistency checks work. Consistency checks only run on the pages in the database that are allocated. If a page isn't allocated to anything, then the 8192 bytes of it are meaningless and can't be interpreted. Don't get confused between reserved and allocated - I explain that in the first misconceptions post here. As long as a page is allocated, it will be consistency checked by DBCC CHECKDB, including testing the page checksum, if it exists. A corruption can seem to 'disappear' if a corrupt page is allocated at the time a DBCC CHECKDB runs, but is then deallocated by the time the next DBCC CHECKDB runs. The first time it will be reported as corrupt, but the second time it's not allocated, so it isn't consistency checked and won't be reported as corrupt. The corruption looks like it's mysteriously vanished. But it hasn't - it's just that the corrupt page is no longer allocated. There's nothing stopping SQL Server deallocating a corrupt page - in fact, that's what many of the DBCC CHECKDB repairs do - deallocate what's broken, and fix up all the links.

I don't have a final answer to your question, but as DBCC CHECKDB only checks allocated pages it won't show inconsistencies in deallocated pages. The only situation I can imagine now is that BACKUP also backups those deallocated pages showing potential checksum errors that were skipped by DBCC CHECKDB.