Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on defining specs for a new server to run DBCC CHECKDB scans on all databases in our portfolio (85 instances, 1300 DBs, 15 TB allocated). I have a custom application that copies dump files to a single server, then performs the restore and DBCC and required logging for reporting, but sequential I/O throughput is my bottleneck (I was working with set of RAID 6 LUNs from our SAN using 7.2K spindles, getting 300 MB/s read but only 50 MB/s write).

Is there a whitepaper or other resource available to help spec out a server for Maximum Consumption Rate specifically for DBCC? I looked at the MSFT whitepaper for Fast Track Data Warehouse servers, but I'm not sure it's the right fit.

Thinking outside the box, I'm contemplating going to a DAS enclosure like PowerVault and using RAID 0. The system DBs will be on RAID 1 on local storage but using RAID 0 for the restore and DBCC work makes sense because as soon as I finish testing a DB I drop the DB to make room for the next one. I can keep a few spindles as hot spares to rebuild a down array if needed and pick up where I leave off.

Any suggestions would be deeply appreciated!

share|improve this question
    
This is an interesting project, but if I'm understanding you correctly, you may be missing problems where your source DBs are by restoring and then checking them in a different location. Why would you not go the route of linked servers (I'm assuming these DBs are on different servers, diff instances at minimum) and remote DBCC calls like this? dbforums.com/microsoft-sql-server/… –  Eric Higgins Aug 10 '12 at 0:18
    
The primary reason for the remote testing is to avoid the performance hit for apps running 24/7. I work for a hospital and most clinical apps have some end-users online at all times. I do agree that it won't check for problems that occur on the source system disks; this will only confirm we have a good backup for DR. –  MattyZDBA Aug 10 '12 at 17:15
    
A requirement to actually do a restore of every DB every day is a strong requirement. I have trouble getting some companies to validate their ability to restore at all sometimes, let alone every DB every day. Kudos :) Do you have the storage to script a restore to a test DB physically where the source DBs already reside, instead of transferring them and doing it centrally? –  Eric Higgins Aug 10 '12 at 17:42
    
Storage isn't so much an issue as the risk of memory/CPU pressure, though I have few VLDBs close to 1 TB in the portfolio. The custom app I wrote is in VB.NET and uses SMO to poll for the DBs on an instance, find the latest backup files in the msdb tables, copy the files, perform a restore using WITH MOVEs and DBCC. Adding a command to do a DBCC on the source server in the existing code is easy, but finding the best time to run it in a shop shooting for five 9s is not :-). Copying the dump files to an alternate server to test without impacting Prod was the best option on-hand. –  MattyZDBA Aug 10 '12 at 17:53
    
I don't see that risk of memory or CPU pressure for doing a restore. If anything you'd have some I/O pressure if you were working the storage hard, but you could test that. Interesting project, good luck! –  Eric Higgins Aug 10 '12 at 18:16
add comment

1 Answer

up vote 1 down vote accepted

You want disks that are as fast as you can afford. DBCC CHECKDB is going to be limited by physical IO speed and pretty much nothing else.

That said, you do still need to run DBCC CHECKDB on your production server as you aren't doing physical IO checks on the production server at this point.

share|improve this answer
    
Thanks Denny! Quick follow-up: Would it be suitable to use the WITH PHYSICIAL_ONLY option on the production hosts daily (like before full backups) and then do the full check on the alternate server, or should a full check be done periodically on Prod due to addition checks that can find problems on the host machine not covered by PHYSICAL_ONLY? –  MattyZDBA Aug 10 '12 at 17:21
    
Doing WITH PHYSICAL_ONLY on the production server should be fine as long as you are running a full DBCC on the other server. –  mrdenny Aug 10 '12 at 19:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.