Saturday, 27 February 2010

Setting up a simple web app talking to a database in the Azure Cloud

I went to an Azure Open Space Coding Day in Birmingham organised by Dave Evans with help from Eric Nelson, Dave Gristwood and a few others from Microsoft. As a complete Azure newbie, I found the day extremely useful. There were 30 developers working together to learn more about what Windows Azure which made it a lot easier to get past any stumbling blocks which I would have undoubtedly hit if I was on my own!! I have written up here a few things I learnt today so that I can look back and remember..

Setting up Windows Azure

The first thing to do is to set yourself up with Visual Studio, SQL Server 2008 and the Azure Toolkit!

I decided to use the Visual Studio 2010 Release Candidate, SQL Server 2008 R2 Express [Tools Only] and the Windows Azure Toolkit (February 2010 Release). Many of the other developers used Visual 2008 without any major issues. Beta 1 of VS2010 caused some issues hence I rapidly updated before the start!!

You need to have a windows azure account. You can get a trial for Windows Azure for 25 hours of a small computer instance, 500MB storage and 10000 storage transactions.

Once you have your trial or account set up you can access the Windows Azure Portal and entering your live credentials. Once in you should arrive at a screen like this.


Once on this page you can click the “project 1” link to access the services currently associated with this project..


Here I have created two services Web Test and Web Test 2. You can create a new service by clicking the “New Service” link. Two options are shown “Storage Accounts” or “Hosted Services”. To set up a new hosted web role click the later. Give it a name and a description. For example “Web Test 2” and “A demonstration of a web application in the Azure cloud”. Click “Next” and you are presented with a screen asking for a public service name, which is a URL that the service can be accessed from. You may need to check the availability if you have a common word or name! You then have the option of hosting it with other services you already have or not. For this demonstration I selected “No” and set the region to “North Europe”. If all goes well you should see your service listed similar to the screen above. This means we now have something to publish too!

Setting up a SQL Azure Service

Now the best thing to do is set up a SQL Azure service. To do this on the navigation bar on the left hand side click “SQL Azure”. You will be presented with a similar project screen. Click your project you have set up. You will be presented with a terms of use screen, click “I Accept”. You will then be prompted with a Create Server screen as shown below.

Create Azure SQL

Provide a administrator credentials and the location to create the server. Take note of these, as you will definitely need them later… :)  [NB name of server is fake]


First thing to do here is to click the “Firewall Settings” tab and allow the SQL azure service to be connected remotely from management studio.

Check the “Allow Microsoft Services access to this server” and add/edit a rule to open the acceptable IP Address Range. Make sure the IP you wish to connect with is in the range!

With this done the services in the cloud are all set up and now we can get to work with building the database and the web application to talk to it.

We need to create a new database to connect to later. Click “Create Database” button. A modal should pop up asking you a name and size of database you want to use. Type in “AzureTest” and select 1GB and click “Create”. Your database should appear in the list now and you can test the connectivity by using the credentials when clicking the “Test Connectivity” button. Also if you click ‘ConnectionStrings’ a modal will come up and show your connection string. This will be handy later when we connect via the web application so note it down for now. It should look something like:

   1: Server=tcp:<ServerName>;Database=TestAzure;User ID=azuredemo@<ServerName>;Password=myPassword;Trusted_Connection=False;Encrypt=True;

Okay we are done with this control panel for the moment.

Creating a new database locally and migrating it to the Cloud

Fire up SQL Server 2008 R2 management studio. Make sure it is the correct version as earlier versions will not allow you to connect to the SQL Azure service. Create a new database on your local SQL Server called “AzureTest”, and run the following script:

   1: /****** Object:  Table [dbo].[TestTable]    Script Date: 02/27/2010 21:42:04 ******/
   3: GO
   5: GO
   6: CREATE TABLE [dbo].[TestTable](
   7:     [ID] [int] NOT NULL,
   8:     [Name] [varchar](50) NOT NULL,
  10: (
  11:     [ID] ASC
  13: )
  14: GO
  15: INSERT [dbo].[TestTable] ([ID], [Name]) VALUES (1, N'TestValue1')
  16: INSERT [dbo].[TestTable] ([ID], [Name]) VALUES (2, N'TestValue2')
  17: INSERT [dbo].[TestTable] ([ID], [Name]) VALUES (3, N'TestValue3')
  18: INSERT [dbo].[TestTable] ([ID], [Name]) VALUES (4, N'TestValue4')
  19: INSERT [dbo].[TestTable] ([ID], [Name]) VALUES (5, N'TestValue5')

Hopefully you should see a screen like the following:


Okay it is only a rather small database with some data in it but it is enough to demonstrate this!

To copy the “AzureTest” database into the cloud you can either:

1) Use Tools /Generated script feature in SQL Server 2008 R2 Management Studio and connect and run the database script on the remote server.


2) Download and run the SQL Azure Migration Wizard v3.1.8 if you have not got R2!

NB: Exporting data does not seem to work currently as the SQL Azure database is missing a stored procedure required to pull the database listings.

Using SQL Server 2008 R2 Management Studio

Click right button on the “AzureTest” database and select Tasks and generate scripts. A dialog should appear which allows you to choose the objects you wish to script. Click “Next” if you are on the Introduction screen (if you have not checked the box “do not show this again”).

You should see a screen as shown below.


Select the “Select specific database objects” and select the created table and click “Next”. Now you should be on the “Set Scripting Options” screen.

Click the “Advanced” button. This brings up another dialog see below. Take note of the red highlighted areas.

The “Script for the database engine type” must be selected to “SQL Azure Database” – note this option is not available on earlier versions of management studio. Also make sure the “Types of data to script” is set to “Schema and data”.

image7 Select the “Save to new query window” radio button and click next.

You are presented now with a summary, click next and if all goes well the script should be generated and ready to use with your remote SQL Azure service. Click “Finish”. The query should appear in a new window of management studio.

Okay now we need to connect to the remote SQL Azure database we set up earlier.

Click “File” menu and select “Connect Object Explorer”. Now in the “Connect to Server” dialog type in the server name which can be found on your Server Administration page on the SQL Azure administration website you created the server on earlier. It should look like: <servername>

Select “SQL Server Authentication” and provide the credentials you specified on the server configuration in the boxes provided.

You should now have access to the SQL Azure service see screenshot below. Select the “AzureTest” database and click right button and select the “New Query” option. Now copy the script you created locally to this window and execute the query. You now have data in the cloud :)

Using SQL Azure Migration Wizard

NB: You do not need to do this step if you have used SQL Server R2 Management Studio above..

Download the migration wizard from here. Unzip and run the executable called SQLAzureMW.exe – you should see a window as shown below..


Select “Analyse and Migrate” and the “SQL Database” radio option and click “Next”. You should now have a “Connect to Server” dialog. Provide the local sever and credentials to connect to it. From the list of databases presented select “AzureTest” and click “Next”. Select “Select Specific Database Objects” radio button and select the “Test Table”. Click “Next”. View the summary and click “Next”. Respond “Yes” to the generate SQL script dialog.

Any issues are reported (but as its a simple none are reported). Beware that things like xml types, multi-part views may throw issues as they may not be implemented in SQL Azure when you try this.. You can optionally save the script here by clicking “Save”.  Now click “Next”.

You are now presented with the Target Server Connection Dialog. See below.


Replace the [SERVER], and provide the username and password credentials. You should be provided a list of databases on the SQL Azure server. You may see the database as shown earlier if you created it. If not click “Create Database” and call it Test Azure. Now select the database and click “Next”. You will be prompted to execute the script against the destination server. Click “Yes”. This may take some time if its a long script. Click “Exit”. You now have data in the cloud!! To verify this go to the SQL Azure control panel, the size of the database should be greater than 0KB.

Creating a new Cloud Service to host Web Application

NOTE please make sure you have the Azure SDK installed!! Click here to download the SDK!

Now we need to create a new cloud service to host the web application. Open Visual Studio (remember I am using 2010 RC) using ELEVATED PERMISSIONS (i.e run as Administrator). Click “New Project” link. Then choose the cloud template for your favourite language (I did Visual C#' and Cloud). Type in a name for the solution if you want to – I used “TestAzureCloudService”.

You now will be prompted with selecting a new role for your cloud service project (see below). I want to create a web forms application for this demo. So I am going to use ASP.NET Web Role. Click the right arrow button to add the role to the right panel and click OK. The solution should now open to something resembling what is shown below.


Now we need to generate a simple web application. I used the following code for default.aspx and web.config. NB Check the connection string for database and password – don’t forget them otherwise you will get a breaking error.

default.aspx Markup:

   1: <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebRole1._Default" %>
   3: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
   5: <html xmlns="">
   6: <head runat="server">
   7:     <title></title>
   8: </head>
   9: <body>
  10:     <form id="form1" runat="server">
  11:     <div>
  12:         <h1>Hello Universe</h1>
  14:         <p>Trying to get this in the cloud...</p>
  16:         <p>
  17:             No Test Records: 
  18:             <asp:Label ID="lblTestRecordsCount" runat="server" />
  19:         </p>
  20:     </div>
  21:     </form>
  22: </body>
  23: </html>

default.aspx.cs Code-behind:

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Web;
   5: using System.Web.UI;
   6: using System.Web.UI.WebControls;
   7: using System.Data.SqlClient;
   8: using System.Configuration;
  10: namespace WebRole1
  11: {
  12:     public partial class _Default : System.Web.UI.Page
  13:     {
  14:         protected void Page_Load(object sender, EventArgs e)
  15:         {
  16:             SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test1"].ConnectionString);
  18:             SqlCommand cmd = new SqlCommand("Select count(*) from dbo.TestTable", conn);
  20:             conn.Open();
  22:             int addresstypesc = (int)cmd.ExecuteScalar();
  23:             lblTestRecordsCount.Text = addresstypesc.ToString();
  25:             conn.Close();
  26:         }
  27:     }
  28: }


   1: <?xml version="1.0"?>
   2: <configuration>
   4:     <configSections>
   5:       <sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
   6:         <sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
   7:           <section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
   8:           <sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
   9:             <section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="Everywhere" />
  10:             <section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
  11:             <section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
  12:             <section name="roleService" type="System.Web.Configuration.ScriptingRoleServiceSection, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication" />
  13:           </sectionGroup>
  14:         </sectionGroup>
  15:       </sectionGroup>
  16:     </configSections>  
  18:     <system.diagnostics>
  19:         <trace>
  20:             <listeners>
  21:                 <add type="Microsoft.WindowsAzure.Diagnostics.DiagnosticMonitorTraceListener, Microsoft.WindowsAzure.Diagnostics, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
  22:                     name="AzureDiagnostics">
  23:                     <filter type="" />
  24:                 </add>
  25:             </listeners>
  26:         </trace>
  27:     </system.diagnostics>
  28:     <appSettings />
  29:   <connectionStrings>
  30:     <add name="test1" connectionString="Server=tcp:&lt;myserver>;Database=TestAzure;User ID=azuredemo@<myserver>;Password=<MyPassword>;Trusted_Connection=False;Encrypt=True;"/>
  31:   </connectionStrings>
  32:     <system.web>
  33:         <compilation debug="true">
  35:           <assemblies>
  36:             <add assembly="System.Core, Version=, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
  37:             <add assembly="System.Data.DataSetExtensions, Version=, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
  38:             <add assembly="System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
  39:             <add assembly="System.Xml.Linq, Version=, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
  40:           </assemblies>
  42:         </compilation>
  43:         <!--
  44:             The <authentication> section enables configuration 
  45:             of the security authentication mode used by 
  46:             ASP.NET to identify an incoming user. 
  47:         -->
  48:         <authentication mode="Windows" />
  49:         <!--
  50:             The <customErrors> section enables configuration 
  51:             of what to do if/when an unhandled error occurs 
  52:             during the execution of a request. Specifically, 
  53:             it enables developers to configure html error pages 
  54:             to be displayed in place of a error stack trace.
  56:         <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
  57:             <error statusCode="403" redirect="NoAccess.htm" />
  58:             <error statusCode="404" redirect="FileNotFound.htm" />
  59:         </customErrors>
  60:         -->
  62:       <pages>
  63:         <controls>
  64:           <add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
  65:           <add tagPrefix="asp" namespace="System.Web.UI.WebControls" assembly="System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
  66:         </controls>
  67:       </pages>
  69:       <httpHandlers>
  70:         <remove verb="*" path="*.asmx"/>
  71:         <add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
  72:         <add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
  73:         <add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false"/>
  74:       </httpHandlers>
  75:       <httpModules>
  76:         <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
  77:       </httpModules>
  79:     </system.web>
  81:     <system.codedom>
  82:       <compilers>
  83:         <compiler language="c#;cs;csharp" extension=".cs" warningLevel="4"
  84:                   type="Microsoft.CSharp.CSharpCodeProvider, System, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089">
  85:           <providerOption name="CompilerVersion" value="v3.5"/>
  86:           <providerOption name="WarnAsError" value="false"/>
  87:         </compiler>
  88:      </compilers>
  89:     </system.codedom>
  91:     <!-- 
  92:         The system.webServer section is required for running ASP.NET AJAX under Internet
  93:         Information Services 7.0.  It is not necessary for previous version of IIS.
  94:     -->
  95:     <system.webServer>
  96:       <validation validateIntegratedModeConfiguration="false"/>
  97:       <modules>
  98:         <remove name="ScriptModule" />
  99:         <add name="ScriptModule" preCondition="managedHandler" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
 100:       </modules>
 101:       <handlers>
 102:         <remove name="WebServiceHandlerFactory-Integrated"/>
 103:         <remove name="ScriptHandlerFactory" />
 104:         <remove name="ScriptHandlerFactoryAppServices" />
 105:         <remove name="ScriptResource" />
 106:         <add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode"
 107:              type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
 108:         <add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode"
 109:              type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
 110:         <add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
 111:       </handlers>
 112:     </system.webServer>
 113:     <runtime>
 114:       <assemblyBinding appliesTo="v2.0.50727" xmlns="urn:schemas-microsoft-com:asm.v1">
 115:         <dependentAssembly>
 116:           <assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35"/>
 117:           <bindingRedirect oldVersion="" newVersion=""/>
 118:         </dependentAssembly>
 119:         <dependentAssembly>
 120:           <assemblyIdentity name="System.Web.Extensions.Design" publicKeyToken="31bf3856ad364e35"/>
 121:           <bindingRedirect oldVersion="" newVersion=""/>
 122:         </dependentAssembly>
 123:       </assemblyBinding>
 124:     </runtime>
 126: </configuration>

Now by default the cloud service is configured to run one instance. That is fine for this demo. So hit F5 to test the web application. NB This will actually use data from the cloud!! You should see the screen below…


So we have successfully managed to run the web application on the local development fabric. Now we need to get this web application into the cloud.

Deploying Azure Web Service to Cloud

First of all stop the debugger! We need to now build a package so that it can be deployed via the Windows Azure control panel. To do this you need to PUBLISH the cloud service. Under the “Build” menu select the Publish option.

Once the package is built a browser window will open and ask you which project you wish to deploy it to. This is identical to the screen after you created the first project.


Select the project and choose a service you wish to deploy it to. In my case I selected “Web 2”. You should then see the screen..


Click the “DEPLOY” button.


This screen asks you to provide the package and configuration file. You can find these by browsing to the code directories on your local machine.

The files should be located at:-


Here you should find two files: ServiceConfiguration.cscfg and TestAzureCloudService.cspkg

Fill the appropriate text boxes, give the deployment a name and click “Deploy”.


You should hopefully get a screen like the above after a few moments. When the package has been deployed the following screen warns you that the Azure Service is stopped (and still charging you money!!). Click “Run” to get the service moving.


Once the deployment it will go through an initialisation and busy phase, before the site is available.



If you are wondering what the line and left arrow is on the right hand side it opens up the “Staging” feature, which allows you to deploy an update without taking down the production site, and then switching the site over once its in place. The downside is that you get charged twice!! Alternatively you can upgrade the site!


Okay almost there, well apart from actually viewing the actual website itself. Copy the URL under “Website URL” and open a browser tab..