SDS Data Model Overview (Authorities, Containers, Entities and Flexible Entities)

Article
06/24/2009

[This document supports a preliminary release of a software product that may be changed substantially prior to final commercial release. This document is provided for informational purposes only.]

Business Challenges

Organizations are seeing a confluence of factors stretching IT organizations. These factors include massive increase in digital data, the need to process transactions and to support random queries over this vast amount of data, and the explosion of Internet traffic. IT organizations have to deal with issues such as the following:

Running out of capacity
Needing more servers
Needing more rack space
Operating the servers
Planning for future needs

Providing extra capacity and getting the service up and running to the desired level takes time. The cost of maintaining infrastructure goes up, as well. IT organizations often use standby capacity as needed, adding further to IT costs. Although hardware costs show a downward trend, the amount of digital data is increasing dramatically, which requires more hardware and storage. Significant increases in the amount of software and maintenance are also adding to costs.

Microsoft SQL Data Services (SDS)

As the market place evolved, so did the Microsoft® data platform. The platform provided server-based offerings for all kinds of data, including simple blobs, relational structured data, and XML. Today the platform also offers a rich feature set, including query services, reporting services, and synchronization services. With the marketplace evolution, data services are now added to this picture.

Cc512402.c9ec2f9a-2147-4b2a-a749-eec627869c45(en-us,SQL.100).gif

With the introduction of Microsoft® SQL Data Services (SDS), data platform customers can store data in cloud, complementing our on-premises offering.

SDS Key features and benefits

The core value proposition of SDS is providing data as utility service. Your data is available at anywhere, any time. The service delivers the following key benefits:

Application agility
- Easy-to-use service
- Quicker application development
- Industry standard and well-known protocol support (REST and SOAP)
Scalability
- Scales as you grow
- Capacity you need will always be there
- No wait time for server provisioning
- Pay as you grow
Business-ready SLA
- Built on robust Microsoft SQL Server and Windows technologies
- Highly reliable and available

SDS Overview

SDS offers a simple, easy-to-understand data model. SDS is a Web 2.0–friendly data service that supports open wire formats such as SOAP and REST. A quick SDS overview below covers the following topics:

Data model
Service interface
URI space and query support
Security model

Data Model

The data model includes the concepts of authority, container, and entity, with flexible schema, a core concept in SDS. The following topics explain the data model concepts:

Three-Level Containment Model (the "ACE" Concept)
Homogeneous vs. Heterogeneous Containment
Flexible Entity

Three-Level Containment Model (the "ACE" concept)

The SDS data model offers a three-level containment model that includes authorities, containers and entities.

Authority

At the top level of this containment hierarchy is an authority. After you sign up for the data service, you begin by creating an authority. An authority is represented by a DNS name. For example:

mydomain.data.database.windows.net

Where mydomain is an authority and data.database.windows.net refers to the service. You can own more than one authority. Additional authorities can be created at any time. The DNS name of an authority resolves to an IP address that maps to a specific data center. That is, an authority and the data beneath it are located in a single data center. An authority, then, is the unit of geo-location.

Container

An authority is a collection of containers. Each authority can have zero or more containers stored within it. Each container has a unique id within an authority. A container stores data (entities). You create a container and then immediately begin storing entities of various kinds without having to worry about any container schema.

A container can store zero or more entities, but nested containers (containers within a container) are not supported.

A container is also the largest domain for search and update. In this beta release, cross-container queries are not supported.

Because each authority is in one geo-location, all containers in an authority are located in the same data center.

Entity

Each entity inside a container can store any number of user-defined properties and corresponding values.

An entity is the smallest object that can be updated, that is, you can retrieve an entire entity; add, update, delete properties; and then replace the original entity with the updated one. Partial updates are not supported. The entities can be blob or non blob entities. In this release the blob entities can only have metadata properties. Flexible properties on blobs are not allowed.

Note

A container can store zero or more entities, but nested containers (containers within a container) are not supported.

This hierarchical structure of authorities, containers, and entities forms a three-level containment model. This containment model is illustrated in the diagram below:

Cc512402.b9e6d15c-c026-4eca-8413-6aaa3d71885d(en-us,SQL.100).gif

Homogeneous vs. Heterogeneous Containment

Depending on your application needs, you container can be a homogeneous or heterogeneous.

In the homogeneous model, a container stores entities of the same kind. In this model, a container is like a database table. Some of the examples of homogeneous containers are as follows:

In an Employee table, you store employee records. These are all records of the same kind. In SDS, you would create an Employee container that stores Employee entities. These entities can have different properties, but they are all entities of Employee kind.
In a sample authority, books-docsamples (DNS name books-docsamples.data.database.windows.net), you can create containers such as ChildrensBooks and TechnicalBooks. You then store book entities in appropriate containers.

In a heterogeneous containment model, a container is like a database that stores entities of all kinds. For example, a database can have Orders, Employees, and OrderDetails tables. In SDS, you can create a single container to store orders, employees, and order details entities. The advantage in this case is that you can query across heterogeneous entities within the same container.

Flexible Entity

In SDS, the flexible entity is a key concept. Each of these entities is a set of metadata properties and a bag of flexible properties.

You can think of flexible entities as sparse tables in the relational data model or an XML fragment with one element per property value. Each property has a name and a value. The value can only be a simple scalar type. The simple scalar types supported in this release are string, binary, Boolean, decimal, and datetime.

string
binary
Boolean
decimal
dateTime

Whether you use the REST or SOAP interface discussed below, the XML payload format uses the following standard XSD types to encode the scalar values:

string
base64Binary
boolean
decimal
dateTime

Metadata Properties

Each flexible entity carries a fixed set of properties (Id, Version, and Kind) A blob entity has additional Content property. These are called metadata properties. The Id property uniquely identifies a flexible entity. The Version property acts like a timestamp. The system assigns value for the Version property when an entity is created. For each update, a new version is automatically assigned. The Kind property value is user-defined and identifies the entity type. The Content blob property describes the blob.

Flexible Properties

In addition to the metadata properties, an entity can also have zero or more additional flexible properties. These flexible properties can have any name and any scalar value of one of the above scalar types.

NOTE In the current implementation, there are limits to using the flexible and the metadata properties. For details, see Guidelines and Limitations.

Service Interface (API)

SDS is a Web 2.0–friendly data service. It supports both the SOAP and REST interfaces so that developers can use almost any language or Web development environment.

The URI Space and Query Support

Query support allows querying for the flexible entities. There are two parts to understanding the query support:

The URI Space
Query Support

The URI Space

When you program with SDS using REST interface you work with four kinds of URIs. These URI types are described in the following table.

URI type	Description
Service	Used while creating and querying authorities.
Authority	Used while creating and querying containers. In addition, the authority URI is used to retrieve metadata regarding a particular authority.
Container	Used while creating and querying entities. In addition, the container URI is used to retrieve metadata regarding a particular container or to delete a particular container.
Entity	Used while retrieving, updating, and deleting a particular entity.

You can think of these URIs as setting a scope over which a set of operations can be performed. URI examples are given in the following sections.

Service URI

In the following URI, data.database.windows.net refer to the service and v1 refers to version of service interface to be used. For this beta release, the value of version must always be v1.

https://data.database.windows.net/v1/

You can optionally append a query to the service URI to query for authorities you created.

https://data.database.windows.net/v1/q='<your query>'

Authority URI

An authority URI is the service URI preceded by the authority id.

https://<authority_id>.data.database.windows.net/v1/

The authority URI fetches a single entity containing the authority metadata (id, version). A sample authority metadata is shown below:

<s:Authority xmlns:s="https://schemas.microsoft.com/sitka/2008/03/" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
             xmlns:x="http://www.w3.org/2001/XMLSchema">
  <s:Id>books-docsamples</s:Id>
  <s:Version>2234</s:Version>
</s:Authority>

You can optionally append a query to the authority URI in which you are querying for containers in it.

https://<authority_id>.data.database.windows.net/v1/q='<your query>'

An empty query string returns metadata for all containers within the specified authority. For details about queries, see Querying SQL Data Services.

Container URI

A container URI is the authority URI followed by a container id.

https://<authority_id>.data.database.windows.net/v1/<container_id>

The container URI fetches a single entity that conatins container metadata. A sample container metadata is shown below:

<s:Container xmlns:s="https://schemas.microsoft.com/sitka/2008/03/" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
             xmlns:x="http://www.w3.org/2001/XMLSchema">
  <s:Id>UsedBooks</s:Id>
  <s:Version>1234</s:Version>
</s:Container>

A container URI followed by a query returns entities that meet the query criteria from the specified container.

https://<authority_id>.data.database.windows.net/v1/<container_id>?q='<your query>'

Entity URI

An entity URI is a container URI followed by entity id. It returns a specific entity, as shown in the following example.

https://<authority_id>.data.database.windows.net/v1/<container_id>/<entity_id>

The result includes all the metadata properties (id, version, and kind) and any user-defined flexible properties. A sample book entity is shown below:

<UsedBookKind xmlns:s="https://schemas.microsoft.com/sitka/2008/03/"        
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
              xmlns:x="http://www.w3.org/2001/XMLSchema">
  <s:Id>MySampleBook</s:Id>
  <s:Version>13453</s:Version>
  <Title xsi:type="x:string">My Book</Title>
  <ISBN xsi:type="x:string">1-57880-066-36</ISBN>
  <Author xsi:type="x:string">Mr. Author</Author>
  <Publisher xsi:type="x:string">Mr. Publisher</Publisher>
  <InPrint xsi:type="x:boolean">false</InPrint>
  <NumberOfCopiesSold xsi:type="x:decimal">250</NumberOfCopiesSold>
  <PublicationDate xsi:type="x:dateTime">2004-01-27T00:00:00</PublicationDate>
  <CoverPhoto xsi:type="x:base64Binary">AQID</CoverPhoto>
</UsedBookKind>

Query Support

SDS supports simple text based queries. For more information, see Querying SQL Data Services.

Security Model

In this implementation, every authority has a single owner (single user name and password). Once authorized to a specific authority end point, you get full control over the authority and the data stored within it. The accounts are secured by SDS issued user credentials. The service allows only https for applications to communicate with the service in a more secure manner.