Tech Brief

SQL Server Data Services

Data storage in the cloud.

Provisioning Internet-facing data access is vital to the success of the Web-based services hosted by Inc., Google Inc. and Microsoft. Renting cloud-based data services on a pay-as-you-go basis eliminates capital investment in extremely costly on-premises data centers and the recurring personnel costs for database maintenance, backup and restoration. The major players in this fledgling utility data-store market already own large-scale, globally dispersed data centers or, in Microsoft's case, are currently in the build-out process. Adding incremental capacity to handle on-demand customers' traffic surges is far less costly than constructing data centers from ground zero. Data store provisioning is automated, and clustering plus replication or its equivalent reduces maintenance to infrequent server replacement.

SQL Server Data Services (SSDS), which the SQL Server team announced in March, is one of several business-oriented Microsoft Online Services in the beta-testing stage. Like the other two queryable, hosted-data stores in beta-test today -- Amazon SimpleDB and Google App Engine (GAE) -- SSDS doesn't expose the related tables, columns and rows of conventional relational databases. Instead, all three data stores employ varying flavors of the entity-attribute-value (EAV) model. What's more, SSDS and SimpleDB don't support schemas -- though Microsoft has announced plans to support schemas in a future SSDS update. Entities have a rough correspondence to relational tables, attributes to columns, items to rows and values to cells. Microsoft uses the terms "flexible entities" and "flexible property bags" to distinguish SSDS constructs from conventional entity-relationship (E-R) terminology.

EAV Benefits
The EAV model simplifies entity metadata and lets developers create new entities without resorting to SQL's Data Definition Language (DDL). Another feature of the EAV model is the ability to add attributes on the fly, which is highly valuable for storage of medical records, drug tests and other semi-structured data with schemas that are difficult to predict accurately. SSDS has far fewer data types than SQL Server 2005, which is the service's database engine; SSDS offers only string, decimal, datetime, boolean and base64Binary scalar types. SSDS entities require a unique Id attribute value to identify an item, an optional Kind string attribute to specify its usage and a timestamp Version attribute. SSDS has no notion of JOINs, foreign keys or other relational niceties. But SSDS does implement concurrency-conflict detection by conditional updates, based on a combination of the Version attribute value and If-Match or If-None-Match headers for Representational State Transfer (REST) PUT operations, or ETags with the VersionMatch property of a SOAP message.

Maximum size of a conventional SSDS entity item is 2MB, which corresponds with the maximum length of a request/response message. Query responses of more than 500 items require paging. There's no limit to the number of attributes, provided that the total length of all attributes doesn't exceed 2MB. Blob is a recently introduced entity type that accepts streams of valid Multipurpose Internet Mail Extensions (MIME) content-types up to 100MB in size (in the current version at press time). SSDS automatically indexes scalar data types and will provide full-text search (FTS) shortly; the back-end currently supports FTS and the SSDS team is working on the FTS Web query interface. An ascending sort on Id (key) values is automatic, but SSDS doesn't support ORDER BY operations on other entities.

SSDS consists of three data-access layers over racks of commodity servers running clusters of custom SQL Server 2005 instances -- authorities, containers and entities, which the SSDS team abbreviates as ACE. Containers store one or more Entity sets and are the basic unit of data consistency and the boundary for queries. Authorities are units of service address, accounting, geo-scale and geo-location at a specific Microsoft data center. An authority corresponds approximately to a shared, clustered SQL Server instance that stores multiple data copies for reliability; data replicated between authorities at different geo-locations provides disaster protection. Containers store one or more Entity sets; they are the basic unit of data consistency and the boundary for queries. Future upgrades will support asynchronous fan-out queries over multiple containers. Containers have a maximum size of 2GB for conventional entities, but don't have a size limit for entities containing blobs.

Microsoft Foundation Class Active container support integrates the Office 2007 drawing features for the Ribbon Bar.
[click image for larger view]
SQL Server 2005 Customized Instances Running on Commodity Servers in Data Centers

Web-oriented architecture favors REST to transport entities and metadata over HTTP without involving a SOAP message layer. A Web-facing programming interface layer, which the SSDS calls the "Head," supports fully REST-compliant queries with GET statements to return entities based on their universal resource identifier (URI). A client API converts a string-based LINQ expression into GET query strings. POST (insert), PUT (update) and DELETE verbs combined with XML templates handle data manipulation. Secure Sockets Layer (SSL) encryption is optional for REST requests but required for SOAP messages. A program to converge the Head's transport protocols with ADO.NET Data Services' JavaScript Object Notation (JSON) and the Atom Publication Protocol (AtomPub or APP) is underway now.

SSDS is the most enterprise-oriented of the three primary cloud data stores, though SSDS doesn't implement role-based security yet. The SSDS team currently upgrades the back-end services with bimonthly "sprints." Sprint 3 rolled out on July 24, so Sprint 4 -- implementing features needed for the Professional Developers Conference 2008 -- should be underway at the end of September. The team released the SSDS software development kit, which consists of command-line and Windows-form client tools, in August.

About the Author

Roger Jennings is an independent XML Web services and database developer and writer. His latest books include "Special Edition Using Microsoft Office Access 2007" (QUE Books, 2007) and "Expert One-on-One Visual Basic 2005 Database Programming" (WROX/Wiley, 2005). He’s also a VSM contributing editor and online columnist and manages the OakLeaf Systems blog. Jennings’ Code of Federal Regulations Web services won Microsoft’s 2002 .NET Best Horizontal Solution Award. Reach him at

comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.