Host ASP.NET MVC Apps on Azure WebSite Without Spending a Cent on Databases

Posted by: Suprotim Agarwal , on 4/15/2013, in Category Windows Azure
Views: 23362
Abstract: Host an ASP.NET MVC Web Application on Azure WebSites for free and use a SQL Backing Store that you are not paying for separately

Clouds have been on the IT horizon for a while, but now, they are on us and well and truly raining down with services and features on a daily basis! Yes, I am talking about the ‘Cloud Computing’ kind of clouds not the ‘Cumulonimbus’ clouds!

In the last couple of articles, we have showed you how to deploy your ASP.NET applications on Azure’s IaaS offering Azure Websites. You may have noticed we’ve not talked about Databases in them and in fact the sample application doesn’t use a database. Why? Well while we try the Azure service out, we would stay away from cost incurring pieces of Azure  and Databases, unlike AzureWebsites, are not free. Now, cost for a piece of infrastructure is perfectly valid, but often our needs don’t justify the cost. For example if we have a low traffic hobby site that we would like to host on Azure Web Site, having a free alternative to SQL Server is welcome!

 

Today, we’ll see how we can use SQL Server Compact 4.0 as a backing store for our ASP.NET web app. Thanks once again to Sumit Maitra for co-authoring this article with me.

Building and Deploying a Sample ASP.NET MVC Application on Azure

Let’s start off with a simple MVC App.

- To add some pizazz, we’ll use one of the new SPA templates and call our App ‘TwoNotes’ (as opposed to the real thing called ‘One Note’).

new-spa-project

- We use the regular ‘Single Page Application’ template

select-spa-template

- Once the App is ready, we right click on the App_Data folder and select Add > New Item. From the Add New Item dialog, we’ll select SQL Server Compact 4.0 Local Database.

Note: You can skip this step and simply setup a correct DB Connection, the DB will get created at runtime the first time around.

add-sql-server-compact-file

- This will add a reference to the System.Data.SqlServerCe assembly

new-reference-added

This however does not come from Nuget. If it does not come from Nuget, how can we be sure that the Azure VM will have this dependency? We can’t be and in fact it is not available by default on Azure WebSites. The following steps become very important now, whether you deploy from Git or deploy using Web Deployment Wizard.

Setting up SqlServerCompact for proper deployment on Azure WebSites

Step 1: Get the SqlServerCompact dependencies

Open up the Package Management Console and fire the following request

PM> Install-Package SqlServerCompact

It will first check if you have the above dependency in place. If not, it will add it (from GAC) and then download a bunch of dependencies for SqlServerCompact to work correctly on Azure Web Sites as we can see below.

sql-server-compact-dependencies

Once Nuget has added these dependencies by enabling Nuget Package Restore, we can be rest assured these will be downloaded when not available.

Step 2: Next we have to add Reference to System.Data.SqlServerCe.dll such that it is available at build/deploy time.

The System.Data.SqlServerCe reference that we saw above, does not come from Nuget, so do the following:

  • Select it from the Reference folder and hit F4
  • In the properties panel, set the “Copy Local” property to True. Now rebuild the app.
  • Make sure the bin folder is visible in Solution Explorer by clicking on Show all files and from the bin folder, Copy (Ctrl+C) the System.Data.SqlServerCe.dll to the _bin_deployableAssemblies folder.
  • Remove reference of the DLL from the project.
  • Add the reference back by browsing to the above folder and selecting the dll you just copied.

Step 3: Finally install Entity Framework Support SQL Server Compact using the following Nuget command.

PM> Install-Package EntityFramework.SqlServerCompact -Version 4.3.6

With that all the dependencies for SqlServerCompact dependencies are set. Let’s go ahead with the app now.

The SQL Compact Connection String

We open the Database Explorer, expand the Data Connections node, select TwoNotes.sdf and click on the Refresh button. This will initialize the connection and the Node will show a Green plug instead of a red cross.

db-connection

- Now right-click on it and select Properties. This will select the Properties pane with data similar to what’s shown below

connection-string

Note that the Connection String only has Data Source=”<Full Path>”. Also the Provider information is not Assembly information, rather a description! So how do we get the connection string?

- Open the web.config and replace the existing DefaultConnection string with the following

<add name="DefaultConnection" connectionString="Data Source=|DataDirectory|\TwoNotes.mdf"
  providerName="System.Data.SqlServerCe.4.0" />

As we can see above, we are using the |DataDirectory| notation to retrieve the Application Path + App_Data folder and then adding the name of our DB to complete the path. The Provider Name took a little bit of searching and the correct values as we can see is “System.Data.SqlServerCe.4.0”.

The App and the DB

- Now let’s run the App and Ta-Da! The default login page comes up

login-page-local

- We have to register first, so we’ll create an account with the follow credentials and log in

UserName: twonotestest

Password: tw0Not3sTest

- After a brief pause for the first time to create the required tables, the system logs in and we are presented with the following

to-do-list-local

- Mark the first two Items as complete and add a Third Todo Item (and tab out to commit). Switch back to the Database Explorer in Visual Studio. Expand the Tables Node for the DefaultConnection (TwoNotes) and there you see all the required tables for both Authentication as well as the Data Items for the ToDo list in place.

todo-items-table

- Right click on the TodoItems table and select ‘Show Table Data’. You’ll see something similar

todo-items-data

The first two items are marked IsDone=True whereas the Third one is added with IsDone=False.

So now we have the app fully functional at our end. Let’s commit the code to GitHub and Pull the source into Azure via GitHub integration

Checking in code to Source Control

I am assuming here that you have read our GitHub with Visual Studio 2012 integration article and are on the cutting edge here. If you don’t use GitHub, feel free to use your favorite source control. It’s likely to be supported by Azure. If you don’t want source control integration and want to do the old style Web Publishing Wizard, we are game there too. Essentially put your code in GitHub/BitBucket/DropBox/TFS or SkyDrive. Azure can pull it from all these locations. Here, I am assuming our code is in GitHub.

Make sure the TwoNotes.sdf file is checked into source control, some Version Control Clients tend to ignore it.

Deploying to Azure by pulling build in from GitHub

For details of how to do this is already explained in our previous article. Essentially select the website, choose to Integrate with Source Control, Select Git Hub, give Azure permissions to your GitHub Repo.

select-repository-to-deploy

Finally pick the correct repository and branch and hit ‘Complete’. Azure will start the deployment immediately.

azure-deploy-in-progress

If all goes well, deployment will succeed and you’ll get the following Message

deploy-completed

If you note above, the two Deployment IDs are different and that’s because my first deployment failed. Some Git clients leave out the App_Data folder by default that resulted in a build failure for me. So if you get a failure, check that out. Also make sure you have not missed the step to add the Nuget Package for SqlServerCompact and its Entity Framework dependencies.

With everything deployed, let’s visit out site and see if the data we had in Dev has moved over or not. We navigate to http://twonotes.azurewebsites.net/ and login using same credentials as earlier! Bingo!

data-in-production

Deploying using Web Publishing Wizard

If deploying via source control is too cutting edge for you, you could always deploy using Web Publishing Wizard. Simply download the publishing profile, Start the Web Publishing Wizard and deploy! Easy!

Wrapping up

Even though it seemed rather seamless, there are a few rough edges to deploying SqlServerCompact. You have to remember that the System.Data.SqlServerCe.dll doesn’t get uploaded automatically, so you’ve to somehow get it into the bin folder at runtime. We showed you one way. Also worth keeping in mind is the little documented requirement for the EntityFramework for SqlServerCE. It usually works in Dev environment without that particular dependency added explicitly, but bombs in production.

There you go, all the caveats we found, we’ve shared and now you have a Web Application Hosted on Azure WebSites for free and using a SQL Backing Store that you are not paying for separately!

Download the entire source code of this article (Github)

Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by John Marsing on Thursday, June 6, 2013 12:39 PM
Nice!  I have an idea of using ELMAH and then use SQLCE as the place to store my logs.  I might have a connection to a "real" database for all the normal stuff that's a completely separate database.
Comment posted by John Marsing on Thursday, June 6, 2013 1:22 PM
Nice!  I have an idea of using ELMAH and then use SQLCE as the place to store my logs.  I might have a connection to a "real" database for all the normal stuff that's a completely separate database.
Comment posted by Henning Eiben on Thursday, June 20, 2013 1:36 PM
You have to keep in mind, that when the VM should be redeployed in azure (for whatever reason) that the database with it's content will be lost, because the local storage in the VM is not persitent.
Comment posted by Chaz Gatian on Saturday, November 2, 2013 9:51 AM
Great article.  I pulled the code off GitHub, built and deployed, and I am receiving an error on Azure once I published.

http://twonotes2.azurewebsites.net/
Comment posted by ddit on Tuesday, July 22, 2014 4:17 AM
gggggggggggggggggggooooooooooooooooo

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel