I have a demo application that I have been using to test out new technologies including ASP.NET dynamic data websites as well as Windows Live Tools.
So now that I have an invite for Azure SQL I wanted to try these technologies out “in the cloud”.
For this web application, I was going to try two different configurations
- App Local, DB in the cloud.
- App in the cloud, DB in the cloud. (more on this later)
The first step was to migrate the database from my local DB server to Azure SQL. The documentation to do this is available now. The only issues I ran into were
- No locking of any kind is supported (Page, Row-level) so all of those options need to be removed from the database
- Multiple Active Recordsets are not supported.
There are other limitations of SQL Azure currently. This is a demo app and is not using every last SQL Server feature, YMMV.
One note about this, Don’t use Debug. I repeatedly got the error “Unable to cast object of type ‘System.DBNull’ to type ‘System.String'” trying to run query analyzer in debug mode. Just hit Execute and everything will run smoothly.
Next I changed the connection strings in my web.config to point to my new Azure SQL database. You can get your connection string by logging into your Azure management portal and choosing your database, there is an option to create connection strings for different data access providers. I ran the web application, and everything was working. I was able to create/edit/delete without any changes to my web application. Very nice.
To review, migrating a DynamicData website from SQL Server to Azure SQL involves
- Moving the database
- Changing your connection string