« Windows PowerShell commands to clean up scripts for SQL Azure | Main | Windows Azure and SQL Azure Videos »

Tested .netTiers with SQL Azure – it just works

A few days ago 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.  For more information check out http://www.microsoft.com/azure/.

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

  .netTiers and Windows Azure?

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 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

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!  Below I copy my Windows Azure configuration file and give you an example on how to change your .netTiers connections string dynamically when it’s deployed to the Azure cloud.

Note: I originally posted these good news at the .netTiers forums – I thought it would be a good idea to cross post it here as well.

.netTiers Dynamic Connection Strings

When you deploy a Web Role (an ASP.NET web site or web service) to Windows Azure, you cannot change the web.config file.

Instead, Windows Azure allows you to change its internal configuration files.  Such changes cause your Azure web roles to restart, just like changes to the web.config file cause your standard ASP.NET web sites and web services to reload. 

In the Azure configuration files (ServiceDefinition.csdef and ServiceConfiguration.cscfg), I have defined a setting called "NetTiersConnectionString":

   1: <?xml version="1.0" encoding="utf-8"?>

   2: <ServiceDefinition name="MYPROJECTAzure" xmlns="http://schemas.microsoft.com/ServiceHosting/2008/10/ServiceDefinition">

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

   4:     <InputEndpoints>

   5:       <!-- Must use port 80 for http and port 443 for https when running in the cloud -->

   6:       <InputEndpoint name="HttpIn" protocol="http" port="8080" />

   7:     </InputEndpoints>

   8:     <ConfigurationSettings>

   9:       <Setting name="NetTiersConnectionString" />

  10:     </ConfigurationSettings>

  11:   </WebRole>

  12: </ServiceDefinition>


  14: <?xml version="1.0"?>

  15: <ServiceConfiguration serviceName="MYPROJECTAzure" xmlns="http://schemas.microsoft.com/ServiceHosting/2008/10/ServiceConfiguration">

  16:   <Role name="MYPROJECT_WebRole">

  17:     <Instances count="2" />

  18:     <ConfigurationSettings>

  19:       <Setting name="NetTiersConnectionString" value="Server=tcp:MYSQLAZURESERVER.ctp.database.windows.net;Database=master;User ID=MYLOGIN;Password=MYPASSWORD;Trusted_Connection=False;" />

  23:     </ConfigurationSettings>

  24:   </Role>

  25: </ServiceConfiguration>

Then, in each of my pages I use this connection string like this - I first add the connection when the page is loaded:

   1: Private Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

   2:   DataRepository.AddConnection("MYCONNECTION", sNetTiersConnectionString)

   3: End Sub

In other places in the page, I use the dynamic connection - for example like this:

   1: DataRepository.Connections("MYCONNECTION").Provider.MYENTITYProvider.Insert(oMYENTITY)

Finally in the page's unload event handler, I remove the connection:

   1: Private Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload

   2:     DataRepository.Connections.Remove("MYCONNECTION")

   3:   End Sub

I opened a thread on the .netTiers forums and members of the .netTiers team (Blake and SuperJeffe) verified that there are no performance or stability issues in using .netTiers dynamic connections.

| More



This page contains a single entry from the blog posted on September 5, 2009 9:26 AM.

The previous post in this blog was Windows PowerShell commands to clean up scripts for SQL Azure.

The next post in this blog is Windows Azure and SQL Azure Videos.

Many more can be found on the main index page or by looking through the archives.

Powered by
Movable Type 3.35