Search This Blog

January 11, 2010

Metadata Basics – The Great DBA Koan

So… you just got your new shiny SQL Server DBA Deputy’s badge, and moved your crap over to a new desk, next to the resident SQL Expert in your office, all keen for the Great Imparting Of Wisdom (GIOW), and he’s handed you a diagram full of coloured regions, annotated boxes and connecting lines.  “First things first, get yourself up to speed with the system catalog for SQL Server,” he says, then turns back to his own screen to fix whatever just got broken for the third time today.

About now, many young DBA apprentices will think “What is this system catalog?  And how will it help ready myself for the GIOW?  Why is the master giving me inordinately complex visual koans rather than explaining how this stuff all works?”

The answer, young grasshopper, is both obvious and obscure.  In telling you to learn the system catalog, the Master has given you the keys to the universe – there is no GIOW more profound.  This is because every Relational Database System that conforms to Ted Codd’s 12 rules for classification as such actually describes itself within its own relational tables.  These days, some compromises have been made to Codd’s Curse (as some describe the 12 rules), but these are for the sake of performance, flexibility and accuracy.

Confused?  I don’t blame you.  I was for the first hour or two.  Then the system started to make sense.  The structure is IN the data itself!  Wow… a self-describing system.  Satori!  Fireworks!  Hooray!

SQL Server’s metadata system is broken up into 3 key parts: the Master Database Schema, the User Database Schema, and the MSDB schema.  [I could also include a discussion of the resource database here, but for now I’ll keep it simple and we’ll get back to that later.]

  • The Master Database Schema contains tables/views and other assets (functions, procedures, etc) that describe the database management system itself.  Within the master DB, you can find what files are used to make up any database on the server, what options have been set on each database, who can access what databases, and so on.
  • The User Database Schema contains tables/views and other assets that describe the data structures, behaviours, security and constraints within a given database.  Even master has a copy of the user database schema, as the objects that describe other databases are all persisted and defined in terms of queryable tabular data.
  • The MSDB database is a special database used to track operational activities.  Like master and every other database, it’s structure is defined by a copy of the User Database Schema.  Within MSDB you will find definitions for tables that describe backup history, restore history, SQL Agent tasks, task steps and their history, and definitions/historical data for many other SQL Server administration features.

Study these schemas well, and the greater order behind SQL Server becomes apparent.  Understanding how SQL Server describes databases unmasks insights into how SQL Server performs operations on those databases.  Once given your first link to SQL Server books online and SQL Server catalog chart, you have taken your first steps to becoming a master DBA.

For the developers and DB designers out there – don’t feel left out.  You should also be aware of these structures and their importance, but for you, these are simply the bricks with which you will pave your path to enlightenment.  For a master DBA, the bricks ARE the journey.  Their knowledge of such things need to be greater than yours.

2 comments:

  1. Jeremy,

    And *that* is what I meant about "talking down" to the less enlightened among us! I realize "talking down" has negative connotations, but in my previous comment and this, I mean its use to be exactly as you have written the above post. Good information, written for the everyman or everywoman.

    I have two years of self teaching with SQL Server, and am only now coming to the point where my brain can accept the "Great Imparting Of Wisdom" of which you speak.

    Thank you, sir!

    ReplyDelete
  2. Thanks David :) Glad you got some value out of it.

    ReplyDelete

There was an error in this gadget