SQL Server is a Database System that needs maintenance and proper care!
Face it, realize it. The faster you realize this, the faster you can start researching why and how to take good care of your Database System.
Now, don’t illude yourself. Every RDBMS system will need care.
Never, ever Install a Database System and leave it there, things don’t work that way. As I said you have to maintain the system.
This is Part 1 and it only concentrate on detecting corruption.
I’ll update this with links for the next parts!
So I’ll skip the Why you need to do this, maybe when I can fully understand all SQL subsystems (SQL engine is huge).
So lets get into the How you maintain a database system.
Its important to understand that SQL Database files stand on a Hard Drive, and this files are under a whole I/O subsystem. You need to be aware of this because well, if a failure occur on the I/O subsystem database corruption may occur, and the worst part of it, you may not even know it, data that is not frequently read is never out of the disk, so its never checked for corruption, so this kind of corruption may only be noticed when you select some data that is corrupt. Fortunately there's a kind of protection that is a CHECKSUM per page that can be enabled on a database that will do a calculation for the bytes on the page and will prevent the corruption when writing the page to disk, and detect when reading from disk. This helps detects corruption, otherwise you wouldn’t even know if your data is corrupt.
So if you don’t have it enabled please do.
ALTER DATABASE [database_name] SET PAGE_VERIFY CHECKSUM;
This is a Best practice. But this only helps detect some corruptions, there are other and you should always have a Consistent database (I mean all databases).
The only way to recover from corruption without Backups and Replication is loosing the corrupt data. So One reason you need to maintain your databases are because well they can become corrupt and you need to investigate, is my data ok? Is there any corruption on my database? Is all the data on my database OK?
If you want to know really really really (have I said enough really?) deep about corruption you may want to check Paul’s Randal Blog – In Recovery. This man really know a lot, he used to work for Microsoft and was one of the responsible for writing one of SQL routines for checking corruption.
So if you understood well, how do you check your database?
there are tons of commands but the most used is DBCC CHECKDB.
This is a pretty heavy command, as it checks the whole DB, the bigger the database the longer it takes, at least this is How I think it is.
So How do I check my Database?
DBCC CHECKDB ([database_name])
Don’t worry this command will not change anything, it will only check.
Pray that it won’t find anything, I have found corruption before and it ain’t nice.
you have to make this check regularly, but do it on a window. If you can do it daily, but if you can’t do it weekly, if you database is really huge, there are other ways, check Paul Randal’s blog.
Well there are 3 things you can do to protect from corruption.
1 – Turn Checksum on a page level.
2 – Check your database regularly.
3 – Take Backups, Lots of them, but not just any Backup, you have to take the right ones. I’ll do post more info around Backups later. But why is taking backups necessary to protect from corruption, well the thing is you can’t escape from corruption without data loss unless you have a backup (or loads of them). And please if you take a backup check if it’s ok, there’s a really nice command which I’ll post about it that verifies the backup.
Well to sum up. Checking your database will not prevent future corruptions, it will only evaluate your database for corruptions Now. There’s “nothing” that can prevent you from future corruptions, only proper database maintenance can help you on that.
Now Do yourself a favor and schedule a job that does check all your databases on the night and mail you if there is an error on the job.
Hope this helps.
Good Luck
The Developer Seer.
No comments:
Post a Comment