Windows Azure and SQL Azure are the new Cloud service products from Microsoft. In this blog post, I am going to show you how you can take a database that is hosted in SQL Azure and expose it as OData in a rich way using WCF Data Services and Windows Azure.

This walk-through requires that you have Visual Studio 2010 and both a Windows Azure and SQL Azure account.

Step 1: Configure the Database in SQL Azure

SQL Azure provides a great way to host your database in the cloud. I won’t spend a lot of time explaining how to use SQL Azure as there are a number of other blogs that have covered this in great detail. I have used the SQL Azure developer portal along with SQL Server Management Studio to create a Northwind database on my SQL Azure account that I will later expose VIA OData. The key to this step is that you want to have created your database in SQL Azure and you need to click the “Allow Microsoft Services access to this server” in the firewall settings on the SQL Azure developer portal as shown in the screen capture below – this allows your service in Windows Azure to access your database. 

When creating your SQL Azure server, carefully consider the server location you choose. In my case I have selected the North Central US location for my SQL Azure server and the key is that when you later choose a Windows Azure server location to deploy your Data Service to, you will probably want to choose the same location to reduce the latency between the Data Service and the database.

Step 2: Create the Data Service

First, you will need to go here and download the Windows Azure Tools for Visual Studio to get support for creating Windows Azure projects in Visual Studio 2010. Once you have done that, create a new solution in Visual Studio and choose the new Windows Azure Cloud Service project type.

Next, you will be asked to choose the Roles for your Cloud Service. Since our cloud service will be used primarily to host the Data Service the only role we need to add is the ASP.NET Web Role.

Once you click ok, Visual Studio creates a Windows Azure solution with Azure server configuration files and a single ASP.NET Web Role. Included in you project will also be an ASP.NET project that you can program against like you would any other ASP.NET project that is not being hosted in Windows Azure. The next thing you will do is create a WCF Data Service that exposes the Northwind Database you created in SQL Azure in step 1. To do this, create an Entity Framework model over that SQL Azure database using the Add New Item –> ADO.NET Entity Data Model gesture in Visual Studio.

In the next screen, choose to generate your model from a database and then specify the database to connect to. Make sure when you setup your SQL Azure database that you have chosen to allow the IP Address of your development system access to the SQL Azure database. Define a standard SqlClient connection to your SQL Azure database and specify your username and password to the database. Once you have defined your connection, select OK and then follow the steps through the rest of the Wizard to finish creating the model.

Next, use the Add New Item option to add a WCF Data Service to your ASP.NET project. Set the data source for your data service as the EF model you just created and set the entity set access rights for each entity set in your model.

Right-click the .svc file and select “View In Browser” and you should see the service document in your default browser. At this point, you should spend some more time configuring your data service – you can user query interceptors to add finer grainer authorization, setup authentication, set paging limits, etc. Because this goal of this post is to walk you through deploying the service, I will skip all of that and begin deploying the service immediately.

Step 3: Deploy the Data Service

The begin deploying the service to Windows Azure, right-click on the Web Role project and select “Publish…”. You will be presented with the following screen which walks you through the process of setting up deployment to your Azure server.

To start, setup your credentials (skip this step if you have deployed from Visual Studio 2010 before and have already setup your credentials). From the credentials drop-down, select “Add”. You will be presented with another dialog for creating your credentials file.

From the first drop down, select “<Create>” and then give the credentials a name – I called mine ODataAzure". When you do this, the Visual Studio 2010 tools will create a credentials file and place it in a temporary folder on disk. Click the “Copy the full path” link in the dialog to copy the path of this file to the clipboard. You will need this location for the next step, which is to upload this credentials file to your Azure server.

The next step is to go to the Windows Azure developer portal here and sign in to your Azure Account. Once you are logged in, go to the “Account” tab and click “Manage My API Certificates”. In this screen, there is an option to Upload a certificate – click this option and paste in the location of the certificate you just created and upload the certificate.

Once you have uploaded the certificate, go back to the Summary page and if you have not already created a Storage Account and Hosted Service, do so now. Click the “New Service” button and follow the prompts to create a storage model and a hosted service, remember to use the same service location for these as you chose for your SQL Azure database in step 1. When completed your summary should be similar to the one shown below.

Now go back to the Account page and copy the Subscription ID from the bottom of the page under the “Support Information” label. Go back to Visual Studio 2010 and paste that subscription ID into the certificate dialog. Name your credentials and click OK. At this point you have completed configuring Visual Studio 2010 to publish to your Azure Account. You will not need to perform these steps again in the future because Visual Studio remembers your credentials information and knows how to publish to your Azure account.

Now you will be back in the publish cloud service dialog, if you select the credentials you just created from the Credentials drop down, the Hosted Service and Storage Account drop downs will be populated with the services you just created. Adjust the label for the deployment if you need to and select OK – Visual Studio will start the deployment to Windows Azure. This will only take a few minutes.

Step 4: Move the Service to a Production Server

In Windows Azure, there is two stages to deploying your service. In the first stage, you deploy the service to the staging servers – these are the private servers you use to test and validate your service before you publish it to the public production server. To configure this, open the Windows Azure developer portal again, go to the Summary Tab and select your Hosted Service from the summary screen. This will bring up the summary screen for your hosted service and give you a visual representation of what is running on your production and staging servers. The screen capture below shows my Hosted Service summary and because I have previously published my project to the production server, I have an active service running on the production server (indicated by the blue cube). You will have separate URLs for the staging and production versions of your service. When you are satisfied with the service as it is running on the Staging server, you can click the circle between the staging and production cube to begin the process of deploying your service on the Production server.

When the deployment onto the production server has completed, you will see the green check mark below the Production service (as in the picture above). You now have a completed Data Service running in the cloud the is exposing your SQL Azure database! Click on the Web Site URL to open the ASP.NET web site that hosts your data service (append the .svc document to the end of the URL to get the service document of your service).

This is a fully functioning WCF Data Service that fully supports the OData protocol. Check out the Consumers page of the odata.org website to see a list of all the client applications and client libraries that you can now target your web service with.

Shayne Burgess

Program Manager

WCF Data Services and OData