Reposted 10/1/06 to include in 'free tools' category
I'm constantly asked "what's Microsoft doing about metadata and do we have a solution."
In the old days (SQl 2000 and 7), Microsoft provided Meta Data Services, a platform designed to help tools and applications manage metadata and information models more effectively. It included the Open Information Model (OIM). A core model of sharable and reusable type descriptions. Microsoft worked with the Meta Data Coalition to publish the Open Information Model as an industry metadata model standard. Initially it was warmly received. However, soon after, a second set of companies in the Object Management Group proposed the Common Warehouse Metamodel (CWM) as a second standard for metadata. Metadata was everywhere!
But the excitement over cross-industry metadata models eventually wore off. For a variety of reasons, ISVs started to move away from industry-wide models and focused on point-to-point mappings between products (primarily focusing on XML-based exchange). The Meta Data Coalition merged with the OMG and cross-industry metadata modeling efforts have been stalled for several years. The world discovered Enterprise repositories and cross-product metadata models are very hard to integrate into existing computing environments (lots of custom applications and tools) and require lots of care and feeding for effective deployment and maintenance (i.e. lots of consulting).
So, Meta Data Services was deprecated in SQL 2005.
With SQL Server 2005 XML is every where, its used to describe all the really useful objects in the product stack. Consequently the possibilities to satisfy many of the needs, that metadata repositories are touted to solve, can now be easily solved by reporting off the XML that describes so many of the key components used in (Microsoft SQL Server) data warehousing (ie used by SSIS, SSRS and SSAS). The important aspects are data lineage, data dictionaries, auditing and impact analysis.
We are very pleased to announce the first incarnation of the "SQL Server 2005 Business Intelligence Metadata Samples Toolkit" http://www.microsoft.com/downloads/details.aspx?FamilyId=11DAA4D1-196D-4F2A-B18F-891579C364F4&displaylang=en. Along with the associated whitepaper http://www.microsoft.com/downloads/details.aspx?FamilyId=182BD330-0189-450C-A2FE-DF5C132D9DA9&displaylang=en it should now be possible to satisfy many of your metadata needs - simply and essentially for free!
The whitepaper also describes the importance of the new Extended Properties that can be applied to objects within the database - these can be used to store all sorts of business information including the all important data lineage and data definition.