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:
- You can navigate only a single navigation property (i.e.,
Customers('ALFKI')/Orders). You cannot navigate, for example,
Customers('ALFKI')/Orders(10634)/Order_Details.
- $expand is not supported
- No views, stored procs, table-valued-functions (coming
soon…)
- 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
