Tuesday, March 10, 2009

SQL Data Services Abandons REST for TDS API and Knocks My Socks Off

Apparently SQL Data Services (SDS) wasn’t at the point of no return today when the SQL Server Team announced that the REST interface for SDS would be abandoned in favor of  SQL Server’s traditional Tabular Data Stream (TDS) transport and relational database management (RDBMS) features. This isn’t the mid-course correction that I wrote about on 2/24/2009, it’s now a mid-course reversal.

Leverage Your .NET, Visual Studio, SQL Server and T-SQL Programming Skills

What the SQL Server and SDS teams describe in their Next for SQL Data Services… and The no spin details on the new SDS features posts of 3/10/2008 is “SQL Server 200x for the cloud.” The Azure marketing folks have touted leveraging your .NET and Visual Studio programming skills as a primary selling point. SDS database design and programming techniques had little or no relationship to developers’ experience with RDBMSs, especially SQL Server and T-SQL.

Switching to a fully-featured SQL Server instance—or a reasonable facsimile thereof—lets the SDS team climb on the leverage your existing [SQL Server and T-SQL] skills bandwagon. Doing this also provides a service to compete directly with Amazon Web Services’ Windows Server 2003/SQL Server 2005 [Express] AMIs. It also lets Microsoft add a surcharge for SDS over Azure tables that’s “competitive” with Amazon’s US$ 0.60 or 1.20 per hour.

S[S]DS was built on a customized version of SQL Server 2005; here’s hoping that the new version will use SQL Server 2008, which enables “transparent data encryption, TDE” of sensitive/confidential data (Enterprise Edition only), supports spatial data types, and offers a raft of other useful new features. Whatever the SQL Server version the team chooses, it behooves them to make a CTP of the new SDS available sooner than “mid-2009.”

(Perhaps moving to TDS will enable the Azure or SDS Team to reply to my question about SAS 70 Audits for Windows Azure and SQL Data Services? of 3/9/2009. There’s obviously an issue regarding private encryption key management and accessibility.)

Update: Steve Marx replied for the Azure team on 3/10/2009:

We are in the process of evaluating various certification requirements relative to Windows Azure with a goal toward achieving key certifications by commercial launch or shortly thereafter.

But the reply is rather vague as to the specific question of SAS 70 audits.

Getting Azure Apps Closer to the Data by Abandoning SDS’s RESTful Inteface

TDS isn’t an Internet-friendly protocol; it’s intended to speed communication between front-end apps and back-end tables over a high-speed on-premises LAN. If you want to use SDS as a scalable data store for on-premises front ends with HTTP[S] access via TCP port 80 or 443, you must implement your own ADO.NET Data Services (Astoria) or SOAP data source. Hopefully Pablo Castro and the Astoria team will provide a pre-built REST data source with the features promised in Pablo’s Adding support for JSONP and URL-controlled format to ADO.NET Data Services post of 2/25/2009 and Mike Flasko’s Announcing ADO.NET Data Services v1.5 CTP1 post of 3/1/2009. However, it’s certain that SDS will connect to Azure Hosted Services more often by orders of magnitude than via the Internet to on-premises front ends. The latter was the original premise of SSDS (before MSFT announced Azure.)

Will It Scale?

It’s conventional wisdom that relational databases aren’t as scalable as the schemaless Entity-Attribute-Value (EAV) tables used by SSDS, original SDS, Amazon SimpleDB, and Google’s BigTable and the App Engine’s data store. If the SSDS team was leveling with us when they attributed the choice of the EAV (Authority-Container-Entity) data model to simplifying data access (not scalability), it could turn out that conventional wisdom was wrong in this case. What scares me is the problem of rolling out a schema update to a multi-terabyte database. As with most other issues of this type, time will tell.

David Robinson was right when he said in his SQL Data Services – What’s with the silence? post of 2/24/2009, “This time around we will be unveiling some new features that are going to knock your socks off.” I’m glad the SQL Server and SDS teams didn’t wait until MIX 09 to drop this bomb.

Stay tuned for updates until the other shoe falls.


Anonymous said...

Makes sense ..too much overlap with
Azure storage services ( queues , blobs and tables) , SQL services and Life services (Contacts etc)

We now have
SQL services focuses on scalable sql not the transport.

There will probably be an add on so you can access SQL over Rest ( as you mentioned)

Azure Services has simple REST type Data ( tables , blobs etc) sufficient for most web site which are the main users of REST.

This way SQL team can focus on making SQL scalable rather than worry about the security and and XML to SQL results mapping.

ErikEJ said...

There are some nice drawings of the TDS architecture here: http://msdn.microsoft.com/en-us/library/dd557595.aspx

Roger Jennings (--rj) said...


The second illustration has unreadable captions.

Anonymous said...

The anonymous December guys returns...

Oh dear oh dear, this brings a whole new meaning to the term "eventually consistent".

Many of us have known since the 2008 PDC that SDS was functionally deficient and when coupled with evident poor performance as demonstrated by Oakleaf, the good ship SDS was not destined to float. But does this new fuzzy technical direction make sense? Why didn't Microsoft fix the original SDS instead? Transactions, web tier connection pools and identity columns would have made SDS V1.0 viable.

Everyone now seems to be cheering near native SQL Server hosting in the Azure cloud and the prospect of shifting existing apps as is. But does this make sense technically and can Microsoft host it economically?

Let me highlight two problems introduced by this SDS u-turn:

1 - Previously SDS provided a readymade model for multi-tenancy and economic scale-out at the tenant boundary. Now with SDS V.Uturn.01 how will I segment my SaaS customer data? Does Microsoft want me to declare a new DB instance per customer? Even if Microsoft hides such a DB clone operation from me, Microsoft will still suffer the hosting overhead of 10,000's of micro databases with each one tying down server resources for data and a query plan cache. There is a plethora of other per DB overheads.

2 - Ok let's move on and think about security. I am horrified at the prospect that my data could be hosted on an Azure SQL Server dangling a TDS port open to all comers on the internet. This is an invitation to hackers around the world saying "pillage my data, download my schema, truncate my tables and restore me at will". TDS is the SQL Server protocol that conveys all forms of raw database administration requests into the heart of the SQL Server engine. Scary!

Microsoft now faces a massive engineering task that involves ripping open the core SQL Server engine and hooking this up to the Azure fabric controller at an intricate level not foreseen 9 months ago. I have doubts that Microsoft possessed the calibre of engineers to accomplish such a task in 6 months, let alone communicate what is happening to the developer community. There is only one Ray Ozzie and one David Cutler, they cannot be cloned.

U-turn if you want to Microsoft, there is always Google's AppEngine for me.