I love relational databases.  Many years ago, I made a conscious decision that I was going to devote my career to architecting, engineering and running databases.  I've spent years poring over subjects such as relational theory, SQL-92 and SQL-99 standards, transaction processing, analytics, set theory, shared everything and shared nothing architectures, security, scalability, reliability and performance.  I've tweaked, I've tuned and I've retweaked and I've retuned.  It started off almost 20 years ago and I've run a gamut of systems and platforms.  DB2, Oracle, Informix, Sybase and ultimately, in 1993 settled on a product called Microsoft SQL Server.  Now we're talking OS2 versions of SQL Server and for anyone who went down that path, you know what I'm talking about when I say the product was in its infancy.  Since then, I've watched it change and grow into the great product we have today. 

So as I mentioned, I love relational databases and I don't just mean SQL Server (it's just the one I like best).   But as tools to create and manage these databases get simpler, I've seen some disturbing trends.  The first is that it's way too easy to create a database without having the first inkling of any of the concepts I mention above.  This is particularly scary when you're the DBA who has to support production databases that don't scale because their design is fundamentally flawed in some way.  An equally (some would call it more) disturbing trend is the current idea that all things belong in a database.  While that may be true on some levels, where the real issue lies is in storing and retrieving non-relational data from a relational database management system (RDBMS).   There are many types of data that do not lend themselves naturally to an RDBMS.  For example, while storing flat file structures and hierarchies in SQL Server ostensibly seems like a good idea...after all it's data and SQL Server is a database, it's important for people to note the "R" at the front of the acronym RDBMS.  Now, without going into the depths of Relational Theory, Boyce-Codd normal form and degrees of denormalization I will say that while hierarchical data is somewhat relational, it does not lend itself well to storage and retrieval operations in an RDBMS.  The reasons are really fairly simple.  RDBMS engines are designed to optimize query plans, storage operations and caching with relational data.  RDBMS engines will still try to interpret the data and apply relational operations to it.  Hierarchies have the added complexity of usually requiring the need for inherited properties, which RDBMS engines do not do well without a LOT of code. 

By now, you're asking yourselves, does he have a point?!  Don't worry, I'm getting there now.  Recently, we had a situation where we had a legacy SQL database that, when we got right down to it, represented an almost pure hierarchical data structure.  And guess what?  We were having issues with performance, requests to have objects inherit properties and attributes from their "parents" and nightmares trying to build a model that would allow for attribute level security.  So we quickly made the decision that SQL Server was not the best solution to store and retrieve the data.  So what did we choose, you ask?  Well, when we looked at what was available to us, the most relevant solution was Active Directory.  It supports hierarchies, inheritance, replication and attribute level security...but it was serious overkill.  As I mentioned in an earlier post, we eventually want to share our tools with all of you and to ask you to install an entire AD structure to manage a portion of your environment seemed a little much.  Then one of my guys, we'll call him Chris (because that's his name) said "Hey, why don't we use AD/AM?"  To which the rest of us said "Huh?  Who the heck is Adam and does he know something we don't?"  To which he rolled his eyes and said "Not Adam, AD/AM!"  and began to educate us on yet another little known Microsoft product.  AD/AM is Active Directory/Application Mode.  It ships with Windows 2003 Server and is downloadable for free for all other operating systems (including XP).  AD/AM is a lightweight Active Directory that supports all of the features of AD that we were looking for without the overhead of a full AD implementation.   Once our data store was converted into AD/AM, we wrote an API that allows us to relate the objects in our hierarchical database to truly relational data stored in SQL Server and I'm here to tell you it rocks!  It's fast, reliable, scalable and the right tool for the right job.  We continue to leverage SQL Server for our relational database needs and AD/AM to manage our hierarchies.  Pretty cool stuff!  I encourage you to take a good look at AD/AM if you're looking to store and retrieve hierarchical data.  I've added the links to their homepage, the download page and an overview presentation below..

AD/AM Home Page

AD/AM Download Page

AD/AM Overview Presentation

That's all for now!

Scott