In one of my presentations recently I was recommending DBCC CHECKDB on every database every day. I realize that isn’t always possible or practical, so I noted that if you don’t have resources on your production server, or enough spare hardware, you should at least run it on every database once a month. At least on the database you care about.
Someone in the audience asked if they could just script DBCC PAGE on every page in the database instead. I wasn’t sure if that was accurate, but I didn’t think it was. So I asked THE MAN, and he confirmed this doesn’t equate to a DBCC CHECKDB.
I won’t attempt to give a complete explanation, mostly because I’m sure I’d miss something or be incorrect, but I will tell you how I feel about this, based on what I know.
CHECKDB performs an extensive evaluation of not only all objects (and hence their pages), but also their linkages. It performs a more complete check by default, but you can add the PHYSICAL_ONLY flag to speed things up and limit the checks to just the physical structures and allocations. PHYSICAL_ONLY also skips Filestream checks.
The DBCC PAGE command, undocumented, works, but it doesn’t really examine if the links and relationships between pages are correct.
I can’t say that DBCC PAGE couldn’t be use to detect corruption or find issues, but I wouldn’t depend on it. YMMV, but I wouldn’t use this as a substitute for CHECKDB.
I told people in New York at SQL in the City that I’d post some resources on the blog from my talk. My apologies for not getting it done over the weekend, but during a little downtime in Austin I’m getting it done.
Checklist
From the last slide, a checklist of things for you to look at on your instances.
Backups scheduled on all database (full and log)
DBCC CHECKDB running regularly on all databases
Test restores scheduled
Manage mdf/ndf/ldf file sizes
Proactively monitor and maintain indexes and statistics
Monitor jobs and set up alerts
Challenge
At work, someday soon, but in the next 30 days, go through the checklist on your important servers, or all your production servers, and assess your maintenance.
Resources
From the slide deck, which will come soon in email. These are a list of links and resources from the talk.
This editorial was originally published on Nov 19, 2007. It is being republished as Steve is on vacation.
When I started in IT things were much simpler. We had smaller data sets, but hardware was larger. There just wasn’t a good way to transfer large amounts of data on 3 1/2″ floppy drives. I remember CDs coming into being and CD writers became a concern, but since few people had them and we knew who they were, it wasn’t a big problem. The zip drives created a cause for concern briefly with their 100MB capacity, but again, they were somewhat bulky and easy to spot.
However with the growing sizes of flash drives, storage in cell phones, and the monstrous capacity of iPods and other music players, it seems that data has little chance of being contained by IT within the walls of the organization.
I saw an interesting discussion about managing all these technology devices and the problems that come with so much storage being in reach for so many people.
So we’re data people, we get beat up to ensure our SOX procedures work well, and we’re in the trenches. With that in mind, the poll this week is…
Does it make sense to ban personal storage devices?
I know it’s not practical to actually try and prevent the iPods and other devices from coming into the building. You for sure cannot take away all the cell phones from people. But does it make sense to prevent these devices from connecting to your network? Ban USB and Bluetooth; don’t purchase rewriteable drives except for admins who can be monitored. Some other ideas?
Think about the past experiences we”ve had. Suppose you”d locked down floppy drives and CD burners a decade ago. All of a sudden MP3 players appear and get mounted as removable drives. It”s a new twist you hadn”t considered, so you shut down all the serial, parallel, and USB ports. You get a new laptop and realize Bluetooth is now available and works just as well and your controls have been circumvented again. What do you do now? It’s a tough balance to strike. Are you trying to be a control freak and make it an hostile work environment or do you trust your employees and deal with the occasional problems that come with them?
I’m not sure what the best solution is, but I’d venture to guess that banning the technologies won”t work. Someone will always come up with a new way to get around your controls, and more importantly, you won”t be as vigilant if the controls “appear” to work.
I’d adopt the Counterpane approach to security, which is what security expert Bruce Schneier believes in. Assume you”ll get compromised and attacked and put systems in place to detect and respond to issues rather than trying to prevent all attacks.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are now available atsqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). We’ve upped the quality a little on the Quicktime files, so if it’s better from your side, let us know. Comments are definitely appreciated and wanted, and you can get feeds from there.
Is separation that much of a hassle? I don’t think so.
Many years ago I worked in a small company that only had about 5 or 6 servers. We had one system administrator whose job it was to manage all the servers. One day our sysadmin was on vacation when there was a problem with the Exchange server. One of the other developers worked on the system and ended up fixing it, but changed the service account password while doing so. The next day I walked into the office to find a group of people stymied as to what was wrong with the development server and version control system. Everyone claimed they hadn’t changed anything on that server, and they were right. However our admin used the same domain account for all servers, including my SQL Servers. I changed the SQL service account that day.
One of the recommendations that I learned a long time ago, and one that I make regularly, is that every SQL Server instance should have a separate security account. In that case, I had separate accounts created for each database instance, and for each SQL Agent instance. We used long, random passwords that were never stored, and if we needed to access a password, we just changed it. That kind of flexibility and separation prevented any crosstalk issues between services, and it allowed us to easily alter permissions or passwords for one service without affecting any others.
The other day I saw someone recommending a single service account for all SQL Servers. Someone else recommended a single account for each version of SQL Server, using separate accounts where it’s really needed. That’s a better recommendation, but I still prefer completely separate accounts. I know that some security groups don’t like that, but is it that big a problem? This Friday I wanted to ask you about your experiences.
Do you find separate accounts for each instance (or Agent) to be a security or administrative issue?
I’m not sure why this is unwieldy. Service accounts rarely change, and you could easily script changes to a group of accounts with PowerShell or some other tool. Once I set a service account, the only thing I might ever do later is alter the permissions to add access to a folder. When that happens, I definitely want to have separate accounts for each instance.
Let us know this Friday how you feel and what works for you.
Steve Jones
The Voice of the DBA Podcasts
We publish three versions of the podcast each day for you to enjoy.
Voice of the DBA 