Building Connected Windows 8 Apps with Windows Azure

Hey – I thought I would share this video, deck and completed demo for all of you who attended my TechEd North America + Europe sessions

Are you building a connected Windows Metro style app? This session introduces you to Windows Azure and demonstrates how you can build connected experiences for your Windows 8 Metro style apps. After walking the through the fundamentals of both platforms, we take a deep dive as we demonstrate how you can use Windows Azure to support common geo-location, multimedia, data and push notification scenarios. At the end of this session, you will be empowered to begin building and deploying your own Windows 8 client applications that are powered by Windows Azure

You can download the full video in high res from channel 9 here

Download the deck Here
Download the source Here Note I have removed the connection strings for SQL + Windows Azure storage + Bing Maps. You will find all places that you need to replace these by doing a search for TODO-REPLACE. Enjoy!

Kind Regards,
Nick

How to Sign Up for an account to Access SQL Azure for FREE

Sign Up for an account to Access  SQL Azure for FREE
There are two ways you can try SQL Azure FREE of charge:

  1. Sign up for this limited-time promotion, and you’ll get TWO 1GB Web Edition databases for one month. No credit card information is required. To get started, insert promo code SQLAZURE25 Note: at the time of writing this includes
    • Windows Azure
      • 3 Small Compute Instances
      • 3 GB of Storage
      • 250,000 Storage Transactions
    • SQL Azure
      • Two 1 GB Web Edition Database
    • AppFabric
      • 100,000 Access Control Transactions
      • 2 Bus Service Connections
    • Data Transfers
      • 3 GB In
      • 3 GB Out
  2. Get a 1GB Web Edition database for no charge for 3 months. This account requires a credit card, as any additional usage above 1GB will be billed at standard rates. After the Free Trial period, you can switch to a paid account without losing your data

For details please on the FREE access pelase see www.sqlazure.com/getstarted and visit www.sqlazure.com/community for additional resources.  And if your not already watching Cloud Cover then you should be :)

Part One – Provision and Deploy to SQL Azure Reporting Services

In many enterprise applications one often overlooked, in respect to their importance, requirement are reports. Reports are typically consumed by upper management of the organisation to determine the health of their organisation through the organisational data collected and crunched by their enterprise app. Fortunately Azure provides SQL Azure Reporting, currently in Limited CTP, to extend the already familiar development experience of SSRS to the cloud.

This is part one of a series on SQL Azure Reporting Limited CTP.  This post will focus on deployment of an SSRS report to the SQL Azure Reporting Limited CTP.  Following posts will detail how to programmatically consume these reports.

Sign Up for a Free account to Access SQL Azure
If you would like to follow along with this post there are currently two ways you can try SQL Azure FREE of charge:

  1. Sign up for this limited-time promotion, and you’ll get TWO 1GB Web Edition databases for one month. No credit card information is required. To get started, insert promo code SQLAZURE25
  2. Get a 1GB Web Edition database for no charge for 3 months. This account requires a credit card, as any additional usage above 1GB will be billed at standard rates. After the Free Trial period, you can switch to a paid account without losing your data

For details please on the FREE access pelase see www.sqlazure.com/getstarted and visit www.sqlazure.com/community for additional resources.

Once you have your account you will need to Sign Up for Access to the SQL Azure Reporting Services CTP

  1. In the Azure Management Portal click on the Reporting in the left nav bar and follow the instructions

    Sign Up for SQL Azure Reporting Services CTP

    Sign Up for SQL Azure Reporting Services CTP

  2. At some point later (in my case 1 to 2 weeks) you will receive an email with your invite and access code for the beta. Once you receive this you need to login the the portal as per above and then press the 2. Provision option and select an appropriate subscription

    Provision step 1

    Provision step 1

  3. Supply the token code provided on email

    token code

    token code

Create your Reporting Project
The development experience in VS2010 with regard to Reporting projects synonymous with Windows Mobile development i.e not supported, unless you want client reports *.rdlc. To me this raises similar questions to that of the huge leap forward seen in Windows Mobile to Windows Phone 7 – Will we soon see a similar leap forward in support for Reporting in the next release of Visual Studio? – I dont know the answer but if I had to take a pick project Cresent is looking like it will be a contender.

This being said that the way to build your reports is using SQL Server Business Intelligence Development Studio (BIDS) which is installed with SQL Server 2008 as follows:

  1. To Start BIDS goto Start >> All Programs >> Microsoft SQL Server 2008 R2 >> SQL Server Business Intelligence Development Studio
  2. Create Report project File >> New Project >> Business Intelligence Projects >> Report Server Project

    Create Report Project

    Create Report Project

  3. Create Shared Data Source  to retrieve content from your SQL Azure Database.  In the  Solution Explorer right click the Shared Data sources folder
       >> Press Add New Data Source
    • Note: At the time of writing this post please note that the SQL Azure Reporting CTP is currently only hosted in our South Central US datacenter – we strongly recommend that you host any servers and databases you might use for your reporting testing needs at this datacenter. You will be charged for bandwidth usage for data transfers to/from the South Central US datacenter should you host your data that you report against outside of this datacenter. Also, co-locating with the service will provide optimal performance.
    • Note: While Shared Data Sources are supported Shared Datasets are not currently supported in this Limited CTP
  4. Select Microsoft SQL Azure in the Type dropdown of the shared datasource properties and use the Edit button to configure and test your connection string to your Database.

    Shared Data Source Properties - General

    Shared Data Source Properties - General

  5. In Solution Explorer >> Right click on the Reports folder >> Select Add a New Report and define a report against your shared datasource.  For links to resources about learning to author reports, see SQL Azure Reporting Resources.  The image below illustrates the report design view against my shared data source.

    Report Definition

    Report Definition

  6. Note: Once you have finished defining your report you can press the preview tab (next to Design tab in the above image) to preview the report.

Deployment

  1. In a browser go to the Azure Management Portal >> select Reporting from the left Nav and then expand out your report subscription to reveal the Web Service URL and username configured through the initial provisioning process.  The image below highlights the two

    Reporting Service Details

    Reporting Service Details

  2. In Solution Explorer >> Right click your  reporting project >> select Properties
  3. Copy the Web Service URL text from the Portal as per image above in step 1 and format the url to be https://<url from management portal>/reportserver    – Note: the https  and /reportserver.  If you are getting an issue when deploying as follows When deploying the project or an item in the project, you get the error message: Could not connect to the report server …. Verify that the TargetServerURL is valid… the common cause is not formatting the url correctly.  Once your done it should be in a form similar to https://fghijk5678.database.windows.net/reportserver
  4. Press Ok on the properties dialog
  5. To Deploy to SQL Azure Reporting go to Solution Explorer >> Right click your reporting project >> Select Deploy
  6. A dialog will popup prompting for your reporting services login.  This will be the username as shown in the image in step 1 above and the password you supplied during the provisioning process.  Enter them and press Ok

    Deploy to SQL Azure Reporting. Report Portal Username and Password

    Deploy to SQL Azure Reporting. Report Portal Username and Password

  7. If deploy was unsuccessful please see the Troubleshooting section towards the bottome of this post.

Verifying your deployed Report

  1. Take the URL configured in step 3 above i.e it should be in the form https://<url from management portal>/reportserver   and append /login.aspx e.g the final form will be https://fghijk5678.database.windows.net/reportserver/login.aspx 
  2. Browse to your report server using your browser and when prompted use the same username and credential supplied when deploying your reporting project in step 6 above.

    Login

    Login

  3. Once logged in Browse to your reports folder and select your report to render
    Rendered Report - SQL Azure Reporting

    Rendered Report - SQL Azure Reporting

Troubleshooting Deployments

  1. Check out the SQL Azure Reporting Limited CTP release notes for solutions to common problems

Documentation and Feeback

  1. Documentation for getting started and using the SQL Azure Reporting CTP can be found in the SQL Azure library on MSDN here
  2. You can provide us feedback through the Connect site (here) and filing a Bug or Suggestion (Select Category = “SQL Azure Reporting) or by visiting the SQL Azure forum
  3. To vote on feature requests and make suggestions for V1 features, please visit http://www.mygreatsqlazurereportingidea.com/

Summary

This post detailed how to provision, deploy and view reports to/on SQL Azure Reporting Limited CTP.  The next post in this series will detail how to programmatically consume these reports.

Geography STDistance and Radial Search

I am currently whittling away, unfortunately rather slowly, on a new blog post series that makes use of the spatial data capabilities provided by both SQL Azure + SQL Server 2008 R2 that then exposes the content to be consumed by a WP7 client.

In the meantime while I chip away on that I was sitting wondering why my recent flight to the US was faster from San Fran to Sydney then from Sydney to LA. Taking away the variables such as head/tail winds, airspeed and flight plan, yes all important factors, we can quite easily get a raw view of the direct distance from San Fran to Sydney and LA to Sydney using spatial data and the OGC methods on the geography type – cool :)

But first we need some data i.e the latitude and longitude of the three airports. You can quite easily retrieve these manually from bing maps by saving the points in your My places with a Right Click >> Add Push Pin then once you have all three clicking Actions >> Export you can retrieve the lat + long of each place. If you wanted to automate the process you could using the Bing Maps API to geocode the address to your lat/long

The result of the manual export from bing maps is as follows, we are only interested in the lat and long for this post:

<?xml version="1.0" encoding="utf-8"?>
<gpx xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="1.1" creator="Bing Maps" xmlns="http://www.topografix.com/GPX/1/1">
<metadata>
<name>Unsaved places</name>
<desc />
</metadata>
<wpt lat="37.6235624402761" lon="-122.383551299572">
<name>San Francisco Int'l Airport</name>
<desc>San Francisco, CA 94128</desc>
</wpt>
<wpt lat="33.946468" lon="-118.384506">
<name>Los Angeles Airport Marriott</name>
<desc>5855 W Century Blvd, Los Angeles, CA</desc>
</wpt>
<wpt lat="-33.936897" lon="151.168334">
<name>Sydney Airport Medical Centre</name>
<desc>International Terminal, Mascot, NSW 2020</desc>
</wpt>
</gpx>

Calculating the distance:

-- using the geography datatype for round earth calculations rather then geometry(flat earth)
DECLARE @SanFran geography
DECLARE @LA geography
DECLARE @Syd geography

-- define the points
SET @SanFran = geography::Point(37.6235624402761, -122.383551299572, 4326)
SET @LA = geography::Point(33.946468, -118.384506, 4326)
SET @Syd = geography::Point(-33.936897, 151.168334, 4326)

-- calculate the distance between points in km
SELECT @Syd.STDistance(@SanFran)/1000 as SydToSanFran
SELECT @Syd.STDistance(@LA)/1000 as SydToLA

Results:
Sydney to San Fran: 11936.3 km
Sydney to LA: 12053 km

From this simple calc we can see that San Fran is in fact closer to Sydney then LA.

While this is a simple example you can apply what you have learnt here to perform radial searches. e.g if you have a table of Airports with their co-ordinates located in a Location column and wanted to see what airports were within a 13000 km radius you could do:

DECLARE @Origin geography
SET @Origin = geography::Point(<YourLat>,<YourLong> , 4326)

SELECT *
FROM dbo.Airports
WHERE Location.STDistance(@Origin) <= 13000000

Enjoy,
Nick

Some first time Azure deployment findings

Today I completed my first couple of deployments of an OData service to Windows Azure hosted in the Geographic Location of “Anywhere Asia”.

What I liked about the dev experience – As  a complete Azure newb I was able to do the following in under a day :

  1. Migrate an existing SQL 2008 R2 DB to Azure SQL
  2. Connect remotely to the new Azure SQL DB using SQL Management Studio
  3. Create and deploy an OData Service that is fed from the Azure SQL DB
  4. Get some blobs into the Blob storage
  5. Consumed the OData service and Blob from a Windows Phone 7 Application – All I had to do was update the client I created here Consuming an OData service from windows phone 7 to point my TestClient URI to the cloud service rather then my local. 
  6. 16 Months Free*

What I didn’t like:

  1. Could not find support in Visual Studio 2010 to deploy to SQL Azure directly.  If anyone is aware how please throw a link at me.  I had to gen deployment scripts using SQL Management Studio.  VS 2010 Data compare did not work when compairing between SQL Server 2008 R2 and SQL Azure. Note I did find this project on codeplex which might be handy – SQL Azure Migration Wizard v3.3.5

  2. newsequentialid() is not supported in SQL Azure - makes sense given that the sequential id is partially generated based on the network card therefore does not fit well with sql azure running across multiple machines.  But the issue is to get it up and running quickly i had to update to use newid() so now not sure how fragmented my indexes are going to become and this will have an impact on my insert performance so I will need to revise this.

  3. Unable to upload to blob storage from within Server Explorer.  You can only view your blobs as per image

    below and I had to code a client application just to upload the simple HelloWorld test file you see below :( … Would be great to see an Upload option in the context menu.

    Server explorer azure no blob upload
    Server explorer azure no blob upload
  4. Time to taken to deploy the service that contained only a simple Entity Model and single OData service was quite a while.  But when you look at whats going on under the hood its pretty damn quick althought it would be great to have the output window for the azure deployment indicate exactly whats happening rather then just a few initialising, busy, running statements etc.
  5. If anyone has any helpful links to the issues above – please feel free to post the links in the comments.
Nick

Change firewall rule for sql management studio connection to SQL Azure

To connect to SQL Azure from Sql Management Studio you need to change your SQL Azure firewall settings to allow access for your public IP. You can do this as follows: 1. Login to your Azure account Select SQL Azure –> Database
2. Select the Firewall Settings Tab
3. Select Add Rule and enter your IP address (in place of the 000.000.000.000). Note: your IP will be displayed under the IP Range text boxes

Azure SQL DB Firewall Rules

Azure SQL DB Firewall Rules

4. Press submit

5. Withing  ~5minutes you should be able to connect directly to your SQL Azure instance.  Note: the server name you supply to SQL management studio can be found in the Server Information Group of the image above.

Nick

Sign up to free trial of Azure using your Bizspark account

If you are a member of Bizspark you are able to get 8 – 16months “free” Azure hosting.  Cool :)

To sign up using your Bizspark account select the My Account tab from the MSDN Subscription site. Make sure you read the fine print

so whats on offer:

Grabbed the following  from here http://www.microsoft.com/WindowsAzure/offers/popup.aspx?lang=en&locale=en-AU&offer=MS-AZR-0005P

“This promotional offer provides monthly compute hours, storage, data transfers, SQL Azure databases, Access Control transactions and Service Bus connections for MSDN premium subscribers.

MSDN Premium Subscription Benefit:

  • Windows Azure
    • 750 hours of a small compute instance
    • 10 GB of storage
    • 1,000,000 storage transactions
  • SQL Azure
    • 3 Web Edition databases (up to 1 GB relational database each)
  • AppFabric
    • 1,000,000 Access Control transactions
    • 1 pack of 5 Service Bus connections
  • Data Transfers
    • North America and Europe (per region)
      • 7 GBs in
      • 14 GBs out
    • Asia Pacific Region
      • 2.5 GB in
      • 5 GB out

You may utilize up to the above amount of service each month without additional charge for 8 months following sign up for this offer as long as you maintain your MSDN Premium subscription.

Any usage each month in excess of the MSDN Premium Subscription Benefit will be charged at the MSDN Premium rates.

MSDN Premium Rates:

Windows Azure

  • Compute
    • Small instance (default): $0.1253 per hour
    • Medium instance: $0.2506 per hour
    • Large instance: $0.5011 per hour
    • Extra large instance: $1.0022 per hour
  • Storage
    • $0.1649 per GB stored per month
    • $0.011 per 10,000 storage transactions
  • Content Delivery Network (CDN)
    • $0.1649 per GB for data transfers from European and North American locations*
    • $0.2198 per GB for data transfers from other locations*
    • $0.011 per 10,000 transactions*

SQL Azure

  • Web Edition
    • $10.4286 per database up to 1GB per month
    • $52.14 per database up to 5GB per month**
  • Business Edition – Up to 10 GB relational database
    • $104.3846 per database per month
    • $208.77 per database up to 20GB per month**
    • $313.15 per database up to 30GB per month**
    • $417.54 per database up to 40GB per month**
    • $521.92 per database up to 50GB per month**

AppFabric

  • Access Control
    • $2.0775 per 100,000 transactions
  • Service Bus
    • $4.1654 per connection on a “pay-as-you-go” basis
    • Pack of 5 connections $10.3874
    • Pack of 25 connections $51.9368
    • Pack of 100 connections $207.7471
    • Pack of 500 connections $1,038.7350

Data Transfers

  • North America and Europe regions
    • $0.1099 per GB in
    • $0.1649 per GB out
  • Asia Pacific Region
    • $0.3297 per GB in
    • $0.4946 per GB out
  • Inbound data transfers during off-peak times through October 31, 2010 are at no charge. Prices revert to our normal inbound data transfer rates after June 30, 2010.

*CDN rates are effective for all billing periods that begin subsequent to June 30, 2010. All usage for billing periods beginning prior to July 1, 2010 will not be charged.

** SQL Azure 50 GB Business Edition Database and 5 GB Web Edition Database will be available starting on June 28, 2010. “

 

Some more detail available here http://msdn.microsoft.com/en-us/subscriptions/ee461076.aspx