On-premise means your application is hosted locally and not in Azure, but your database is in SQL Azure. Your application code uses client libraries to access one or more SQL Azure databases. Some companies are reluctant to put business logic or application-specific logic outside of their corporate data center, and the on-premise option provides the ability to house the data in the cloud while keeping the application logic local.

Although this is a viable option, limitations are associated with it. For example, only the following client libraries are supported:

If your application uses OLE DB, you have to change your application so use one of the client libraries listed here.

The biggest consideration related to keeping your application on-premise is the cost. Any time you move data between SQL Azure and your on-premise application, there is an associated cost (currently, $0.10 in and $0.15 out per GB, or more if your selected geolocation is Asia). If you're using Azure Storage, there is also the cost of using that storage (currently, $0.15 per GB stored per month). Again, this is per GB, so the cost is low. An example of an expensive pattern is synchronizing large amounts of data multiple times per day. But keep in mind that synching even a 50GB database costs only $5.

These costs and limitations shouldn't deter you from using an on-premise solution for your application. However, let's look at what an Azure-hosted solution provides.

Azure-hosted means that your application code is hosted in Windows Azure and your database is in SQL Azure. Your application can still use the same client libraries to access the database or databases in SQL Azure. Most companies right now are taking existing ASP.NET applications and publishing them to Windows Azure and accessing SQL Azure. However, you aren't limited to just web apps: you can use a Windows desktop app or Silverlight app that uses the Entity Framework and the WCF (Windows Communication Foundation) Data Services client to access SQL Azure as well. Again, you have plenty of options.

The benefit of using an Azure-hosted solution is the ability to minimize network latency of requests to the SQL Azure database. Just as important is the fact that you're cutting the costs of data movement between SQL Azure and the application. As long as your Windows Azure and SQL Azure are in the same subregion, bandwidth usage between SQL Azure and Windows Azure is free.

By putting both your application and database in Azure, you also get the benefit of more efficient transactions between your app and the database, because doing so minimizes the network latency between your application and the database.

But you incur the compute cost, which is currently $0.12 per hour. Compute hours is the time your application is deployed to Windows Azure. Even if your application isn't in a running state, you're being billed. Billing is per hour, and partial hours are billed as full hours. When developing and testing your application, you should remove the compute instances that aren't being used. Thus, the key here is to test locally before deploying remotely.

The decision to move your application to the cloud versus keeping it local is entirely up to you and shouldn't be determined solely by what you read in the last two sections. The decision isn't that cut-and-dried. You need to look at several other factors, such as costs, data traffic, and bandwidth, and then base your decision of the analysis of this information. For example, it may not be a sound decision for a small company with little web traffic to host an application in Azure, due to the compute costs. However, that same company can keep its database in Azure while keeping the application on-premise because the data-transfer costs are minimal, yet gain the benefits of SQL Azure (failover, and so on).

In many companies, the initial goal isn't an all-or-nothing approach. The companies spend some time looking at their databases and applications, decide what functionality makes sense to put in the cloud, and test functionality on that. They test for performance foremost, to ensure that when deployed to Azure in production, performance is in the same ballpark. The thought is to keep the important things up front to ensure a successful Azure deployment. Roll your application out in pieces, if necessary, and test locally prior to deployment.

Whether you deploy all or part of your database and application is for you to decide. Chapter 2 discussed the issue at length, and this chapter doesn't rehash it except to say that before you make a decision, you should look at all the facts.

Microsoft makes it very easy to connect an application to SQL Azure by providing the necessary connection strings for both ADO.NET and ODBC, as shown in Figure 6-1. You can find the connection information on the SQL Azure Server Administration page by selecting a database and clicking the Connection Strings button.

Figure 6.1. Connection strings

using (SqlConnection conn = new SqlConnection(connStr))
{
    try
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.CommandText = "CREATE TABLE TestTable(ID int, Name varchar(20))";
            cmd.Connection = conn;
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO TestTable (ID, Name)
    VALUES (1, 'Scott'), (2, 'Herve')";
            int val = cmd.ExecuteNonQuery();
        }
    }
    catch (SqlException ex)
    {
        MessageBox.Show(ex.Message.ToString());
    }
}

There is nothing earth-shattering or truly groundbreaking here, but let's walk though an example to see how ODBC connections work and illustrate that your ODBC classes still work as you're used to. Follow these steps:

From these examples, you can see that connecting to and querying SQL Azure is no different from connecting to a local instance of SQL Server. The end of this chapter discusses some guidelines and best practices to help you prepare for your move to SQL Azure.

So far we have discussed connecting with ADO.NET and ODBC along with the different options we have with each, so let's continue the discussion and talk about using the SqlCmd utility.

If you've worked with SQL Server for any length of time, chances are you've worked with the sqlcmd utility. This utility lets you enter and execute T-SQL statements and other objects via a command prompt. You can also use the sqlcmd utility via the Query Editor in sqlcmd mode, in a Windows script file, or via a SQL Server Agent job.

This section discusses how to use the sqlcmd utility to connect to a SQL Azure database and execute queries against that database. This section assumes that you have some familiarity with sqlcmd. This utility has many options, or parameters, but this section only discusses those necessary to connect to SQL Azure.

To use the sqlcmd utility, you first open a command prompt. At the command prompt, you need to provide the options and values necessary to connect to the SQL Azure database. As a minimum, the command syntax is the following:

sqlcmd -U login -P password -S server -d database

The parameters are nearly self-explanatory, but here they are, just in case:

Optionally, you can provide a database name via the -d parameter. Thus, the sqlcmd syntax looks something like the following:

Sqlcmd -U providerlogin@Server -P ProviderPassword -S ProviderServer -d database

Let's put this syntax to use. Follow these steps:

Let's work through another example in which you create a table and add data. Here are the steps:

The table you created is called TechGeoInfo, and it has three columns: an ID column that is the primary key (clustered index), a TechID column, and an address column. The table is simple, but it's good enough to demonstrate functionality.

Figure 6.7. Table in SSMS

As you can see, using the sqlcmd utility is straightforward. Just remember that it doesn't work with SQL Azure if you're trying to use heap tables. All tables must have a primary key. Also, as mentioned earlier, the -z and -Z parameters don't work.

This section has discussed the different mechanisms for connecting and querying SQL Azure, including examples for ADO.NET, ODBC, and SqlCmd. You can see that it quite similar to the way you currently connect to and query an on-premise database. However, with an overall industry push to an SOA architecture, let's take the discussion to the next level and discuss using services, specifically WCF Data Services, to connect to our Azure database.

First you need to create a data service. Follow these steps:

When the ADO.NET Data Service is added to your project, the associated .cs file will automatically be displayed in the IDE. As you can see the ADO.NET Data Service template has generated for you the beginnings of your data service.

Now you need to wire up your data service to your data model so that the service knows where to get its data. You know where to do this because as you can see in the code it tells you where to enter that information. Thus, change the line:

public class TechBioDataService : DataService< /* TODO: put your data source
class name here */ >

To:

public class TechBioDataService : DataService< TechBioEntities >

Wiring up your data service to the model is as simple as that. Believe it or not, you're ready to test your service. However, let's finish what you need to do on this page. By default, the WCF Data Service is secured. The WCF Data Service needs to be told explicitly which data you want to see. The instructions in the code tell you this, as you can see in code in the InitializeService method. Some examples are even provided in the comments to help you out.

For your example, you don't want to restrict anything so you really want to unlock all the entities and explicitly define access rights to the entity sets. You do this by adding the code below to the InitializeService method. The code below sets the access rule for the specified entities to All, providing authorization to read, write, delete, and update data for the specified entity set:

// This method is called only once to initialize service-wide policies.
public static void InitializeService(DataServiceConfiguration config)
{
    // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
    // Examples:
    config.SetEntitySetAccessRule("Docs", EntitySetRights.All);
    config.SetEntitySetAccessRule("UserDocs", EntitySetRights.All);
    config.SetEntitySetAccessRule("Users", EntitySetRights.All);
    config.SetEntitySetAccessRule("TechGeoInfo", EntitySetRights.All);
    config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
}

If you don't feel like specifying each entity one by one, you can optionally specify all the entities with a single line, as follows:

config.SetEntitySetAccessRule("*", EntitySetRights.All);

The above line assumes that you want to specify the same rights to all the entities. Not recommended, but will do for this example. In a production environment you want to more specific with what rights you specify for each entity.

There are other EntitySetRights options, such as AllRead, AllWrite, None, ReadSingle, and WriteAppend. You won't cover them all here but you can read about them here:

http://msdn.microsoft.com/en-us/library/system.data.services.entitysetrights.aspx

So far, you've created your Web application, added your data model, and added your WCF Data Service. Right now your Solution Explorer should look like Figure 6-11.

Figure 6.11. Solution Explorer

The next step is to add the client application. In Solution Explorer right click the solution and select Add->New Project. In the Add New Project Dialog, select the Cloud project types then select Windows Azure Cloud Service, providing a name of TechBioSite, as shown in Figure 6-12.

Figure 6.12. Adding an Azure Cloud Service

Click OK on the Add New Project dialog.

Next, in the New Cloud Service Project dialog, select ASP.NET Web Role to add it to the Cloud Service Solution pane, leaving the default name of WebRole1, then click OK.

Next, right click the Web Role project in Solution Explorer and select Add Service Reference from the context menu. This will bring up the Add Service Reference dialog shown in Figure 6-13.

Figure 6.13. Adding a Service Reference

In the Add Service Reference click the Discover button, which will interrogate your solution for existing services and display them in the Services list. As you can see from Figure 6-12, the discovery function did in fact find your TechBioDataServices service in your Web application project. The discovery also provides your local URI for the service as well as the Entities that are exposed by the service. Give the service a namespace name of TechBioServiceReference and click OK.

At this point your Solution Explorer will have your TechBioSite Cloud Service project, your WCFDataServiceWebApp project and your web role. You should see those items as they're shown in Figure 6-14.

Figure 6.14. Projects in Solution Explorer

You're almost done. You need a user interface in which to display the data you query via the data service, so open up the Default.aspx in the Azure Web Role project and select the Source tab. Replace the code that you see with the following code which defines a list box, label, and combo box:

<%@ Page Title="Home Page" Language="C#" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="WebRole1._Default" %>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:ListBox ID="docsList" runat="server"

OnSelectedIndexChanged="docsList_SelectedIndexChanged"
                AutoPostBack="true">
            </asp:ListBox>
            <br />
            <br />
            <asp:Label ID="infoLabel" runat="server"></asp:Label>
            <br />
            <br />
            <asp:DropDownList ID="authorList" runat="server">
            </asp:DropDownList>
        </div>
    </form>
</body>
</html>

The last thing you need to do is add the events that are defined in the code above, specifically the docsList_SelectedIndexChanged event. Click the Design tab for the Default.aspx page and double click the list box, which will create the docsList_SelectedIndexChanged event. However, before you put code in this event, you need to put some code in the Page_Load event as well as define a few variables. First, add the following using statements:

using System.Data.Services.Client;
using WebRole1.TechBioServiceReference;

Next, add the following declarations. These define the DataServiceContext and URI to the data service. You can get the URL from the Add Service Reference box shown in Figure 6-13:

private TechBioEntities context;
private Uri svcUri = new Uri("http://localhost:51176/TechBioDataService.svc");

Next, add the following code to the Page_Load event. This instantiates the data service context, and loads the list box with available documents and the combo box with available users:

context = new TechBioEntities(svcUri);

DataServiceQuery<Doc> docs = context.Docs;
DataServiceQuery<User> users = context.Users;

foreach (Doc d in docs)
{
    docsList.Items.Add(new ListItem(d.Name, d.ID.ToString()));
}

foreach (User u in users)
{
    authorList.Items.Add(new ListItem(u.Name, u.ID.ToString()));
}

Finally, add the following code to the docsList_SelectedIndexChanged event. This code queries the docs table to get the document information for the selected doc and displays the associated document description and price in the label, then selects the author (userid) for the selected document. By the way, the query you see below is LINQ to Entities, a LINQ (Language-Integrated Query) query that enables developers to write queries using LINQ syntax against an Entity Framework conceptual model:

var docInfo = (from d in context.Docs
                where d.ID == Convert.ToInt32(docsList.SelectedItem.Value)
                select d).FirstOrDefault();

infoLabel.Text = string.Concat("Desc: ", docInfo.Descr, "   ", "Price: ", docInfo.PurchasePrice.ToString());

authorList.SelectedIndex = docInfo.AuthorId;

You're ready to run your application! Make sure that the Web Role project is the startup project by right clicking the Web Role project and selecting Set as Startup Project from the context menu. Go ahead and press F5 to build and run the project. When the web page in Figure 6-15 comes up, the list box will be populated with the list of documents from the Docs table. Scroll through the list and select a document which will then populate the label with the appropriate description and price, as well as select the associated author in the combo box.

Figure 6.15. Running Application

Congratulations, you've successfully built a WCF Data Service that connects to, and queries, a SQL Azure database. While this was a fairly simple example, it should be enough to provide you with a solid foundation on which to start building and architecting data services for the cloud.

At this point you either get an XML representation of the data in the Users table, or the web page shown in Figure 6-17. If it's the latter, then you need to go turn off the feed reading view in Internet Explorer IE. That is because IE thinks that the data coming back is the type you would get in an RSS feed. You can see in the message that the browser thinks you're trying to view an RSS feed.

Figure 6.17. RSS Feed Page

To fix the RSS feed issue you need to turn off this feature in Internet Explorer. With IE open, from the Tools menu select Options which open the Internet Options dialog. This dialog has a number of tabs along the top which you might be familiar with. Select the Content tab and on that tab click the Settings button under Feeds and Web Slices.

Clicking the Settings button will display the Settings dialog, and on this dialog you need to uncheck the Turn on feed reading view checkbox, shown in Figure 6-18. Click OK on this dialog and the Internet Options dialog.

Figure 6.18. Disabling Feed Viewing

Back on your web page, press F5 to refresh the page. What you should get back now is a collection of Users, shown in Figure 6-19, by querying the underlying database for the Users.

However, you aren't done yet because there is still so much more you can do here. For example, the page you're currently looking at displays all the Users, but what if you want to return a specific user?

Looking at the data you can see that the each record contains the id of the specific row, and you can use that to your advantage by including that in your URI. For this example let's use ID 113. Modify the URI by appending the number 113 to the end of the URI enclosed in parenthesis, as shown in Figure 6-20.

By loading the URI which includes the id of a specific record, I can now drill down further and return just the record I am looking for. This is just like applying a WHERE clause to a T-SQL query, in this case WHERE ID = 113. In this case I have queried the underlying store for a specific user by passing the appropriate ID in the URI.

Additionally I can return a specific field by adding the field I want to the URI, such as:

http://localhost:51176/TechBioDataService.svc/Users(113)/Name

Specifying the specific field along with the id will return just the field you request. In the code snipped above, the value in the Name column for User ID 113 is returned, as shown in Figure 6-21.

You can also use this same methodology to navigate between tables. For example, you could do the following to return documents for a specific User ID:

http://localhost:51176/TechBioDataService.svc/Users(113)/Docs

Figure 6.19. Viewing All Users

Figure 6.20. Viewing a Specific User

Figure 6.21. Viewing the Name of a Specific Users

While this information isn't critical to connect to SQL Azure it's good information to have so you know how REST services work and can benefit from its functionality in your application. While this chapter did not go deep into the Entity Framework or REST technology, there are plenty of good books by APress or information on MSDN about this technology. I highly recommend that you explore these technologies further to enhance your SQL Azure applications.