I recently had the opportunity to get access to an internal CTP of the new SQL Azure technology which, at the time of this writing, should now be available as a public CTP.  Here is a link to the SQL Azure team post for information on how to get started.

SQL Azure, formerly known as SQL Services, is a new relational database in the cloud.  It is a specialized version of SQL Server 2008.  In fact, if you execute the command “select @@version”, you will get back a string that says as much:

One of the really neat things about SQL Azure is that you can connect to it using a SQL connection string, much like you would with an on-premise SQL Server.  This works because SQL Azure, much like its non-cloud cousins, comes with TDS support.

TDS stands for Tabular Data Stream and is an application-level protocol used in communication between client applications and SQL Server.  TDS packets are encapsulated in the packets built for the protocol stack.  So if you are using the TCP/IP Sockets Net-Library, then TDS packets are encapsulated in the TCP/IP packets.  Because of this, you can use a SQL connection string to connect to SQL Azure from even behind a firewall, provided your firewall settings are not blocking outbound ports.

Support for TDS means that you will eventually be able to use the same tools you use today when developing against SQL Server.  I say eventually because as of CTP1 of SQL Azure, there are still a few kinks to work out in order to enable full support for tools like SQL Server Management Studio (SSMS).  You can still use SSMS today against SQL Azure if you are aware of these kinks and how to work around some of them.

For the purposes of this post, I’m going to assume you’ve already signed up for the SQL Azure CTP.

Once you’ve gotten access to SQL Azure, you can navigate to the portal site and from there you can create a database from the Server administration page.

The master database will be the only database listed initially.  I should also mention that you can create a database using SQL as well.  As I’ve implied, support for tools is still in the works since this is only a CTP, however you can use sqlcmd and SSMS today to do much of what you need to do with SQL Azure.

The following is an example of the syntax you would use when connecting to SQL Azure using sqlcmd:

sqlcmd -S oup6wnx5kr.ctp.database.windows.net -U mpapas@oup6wnx5kr -P ***********

Connecting using SSMS is a bit trickier.  The following are the steps you need to take:

1. Launch SSMS
2. Cancel the initial “Connect to Server” dialog (support for the Object Explorer in SSMS is not there yet)
3. Click on New Query
   
   
   
4. You’ll need to specify SQL Server Authentication and specify the Server Name in the following format tcp:<your server name>.ctp.database.windows.net.  Be sure you have support for TCP/IP enabled as a client protocol.
   
   
   
5. Don’t click Connect just yet.  Instead click on Options >>.
6. If you’ve already created the database using the portal site, type in the name of the database in the Connect to Database combo box.  You won’t be able to see your database in the drop down because support for that just isn’t there yet either.
   
   
   
7. Finally, click Connect.
8. You will see the following dialog pop-up.  This is safe to ignore (more CTP goodness I guess).

And that’s all there is to it.  Now that you have a query window open, you can enter and execute your SQL.

You can even show the execution plan of your queries:

It’s important to mention that not all T-SQL is supported.  For a list of what is and is not supported, see the SQL Azure documentation on MSDN.

In an upcoming blog post, I plan on blogging about my experience with converting NerdDinner to use SQL Azure for its main database instead of Windows Azure Storage.

This posting represents the sole opinion of the author and is not the opinion of Microsoft Corporation.  This posting is provided "AS IS" with no warranties, and confers no rights.  Use of any included samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.

What an amazing week it was last week at TechReady 9 in Seattle!

TechReady is a conference where the Microsoft product teams and the field get to exchange information.  It’s a sort of meeting of the minds.  The product teams, in turn, get to ask questions of the field in order to better understand how products are being leveraged by customers.

Learning about the enormous amount of innovation coming from the product teams is akin to drinking from a fire hose.  I’m always amazed at the wealth of innovation that flows from these teams.  It’s truly amazing and inspiring to see.  I’m constantly impressed by Microsoft’s ability to envision long-term and align the business and the engineering teams in order to realize, unrelentingly, a set of shared goals. 

Many people don’t realize the full depth and breadth of innovation that occurs within this company.  It isn’t just Windows and Office.  It isn’t just about the “search war” with Bing.

These incremental product improvements are about taking steps towards the realization of a future that could be.  Our strength lies in the interwoven tapestry of innovation that spreads between and through our products.

At the very core, the engine of innovation is Microsoft Research.  I’m truly thankful to be working for a company which is willing to make such long-term strategic investments in not just its future, but the future of the world.  Perhaps I am a little naive, but I really do believe that.  And belief is a powerful motivator for change.

I wish I could share the little glimpse of the future that I’ve seen while at TechReady, but unfortunately I can’t.  I guess you’ll have to trust me when I tell you that the ride is far from over.  We’re just changing roller coasters, so hang onto your hats.  The best is yet to come.

As a first foray into the Microsoft Azure Services Platform, I decided to try my hand at converting an application to run in the Microsoft Cloud.  I chose NerdDinner as the application to convert because it was a fairly small and easy to understand application.  NerdDinner was first released as a sample app for the book Professional ASP.NET MVC 1.0. 

Configuration

Since the ASP.NET MVC bits are not installed on Windows Azure instances, I needed to include a local copy of the DLLs with the project. 

For storage, I planned on using the Windows Azure Table Service for storage.  You can access the Table Service API in the following ways:

• Using the REST API directly, or…
• Using the .NET Client Library for ADO.NET Data Services

The .NET Client Library is just a layer on top of the REST API and makes things a little easier.  I decided to use the .NET Client Library so therefore I needed to include a reference to the System.Data.Services.Client namespace. 

I’m also going to include two sample projects into the solution.  These sample projects came with the Windows Azure SDK and they are the StorageClient and AspProviders libraries.

The StorageClient is a library that sits between the .NET Client Library for ADO.NET Data Services and the NerdDinner application.  Think of it as a Windows Azure Table Service-specific helper library.  You can read more about the sample here.

The AspProviders project is provides sample implementations of ASP.NET providers that can run on Azure.  Since NerdDinner uses an ASP.NET membership provider, I needed something equivalent.  You can read more about that sample here.

Once these two projects were added to the solution, I added a project reference to each from the NerdDinner project.

I also need to tell Windows Azure a little about the application I wish to host.  This is done through a new project type that gets added to Visual Studio when you install the Azure SDK.  What I needed was to add a Blank Cloud Service project type to the solution:

A Cloud Service project type basically defines what your hosted service looks like to Windows Azure.  Once you have this project type in your solution, you can add Web or Worker roles to it, which are basically references to other projects.  See Understanding Service Architecture for more information on Web and Worker roles in Azure.

In order to add NerdDinner as a Web role, I needed to modify the .csproj file to include a <RoleType> element.  For more information on this, see this blog post.

My final solution structure looked like the following:

The next step was to modify the Web.config file in NerdDinner.  I needed to tell the app that I wanted to use the TableStorageMembershipProvider instead of the AspNetSqlMembershipProvider.  I also needed to include some basic application settings for local development. 

Initially I put these <appSettings> values in Web.config, but down the road I learned it was better to actually put these values in the local ServiceConfiguration.cscfg file.  The reason for this is that if they are in the .cscfg file, you can update the configuration in the Cloud without having to redeploy your application. 

Therefore, I created another .cscfg file which I added as a Solution Item that I use to configure the application when I deploy it to the Cloud.  The .cscfg file within the NerdDinner_Azure Cloud Service project contains settings for local development.  This is convenient because when I run locally, I can just leave those settings alone.  Here is my local ServiceConfiguration.cscfg file:

<?xml version="1.0"?>

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

  <Role name="Web">

    <Instances count="1" />

    <ConfigurationSettings>

      <Setting name="AccountName" value="devstoreaccount1"/>

      <Setting name="AccountSharedKey" value="Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw=="/>

      <Setting name="TableStorageEndpoint" value="http://127.0.0.1:10002"/>

      <Setting name="BlobStorageEndpoint" value="http://127.0.0.1:10000/"/>

      <Setting name="QueueStorageEndpoint" value="http://127.0.0.1:10001"/>

    </ConfigurationSettings>

  </Role>

</ServiceConfiguration>

Data Model

Because the relational version of SQL Data Services technology wasn’t available to me yet, I did not want to spend time learning SDS when Azure Table Storage would work just fine for my purpose.  Had the new version of SQL Data Services been available, it would have made this conversion considerably easier as there would have been fewer changes to the model.  Oh well…

Since we can’t use LINQ-to-SQL, I blew away any files associated with that approach and instead created the following classes:

Note that the entity classes, Dinner and RSVP, derive from TableStorageEntity and that NerdDinnerDataContext derives from TableStorageDataServiceContext.  These base classes are part of the StorageClient library.  From the class TableStorageEntity, the entity classes inherit the PartitionKey, RowKey and Timestamp properties.

The PartitionKey and RowKey properties are required when using the Windows Azure Table Service.  These are strings and together form the unique key for an entity. 

Choosing PartitionKey and RowKey values can have significant implications in scalability, which I won’t go into here. 

For my purposes, I decided on the following PartitionKey and RowKey combinations for the entites:

Since I now had to refer to entities by using PartitionKey and RowKey, I had to modify the Controller classes and Views in NerdDinner to refer to an entity using this combined key.  I also had to modify the ViewDataModel classes as well as the JsonDinner class representing the JSON data structure used during the AJAX requests.

Also, because of the limitations inherent with the current version of the Windows Azure Table Service, I had to get a little creative in the data access functionality.  For instance, the OrderBy LINQ operator is not supported by the Table Service.  Therefore, I had to make sure that the ordering of the sequence in my LINQ queries was done in memory and not remotely.  Here’s a little trick that worked nicely for me in one of the controllers:

IEnumerable<Dinner> dinners = dinnerRepository.FindUpcomingDinners().AsEnumerable().OrderBy(dinner => dinner.EventDate);

I used AsEnumerable() to hide the custom operators and instead make the standard query operators available to me.  This forced the query to execute and allowed me to use an in-memory sequence for the OrderBy method call as you see in the method syntax I’m using above.  I could have also called ToList<T> or ToArray<T> to force immediate execution and cache the results in memory before calling OrderyBy.

Since the Count operator is also not supported, I had to work around that as well.  Who would have thought it would be that difficult to ask for the RSVP count?  Well, once you know the tricks, it’s not that bad, but it sure would have made it A LOT easier if these aggregate functions were supported out-of-the-box.

Unfortunately it was getting very late by the time I got to the paging functionality, so paging through a dinner list is not supported at this point.  I’ll let the reader figure that one out J

Extension methods are a feature of C# 3.0 that is just one of those features which can be easily overlooked. 

A lot of .NET old-timers, like myself, who are particularly kept busy on a daily basis trying to meet a project deadline or build the next biggest, baddest Line of Business application (wow.. that just sounds soo sexy doesn’t it?) will often fall back on old practices.  .NET has trained me to often create a number of helper classes, such as a StringValidationHelper etc., in order to do things like validating user-entered data.  So for instance, I could have a class that looks like the following:

And here’s how you use it:

Nothing too exciting here so far… 

But watch out! Here come Extension Methods!

With Extension Methods, I can change add a method onto the string class itself.  And it doesn’t matter if the class is sealed! 

Here’s my adjusted StringHelper class:

Notice that all I had to do was add the keyword this before the string data argument.

Now I can use the IsValidPhoneNumber method directly from my string variable:

Ah.. that’s more like it.  This code now looks a lot less cluttered.

Of course this is a contrived example but nevertheless illustrates how easy it is to use this very cool feature of C#.

Some of you who have been using LINQ may not realize that LINQ itself heavily uses Extension Methods, particularly on the IEnumerable interface.  This occurs when you import the System.Linq namespace.  Try it out – look at the IEnumerable interface intellisense before and after you import the System.Linq namespace.