Easy path to REST on SQL Server?

Question

At work, one of our primary databases is running on MS SQL Server. I think it's 2008.

I'm currently hitting it via UnixODBC and FreeTDS from a Linux host. Unfortunately, the UnixODBC/FreeTDS are not supported by our admins, so it falls on me. For a long-term strategy I want to move off ODBC and switch to HTTP, using either SOAP or REST calls. The installation is a lot easier with HTTP and most languages understand it, at least Perl, Python and Ruby, which I develop in.

We've got a decent amount of SOAP calls set up on the database for some Java processes, so we can probably switch to that pretty easily. We were discussing REST also, which I like because it's lighter-weight. Our DBAs don't have any REST interfaces set up, so I was wondering if there is a plugin or tool for SQL Server that can do simple REST mappings of the tables to get us jump-started. I've considered writing something in Ruby's Sinatra but that'd be one more thing on my plate.

I searched for "SQL Server + rest" but it's pretty obvious that the Googles will do the wrong thing with the word "REST" and, as a consequence, didn't find anything worth while. So, if you have experience with something providing REST and felt it was a good tool then chime in.

Answer 1

If you're on SQL Server 2008 R2, I'd recommend taking a look at the Open Data Protocol (also known as OData). Microsoft has some FAQs on it as well, and there is a Java client library available.

Also take a look at sqlREST, which exposes relational databases using web services and works with most databases that have a JDBC driver.

Answer 2

Background
From wikipedia RESTful API

REST was initially described in the context of HTTP, but is not limited to that protocol. RESTful architectures can be based on other Application Layer protocols if they already provide a rich and uniform vocabulary for applications based on the transfer of meaningful representational state. RESTful applications maximize the use of the pre-existing, well-defined interface and other built-in capabilities provided by the chosen network protocol, and minimize the addition of new application-specific features on top of it.

From MSDN article Anti-Patterns To Avoid In N-Tier Applications

REST, or Representational State Transfer, is a type of Web service that is rapidly gaining in popularity. So you might ask yourself what the difference is between RESTful services and custom Web services, and why you might choose one type over the other. The key difference between the two types is that REST services are resource-centric while custom services are operation-centric. With REST, you divide your data into resources, give each resource a URL, and implement standard operations on those resources that allow creation, retrieval, update, and deletion (CRUD). With custom services, you can implement any arbitrary method, which means that the focus is on the operations rather than the resources, and those operations can be tailored to the specific needs of your application.
...
If you can fit your service into the constraints of REST, doing so will buy you a lot of advantages. ADO.NET Data Services in combination with the Entity Framework makes it easy to create both RESTful services and clients to work with them. The framework can provide more functionality to RESTful services automatically because the services are constrained to follow a specific pattern.

Implementation

• WCF Data Services "ADO.NET Data Services exposes data, represented as Entity Data Model (EDM) objects, via web services accessed over HTTP. The data can be addressed using a REST-like URI"

  • Using Microsoft ADO.NET Data Services

  This document describes how to create and use Microsoft® ADO.NET Data Services, and discusses various details around the URI and payload formats. This document is meant as an introduction to ADO.NET Data Services and thus covers the core aspects of the technology, defering discussion of more advanced topics to companion documents.

  The goal of Microsoft® ADO.NET Data Services is to enable applications to expose data as a data service that can be consumed by web clients within corporate networks and across the internet. A data service is reachable via regular HTTP requests, using standard HTTP verbs such as GET, POST, PUT and DELETE to perform CRUD operations against the service. The payload format used by the service is controllable by the application, but all options are simple, open formats such as JSON and Atom/APP.

  The use of web-friendly technologies make ADO.NET Data Services ideal as a data back-end for AJAX-style applications, Rich Interactive Applications and other applications that need to operate against data that is stored across the web.

• or for fully customised version look into: Canonical REST Entity Service "Demonstrates a REST Service built with WCF 4 that fully complies with HTTP specs for use of GET, PUT, POST, DELETE and includes unit tests to test compliance"

Answer 3

I work for DreamFactory Software, and we have something that might help. DreamFactory is a free open source REST API Backend that connects to any SQL database including SQL Server. Hook up the DB and you get 45 services that do everything including stored procedures, SQL Views, etc. The query filter API is very extensive and has ordering, commit and rollback, pagination, filters, and related objects. DreamFactory 2.0 also has SOAP conversion capabilities.