I occasionally scratch my head trying to think of a good technical reason why SQL Azure (sorry, Windows Azure SQL Database) does not support database extended properties. As far as I'm aware, Microsoft currently offers no indication of when, or if, support will appear so, for now, if you're porting databases to SQL Azure, you'll need to remove all existing extended properties.

Peter Larsson provides a short script that will make this process painless, but that's not really the main point. Flawed as they are in some respects, extended properties are simply the best way to document a database, and sometimes the only viable mechanism.

They values of extended properties are stored with the database object metadata (in a base system table for the database) and offer a means to attach the documentation directly to the database itself and its objects, rather than a separate document. The best-known example, and sadly the only one that we can add directly through SSMS, is the MS_Description extended property, in which we store a basic description of each object. However, we can use the sp_addextendedproperty stored procedure to store any property we wish in the metadata, such as revision date, author or, critically, database version number (i.e. the build number).

The beauty of extended properties is that the documentation stays with the database. When we build a database (e.g. from SMO), it extracts the extended properties from the metadata and includes them with the build script. They are also included in database backups.

Extended properties are the only satisfactory way of documenting tables and their components, since table scripts are not preserved within the server. Even more importantly, many deployment tools rely on extended properties as a means to extract from, or attach, a version number to a database in order to ensure it applies the correct set of upgrade scripts. Without them, we have to adopt a different deployment method just for the Microsoft cloud. This might involve separate documentation (in XML of JSON), or storing the build numbers in a dedicated table (not always an option, depending on how strictly controlled is the schema design).

It seems odd to me that Microsoft would introduce such hurdles to deploying databases to their cloud, especially when it's now possible, and relatively cheap, to work with fully-fledged SQL Server databases, in the Amazon cloud, without this sort of inconvenience.

Is there a sound technical reason why SQL Azure can't support extended properties? Or some other reason why Microsoft is reluctant to add support?

Cheers,

Tony.