In his MIX Keynote this week, Douglas Purdy demonstrated a new OData Service for SQL Azure. I am pleased to announce that a preview of this exciting technology, providing the quickest no-code solution for bringing your SQL Azure data into the growing OData ecosystem, is available to all SQL Azure users today.

Got SQL Azure?

If you have a SQL Azure database then OData is just a click away.  No need to build, maintain and host a custom service in a separate middle tier; the OData Service for SQL Azure provides a no-code solution for exposing an OData endpoint based on built-in database logic.  Exposing your data through an open HTTP protocol enables friction-free development and deployment of modern applications to a variety of devices and platforms.

To get started, just visit the OData Service Portal and click on the "OData Service for SQL Azure" tab. This wizard will allow you to select one or more databases to expose through OData.  You have the choice of mapping a user within your database to an anonymous endpoint (meaning that anyone can access the data according to the permissions of that user) or mapping one or more users to authenticated access (meaning that the application must obtain a security token based on a security key in order to access the service as that user).

Note that the login used to access the portal must have read access to your master database (in order to list the databases and users available) and your SQL Azure firewall must be configured to allow access based on the IP Address of the machine accessing the portal, or provide access to Microsoft Services (0.0.0.0-0.0.0.0), in order to configure the database.

Try it out!

Once you have your SQL Azure database configured for OData, try it out!  Access your data from any HTTP client using a URL configured for your database of the form:

https://odata.sqlazurelabs.com/OData.svc/v0.1/<serverName>/<databaseName>

where <serverName> is the name of your SQL Azure server and <databaseName> is the name of your configured database.

Once you've enabled OData, you can:

  • View your data in the browser (for best results reading OData results in Microsoft Internet Explorer, turn off Feed Reading view.)
  • Write LINQ queries against your data using LinqPad
  • Analyze data from your database using Microsoft Excel PowerPivot
  • Explore your data using OData Explorer or the Sesame OData Browser

Note that, in order to avoid run-away queries, the OData Service for SQL Azure returns a maximum of 50 rows in any one request.  If there are more than 50 results matching the query, a <link rel="next"…/> will be provided at the end of the results containing the URL to fetch up to the next 50 records.

Don't Have SQL Azure?

If you don't have a SQL Azure account, but want to try out the OData Service for SQL Azure, below are some URLs you can use to query common databases (note that these endpoints are read-only).

https://odata.sqlazurelabs.com/OData.svc/v0.1/hqd7p8y6cy/AdventureWorks

https://odata.sqlazurelabs.com/OData.svc/v0.1/hqd7p8y6cy/Northwind

Here are a handful of other interesting queries to get you started:

Top 10 customers ordered by CompanyName:
https://odata.sqlazurelabs.com/OData.svc/v0.1/hqd7p8y6cy/Northwind/Customers?$top=10&$orderby=CompanyName

All Customers from Northwind with "Sales" in their title:
https://odata.sqlazurelabs.com/OData.svc/v0.1/hqd7p8y6cy/Northwind/Customers?$filter=substringof('Sales',ContactTitle)

Just the CompanyName and ContactName form Customers in Germany:
https://odata.sqlazurelabs.com/OData.svc/v0.1/hqd7p8y6cy/Northwind/Customers?$filter=Country%20eq%20'Germany'&$select=CompanyName,ContactName

The Orders for Customer "ALFKI" ordered by required date:
https://odata.sqlazurelabs.com/OData.svc/v0.1/hqd7p8y6cy/Northwind/Customers('ALFKI')/Orders?$orderby=RequiredDate

These are great for playing around with OData, but the real power of the OData Service for SQL Azure is its ability to expose the data that you want to expose.  So get SQL Azure today!

Release Notes

The OData Service for SQL Azure is an early preview of a no-code way to extend the reach of selected data and logic within your SQL Azure store to HTTP-enabled devices, and to enable your data to play in the growing OData ecosystem. The current service is intended to start giving you a feel for the offering by exposing basic query and update capabilities over a simple derived model. Over the coming weeks we'll be adding additional functionality such as views and stored procedures, richer relationship support, and so forth.

In this version:

  1. You can navigate only a single navigation property (i.e., Customers('ALFKI')/Orders). You cannot navigate, for example, Customers('ALFKI')/Orders(10634)/Order_Details.
  2. $expand is not supported
  3. No views, stored procs, table-valued-functions (coming soon…)
  4. Most common datatypes are supported, but some of the less common datatypes may not.

FAQ:

Q: Does this bypass my built-in server logic?

A: Absolutely not.  The OData Service for SQL Azure is designed to expose the logic built into your database.  Each endpoint is configured with a particular user (either anonymous or authenticated) and the database objects and logic exposed by the endpoint are subject to the permissions granted that configured user.

Q: Does OData replace TCP as the way I should access my SQL Azure database?

A: No. TCP, where available, remains the richest and most performing way for clients to interact directly with your SQL Azure database.  OData provides reach to any HTTP-enabled device, allowing your data to be accessed by a growing set of consumer applications, tools and platforms.  Rich + Reach; nice.

Q: How does this relate to WCF Data Services and the Microsoft Entity Framework?

A: WCF Data Services and the Entity Framework pick up where the OData Service for SQL Azure leaves off.  Where the OData Service for SQL Azure provides a no-code way to expose a default entity model over your data, WCF Data Services and the Entity Framework allow you to customize that model and add additional logic in a middle tier.

Stay Tuned:

Over the next couple of weeks we'll be providing more information on how to use the OData Service for SQL Azure, including samples for writing a .NET or Silverlight client that accesses the OData service through an authenticated user.

OData for SQL Azure - You've got a feed for that!

Michael Pizzo
Architect, Microsoft Data Management Group