To get into Microsoft Azure application development, I was playing around with SQL Server Data Tools (SSDT), trying to create a database on SQL Azure.

I started creating a simple database, having two tables with a few columns. As done before with other tools, I was using the extended properties to document the database ‘inline’. The target platform was my local SQL 2012 instance. Deployment went fine, updating after some changes as well.

So the SQL Azure database server was created quickly, and I tried to deploy that database from my local machine to SQL Azure. It failed – of course (I wouldn’t have written a post just to tell you I succeeded 😉 ).

The reason surprised me, but a solution was found very quickly.

The reason why publishing failed is, that, beside others, SQL Azure does not support extended properties.

The solution is posted by Bill Gibson on the SQL Server Data Tools Team Blog in his post Migrating a Database to SQL Azure using SSDT.

I created a Schema Compare file as suggested by Bill, and put a second SQL Server project into my solution, following his instructions – and it worked fine 🙂

Now I have two database projects in my solution. The first one with full featured extended properties to document the database by itself. This one is used to build and update the local SQL 2012 version of the database. And the second one, based on Schema Compare, to be able to convert the SQL 2012 version into a SQL Azure compatible one and build and update the instance on SQL Azure. Of course, one have to take care not to update the scripts of the SQL Azure version, but keep in mind that SQL 2012 is the master.

This works smooth and easy in my simple test environment, because I do not use such things as user defined types (CLR) or XML indexes. Both are also not supported by SQL Azure. And migrating an existing database which uses these feature is not just letting Schema Compare doing the work. In such a case, one has to do some re-work, or probably re-think the decision to move to SQL Azure.

Btw, I think it’s worth to mention that when excluding the Default objects from schema comparison, this does not mean that column default values created by the CREATE or ALTER TABLE statement will be ignored. It seems that only those defaults created by CREATE DEFAULT are ignored. To me this is OK, since the SQL Server books say that CREATE DEFAULT will be removed in future versions of SQL Server anyway.