Benefits of copying Dynamics CRM Online metadata to SQL

Dynamics CRM is, to a large extent, a metadata-driven platform, as illustrated here. What this means is that a lot of its functionality is derived by business entities, their relationships, fields, picklists, etc.

Given the importance of metadata in CRM, you'd expect that you'd be able to write a query against it, such as "List all account fields that are not on a form." or "List all web resources and the forms, if any, each one is used on.". Unfortunately, these types of queries aren't possible without writing a fair bit of SDK or Web API code.

Being a long time user of SQL Server and T-SQL, my instinct was to find a way to regularly copy CRM Online's metadata to SQL tables. To follow is an overview of what I have working so far.

My initial set of requirements are these:

• Make it quick and easy to run robust queries (joins, filters, sorting) against CRM Online metadata.
• Allow for enhancing CRM's metadata, such as listing all picklist items next to each picklist (OptionSet) field and showing whether a field is on any forms and which ones.
• Allow for diffs on the metadata, to be able to determine, for example, what new entities and fields were created in the past week.
• Provide a fast on-demand way to export CRM Online metadata to an Excel file.
• Allow for the service to run for multiple CRM Online organizations.

The approach I've taken (so far) is this:

• An Azure WebJob is responsible for querying CRM Online metadata (entities, fields, relationships, roles, global optionsets, views, forms and form structure, etc.) and copying this data to a set of SQL Server tables. Entity Framework, along with some CRM SDK code, makes this relatively easy to do. (The benefit of using a WebJob is that it can be scheduled or triggered on-demand.)
• A SQL Azure database stores the latest metadata and maintains copies of the previously extracted metadata. Stored procedures write differences to a table for easy reporting.
• Another WebJob (I might move it to REST service) allows for extraction of the metadata into various forms, such as Excel and Word templates. This allows for fast documentation of a CRM Online's org metadata.

This initial design has met all of my requirements except for handling multiple CRM orgs. That will take a bit more work and will probably deplete my Azure credits each month so I'm leaving it as a "nice to have".

I'll hopefully be able to put this out as open source but until then I hope you can glean some ideas from this information. And... hopefully Microsoft will provide the same types of functionality soon so that I don't need to maintain this solution. Although I have to admit, it was fun to build.