Good news: .netTiers works with SQL Azure!

Last week Microsoft released a “Community Technology Preview” of SQL Azure, a hosted version of Microsoft SQL Server 2008.  In addition to the SQL Azure CTP, Microsoft also has a CTP for Windows Azure, where you can deploy web and worker roles – similar to how we deploy ASP.NET web sites and Windows Services today.

A few weeks back I had started a thread on .netTiers and Azure:

    .netTiers and Windows Azure?
    http://community.codesmithtools.com/forums/p/9327/37238.aspx

At the time only "Azure Storage" was available - Azure storage has support for Blobs, queues and "Tables".  Tables are not the tables we are used to in our relational databases and it would have been very difficult to have .netTiers work out of the box.

Well, now that the SQL Azure CTP is out, I tried today to create a project that used .netTiers and its BLL and DAL to work with data in SQL Azure.  It works!

Here's the process:

1. Work on an on-premise SQL Server - create your schema, indexes, foreign keys and custom stored procs, etc... just like you do today. 
2. At design time, use CodeSmith and generate your .netTiers DAL and BLL based on your on-premise SQL Server schema, like you do today.
3. At run-time, in development: use your on premise SQL Server or your SQL Azure SQL server - they both work by simply changing your connection string!
4. At run-time, in the Azure Cloud (Staging/Production): use SQL Azure

Your web and worker roles must run in full trust for the .netTiers DAL/BLL to work.  In your Azure service definition, make sure you set enableNativeCodeExecution to "true" - something like:

  <WebRole name="MYPROJECT_WebRole" enableNativeCodeExecution="true">

Also, currently when creating your database, indexes, stored procs, etc... in SQL Azure, you'll need to manually generate a script in SSMS and then clean it up before running it in SQL Azure - I blogged about it here:

  Cleaning up scripts for SQL Azure
  http://blog.ehuna.org/2009/08/cleaning_up_scripts_for_sql_az.html

Here's what I've tried so far:

• Using .netTiers inside the Azure "Development Fabric" and connecting to a local on-premise SQL Server.
• Using .netTiers inside the Azure "Development Fabric" and connecting to a SQL Azure database in the cloud.
• Using .netTiers in a web role in the Azure Cloud connecting to a SQL Azure database in the cloud.

All I had to do to make the above work is to change my connection string - no need to change code!
Good times!