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:
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:
[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.
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/:
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:
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. | |
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:
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="Server=XXXX.ctp.database.windows.net;Database=Northwind;User ID=XXXX;Password=XXXX;Trusted_Connection=False;MultipleActiveResultSets=True""
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