Beginning, Microsoft® SQL Server® 2008 Administration

Chapter 4. SQL Server 2008 Storage Architecture

I had just spent the better part of the day describing the storage architecture to a group of about 30 new database administrators when one of them approached me while the class was on break and asked me pointedly, "Why do I need to know this stuff? I mean, who cares how SQL Server stores data as long as it does it?" They were valid questions. After all, I have no idea how the fuel injection system on my car works, but I drive it anyway. The key difference is that when my car needs service, I take it to a mechanic. If your database doesn't work, who are you going to take it to? Understanding the mechanics of the SQL Server storage will help you make informed decisions on where the data is stored, how the data is indexed, and how to troubleshoot an ailing database.

For years, SQL Server database administrators have grown accustomed to having unrestricted access to system objects. This ability gave the DBA incredible power for both good and for evil. For example, a database administrator could turn on ad hoc updates to the system tables and then modify any value, including password hashes. This ability was certainly useful for correcting some system errors; more damage was just as likely, however.

In the past, Microsoft strongly recommended that system objects not be accessed directly, while sometimes offering solutions to database problems that required directly updating system tables. With the release of SQL Server 2005 and continuing ...