THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

RESTful Northwind on SQL Azure

Introduction

I recently gained access to SQL Azure, the hosted database part of Microsoft’s Azure cloud offering. I don’t currently have a reason to use SQL Azure in my day-to-day work so I set myself a small project that would enable me to prod and poke at the service and hopefully learn a bit about it. I decided to implement a RESTful service on top of the venerable Northwind database using SQL Azure (for data storage), ADO.Net Data Services (for service enabling the data) and Windows Azure (for hosting the service). The resultant service is available at: http://northwindazure.cloudapp.net/Northwind.svc/ 

Here are the results returned when asking for the Customer known as ALFKI and below that are some more sample queries that you can issue if you wish:

ALFKI results from Northwind Azure

Customer ALFKI http://northwindazure.cloudapp.net/Northwind.svc/Customers('ALFKI')
Order items with a price > 250 http://northwindazure.cloudapp.net/Northwind.svc/Order_Details?$filter=UnitPrice gt 250
All orders by customer ANATR http://northwindazure.cloudapp.net/Northwind.svc/Customers('ANATR')?$expand=Orders
All order details of all orders by customer ANATR http://northwindazure.cloudapp.net/Northwind.svc/Customers('ANATR')?$expand=Orders/Order_Details
All order details for the first 3 customers http://northwindazure.cloudapp.net/Northwind.svc/Customers?$top=3&$expand=Orders/Order_Details
All products in all orders for the first 3 customers http://northwindazure.cloudapp.net/Northwind.svc/Customers?$top=3&$expand=Orders/Order_Details/Products

Tip: Don’t forget to turn OFF your browsers feed reading abilities or else you won’t see anything very interesting – you want to see the raw data not a stylised view of it:

Turn off feed reading in Internet Explorer 8

[screenshot taken from IE8]

If all you’re interested in doing is playing around with a RESTful service then you can stop look away now but if you’re interested in more of the mechanics of how this works then keep on reading.


Overview

As I said above this solution leveraged:

In the rest of this blog post I’ll make some assumptions so that I don’t have to waste words explain things which can be learned elsewhere. Those assumptions are:

  • You know what an ADO.Net Data Service is
  • You understand the concepts of running a Windows Azure service and deploying to it
  • You have a working knowledge of SQL Server
  • You have a working knowledge of VSTS Database Edition

Both SQL Azure and Windows Azure are (in this case) hosted in North West USA and I live and work in London, UK so bear those things in mind when you’re using the service or if I mention anything of time taken to execute or similar.

At the time of writing use of Windows Azure and SQL Azure is free but this will change after the Professional Developer’s Conference (PDC) 2009 so you should assume that thereafter the service will be taken down.

All the code is available on Codeplex at http://nwindazure.codeplex.com/. There is one solution containing three projects:

  • Northwind - A Visual Studio Team System (VSTS) Database Edition (aka datadude) representation of the database schema
  • RESTNWind_WebRole – A Windows Azure web role project which in actuality is my my data service implementation
  • RESTNWind – The Azure wrapper project that takes care of management and deployment of Azure roles.

Visual Studio Solution Explorer screenshot


SQL Azure and Datadude

I’m a big fan of datadude so I wanted to kick the tyres of using it with SQL Azure.

I began by building a local copy of Northwind using InstNWind.sql which I obtained from Northwind and pubs Sample Databases for SQL Server 2000 (There were a couple of gotchas running this script on SQL Server 2008 but nothing that was too hard to fix. Executing SQL2000 scripts on SQL2008 is outside the scope of this blog post). Once I had a copy of Northwind locally I imported the DDL into my datadude project. I also saved all of the data insertions as a post-deployment script hence if you ever need or want a datadude project for Northwind (even if you are not interested in Azure) then it is freely available at http://nwindazure.codeplex.com/:

image

I then tried to deploy my datadude project against SQL Azure and that’s when the fun began! Firstly, you have to tell datadued where it should attempt to deploy to in the ‘Deploy’ section of the project properties. When building my connection string using the familiar connection properties dialog I was able to successfully validate against my SQL Azure server:

image

Upon attempting a deployment however I got the following output:

------ Build started: Project: Northwind, Configuration: Debug Any CPU ------
    Loading project references...
    Loading project files...
    Building the project model and resolving object interdependencies...
    Validating the project model...
    Writing model to Northwind.dbschema...
  Northwind -> C:\Users…\Northwind\Northwind\sql\debug\Northwind.dbschema
------ Deploy started: Project: Northwind, Configuration: Debug Any CPU ------
C:\Program Files\MSBuild\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets(120,5)Error Deploy01234: The target database schema provider could not be determined.  Deployment cannot continue.
   Done executing task "SqlDeployTask" -- FAILED.
  Done building target "DspDeploy" in project "Northwind.dbproj" -- FAILED.
Done executing task "CallTarget" -- FAILED.
Done building target "DBDeploy" in project "Northwind.dbproj" -- FAILED.
Done building project "Northwind.dbproj" -- FAILED.

Build FAILED.
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

Going on “The target database schema provider could not be determined” I am assuming that deploying to SQL Azure from datadude is not currently possible.

Thwarted, I went for another approach. I deployed against a local SQL Server instance opting to create the database from scratch – effectively mimicking what I would be doing were I able to deploy to SQL Azure. I then took the .sql file produced during that successful deployment and ran it against SQL Azure from SQL Server Management Studio. This failed for a number of reasons, the errors that I received along with the offending code are listed below:

Msg 40508, Level 16, State 1, Line 1
USE statement is not allowed other than specifying the current database for the current version of SQL Data Services.

use master
go

Msg 195, Level 15, State 5, Line 3
'ANSI_NULLS' is not a recognized SET option.

SET ANSI_NULLS, NUMERIC_ROUNDABORT OFF;

Msg 40511, Level 15, State 1, Line 2
Built-in function 'DATABASEPROPERTYEX' is not supported in this version of SQL Server.

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')

Msg 40510, Level 15, State 2, Line 3
Statement 'ALTER DATABASE' is not supported in this version of SQL Server.

ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Msg 40530, Level 15, State 1, Line 1
The CREATE DATABASE statement must be the only statement in the batch.

(This one made no sense to me because it *was* the only statement in the batch. I suspect I may have found a bug!)

CREATE DATABASE [$(DatabaseName)]

Msg 40511, Level 15, State 1, Line 125
Built-in function 'fulltextserviceproperty' is not supported in this version of SQL Server.

IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';

Msg 40517, Level 16, State 1, Line 2
Option 'allow_page_locks' is not supported in this version of SQL Server.

Msg 40517, Level 16, State 1, Line 3
Option 'allow_row_locks' is not supported in this version of SQL Server.

Msg 40517, Level 16, State 1, Line 3
Option 'pad_index' is not supported in this version of SQL Server.

Msg 40517, Level 16, State 1, Line 3
Option 'sort_in_tempdb' is not supported in this version of SQL Server.

Msg 40517, Level 16, State 1, Line 3
Option 'maxdop' is not supported in this version of SQL Server.

CREATE NONCLUSTERED INDEX [CategoryName]
    ON [dbo].[Categories]([CategoryName] ASC) WITH (
    ALLOW_PAGE_LOCKS = ON,
    ALLOW_ROW_LOCKS = ON,
    PAD_INDEX = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    IGNORE_DUP_KEY = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    ONLINE = OFF, MAXDOP = 0);

Hopefully that gives you a good idea of some of the limitations.

Once I had manually corrected all of these errors in the deployment script I was able to run it against SQL Azure and successfully create my Northwind database (with data). I have kept that edited script and made it part of the solution:

image

and hence it can be downloaded from: http://nwindazure.codeplex.com/SourceControl/changeset/view/28806#445285

 

It took 2minutes 35seconds to execute this script against SQL Azure. I took the same script and ran it against my local server and it took 3 seconds. Make of that what you will (note that there are many many single record inserts which means many many round trips so there is a lot of scope for speeding this up!)!


ADO.Net Data Services and Azure

I didn’t have to do anything out of the ordinary to build an ADO.Net Data Service and host it on Azure. Assuming you know how to build an ADO.net Data Service and you know how to publish to Azure then the only thing you really need to know is the format of the connection string that needs to get set in web.config. Here is that connection string:

<add name="NorthwindEntities"
     connectionString="metadata=res://*/NWindModel.csdl|res://*/NWindModel.ssdl|res://*/NWindModel.msl;provider=System.Data.SqlClient;provider
          connection string=&quot;Server=XXXX.ctp.database.windows.net;Database=Northwind;User ID=XXXX;Password=XXXX;Trusted_Connection=False;MultipleActiveResultSets=True&quot;"
     providerName="System.Data.EntityClient"
     />

The constituent parts of the connection string are:

  • Server=<azureservername>.ctp.database.windows.net
  • Database=<dbName>
  • User ID=<user id>
  • Password=<password>
  • Trusted_Connection=False

That’s basically it. The venerable old Northwind stored in SQL Azure and exposed as a RESTful service using ADO.Net Data Services and Windows Azure.  If you have any questions or comments please put them in the comments section below.

@Jamiet

Published Thursday, September 10, 2009 12:13 AM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Juliën said:

"don’t currently have a reason to use the SQL Azure in my day-to-day work..." Well, you sure found a great community effort for it! Great work, keep it up.

September 9, 2009 6:27 PM
 

Jonathan Kehayias said:

Awesome work Jamie.  I activated both of my subscriptions and have yet to create a useful project for it.  Good to see that someone else was able to think something up.

September 9, 2009 8:00 PM
 

Rod said:

Hello Jamie,

You once blogged on an insert/update SSIS packge.

http://consultingblogs.emc.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Is this still the best way to have an SSIS package handle an insert/update?

Are you willing to answer one more question? How to you push the "updates" to the database? Do you delete all of the changed rows first?

Sorry if this is the wrong place? Where is the most appropriate place to ask these questions?

Thanks

Rod

September 25, 2009 9:30 AM
 

jamiet said:

Hi Rod,

I think that the stuff in that blog post still stands today, yeah.

Updates: No, don't delete all the rows first. You have 2 options:

1) Use an OLE DB Command component to issue an UPDATE statement. This has the (big) disadvantage that it issues an UPDATE for every row in the dataflow.

2) Push the data to be used for updates into a different table and issue the UPDATE from an Execute SQL Task. This has the advantage that only one UPDATE statement gets issued (unlike the first option) however you do have more executables in your package.

I don't mind at all if you want to post this stuff up here however the SSIS forum (http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/threads) might be a better bet.

Hope that helps!

-Jamie

Regards

Jamie

September 29, 2009 4:10 AM
 

Nigel Ainscoe said:

Excellent - this is just the thing to give me a heads up with my SQL Azure account.

September 29, 2009 6:46 PM
 

SSIS Junkie said:

On 31st January 2010 Windows Azure and SQL Azure will transition to becoming services that you have to

January 23, 2010 5:26 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement