Today I discovered an absolute gem, a solution I’ve been hunting for a while actually, that being: the ability to migrate my SQL Server 2012 Express database schema changes to a Windows Azure database from within Visual Studio 2012 automatically (This may work in Visual Studio 2010 as well, I am not sure, but no doubt both Standard/Enterprise and Express editions of SQL Server) and I thought those working with Windows Azure and constantly having to migrate database schema changes across might find it helpful too.
As it stands, I believe there is no particular tool or facilitation or guide built by Microsoft in order to migrate schema changes from your local SQL Server database to Windows Azure without doing it manually. I mean Microsoft wrote this guide, but again, it talks really about one-shot database migrations and not subsequent, iterative schema updates.
It should be said, I am aware of the awesome “SQL Azure Migration Wizard” tool built by the community to bridge this shortcoming, I actually used it to initially import my SQL Server 2012 Express database across to Azure; worked a treat. But again, I do not see this as a practical or efficient way of doing things as you have to import a change script each time.
So let’s get on with it – Disclaimer: If you have found an easier way to do this, feel free to leave a comment on the bottom of this post to help myself and others
COMPARE & MIGRATE SQL SERVER SCHEMA UPDATES TO WINDOWS AZURE DATABASE:
- With your Visual Studio 2012 project open, click the menu SQL -> Schema Compare -> New Schema Comparison…
- In the left hand drop down stating “Select Source” press and enter the database connection settings of the database containing the updated schema you wish to migrate into your Windows Azure SQL Database.
- In the right hand drop down stating “Select Target” press and enter your Windows Azure SQL Database connection settings of which you wish to migrate to.
- Press “Compare” on the menu strip.
- Wait until compared, review the changes by clicking on each row, you may include and exclude certain DB changes by clicking the checkbox. (Note: some changes, typically the stuff of little importance, such as extended property information will not migrate across to Windows Azure and that’s okay.)
- Now the all important part, if you pressed “Update” now to migrate changes across to Windows Azure, it would fail with an error something to the effect of: “A project which specifies SQL Server 2012 as the target platform cannot be published to SQL Azure.” So you must enter the “Options” dialog which is represented by the cog icon, then tick “Allow incompatible platform” (it appears you may have to do this each schema comparison, unless you save the comparison file).
- Now press “Update” and watch all your glorious changes, and ONLY your changes be migrated to your Azure database! (You may encounter some warnings in your Error List stating “A project which specifies SQL Server 2008 as the target platform may experience compatibility issues with SQL Azure.“, but that’s okay, I had about 5 schema updates on various tables and all mine went through fine, including foreign key re-assignments and additions, and my columns were placed in the right positions!)
- No fuss, no mess. A high-automation solution which is what we want.