Chapter 4. SQL Server 2005 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.

Retrieving information about data storage requires digging into the inner storage structures of SQL Server. For SQL Server 2005, Microsoft has made some fairly dramatic changes to how metadata and system information is stored and accessed. Understanding these changes, or if you are brand new to SQL Server with this release, just the overall storage architecture is critical to understanding how SQL Server accesses and retrieves data.