Part 1: SQL Data Services – An Introduction


Recently, SQL Data Services (SDS) were published as a Community Technical Preview (CTP).

Registering via Microsoft Connect, I’ve been able to start building a sample application which is designed to make use of SDS for online real-time queries.

This blog entry forms part of the [MiniProject] documentation – the Mini Project uses SDS for online storage and SQL Server Compact Edition 3.5 (SP 1) for offline storage.

SDS Overview

As part of Microsoft’s Cloud Computing strategy, SQL Data Services are envisioned to provide lightweight data storage and services for Internet-enabled applications.

Predominantly, SDS utilizes three different data access methods (Browser, REST and SOAP) and incorporates a tri-level data containment model (the “ACE” concept) comprising of an Authority, a Container and an Entity.

Naturally, an Authority can contain multiple containers, and a container can contain multiple entities.

SDS_ACE

Source: SDS Reference

Getting Started with SDS

The first step is to register for SQL Data Services.

You need a Windows Live ID account, and need to register with Microsoft Connect.

connect1

Once you’ve set both up (and signed in to Windows Live), a simple visit to this link will allow you to register for the SDS CTP.

connect2

Once you get emailed an invitation token (or obtain one from elsewhere) you’re ready to start working with SQL Data Services.

Notice, once you’ve registered in Windows Connect, a project is added to your Connect Dashboard, as below.

connect3

Browse to http://portal.ex.azure.microsoft.com/ where you will be prompted for your invitation code (which you most likely will receive via email).

SDS1

Once you have entered your code you will land on the getting started page.  Click the “Create a New Solution” button to get started.

sds2

Understanding SDS Data Containers

image

Authority

An Authority is a top level container used to uniquely identify a service authority.  It is also a DNS name, and forms the base of a URI (e.g. https://my-authoprity.data.database.windows.net).

In the world of SDS, an Authority is created by a solution and a solution is created by a registered user (Windows Live Account).

Container

A container can be thought of as a Table (as in the traditional database world) or as a grouping (a container which holds different entities, unlike a traditional database table).

In other words, you may use a container to only hold one type of entity (but use different containers to store different entities, like in today’s databases) or you can put many different kinds of entities into a container, it’s entirely up to you.

Important note: at present, cross-container joins are not supported, so if you plan to write complex queries, or to link different entities together, you’ll pretty much have to put all those entities into the same container.

Design note: It’s important to know that entities can not share Id values within the same container.  See more in the section below regarding Entities.

Entities

Stored within a container, an entity has a basic definition which contains certain common properties.  An entity can also be used to store a Dictionary-like set of key/value pairs, allowing an entity to store more specific information, according to your own definition.

Common Properties (Metadata Properties)

  • Id (unique within the container),
  • Kind (string defining the user defined entity type), and,
  • Version (used for concurrency).

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 scalar types listed below.

Supported Data Types (v1.0)

  • string
  • binary
  • Boolean
  • decimal
  • dateTime

    Limitations on data storage are listed here.

    Web Browser Access

    Authorities can be accessed via a web browser using the following syntax:
    https://<authority_id>.data.database.windows.net/v1/

    Once authenticated (if using Basic Authentication, this is the solution name accompanied by the solution password), the browser will render the following XML:

    <s:Authority xmlns:s="http://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>[Authority Name]</s:Id>
      <s:Version>XXX</s:Version>
    </s:Authority>

    Summary

    This article should get you up and running, and give you a basic knowledge of SQL Data Services.

    Check back for the second part, which will introduce you to the concepts behind getting data into SDS and more advanced topics.

    Next steps:

    • Create a container
    • Create entities
    • Query entities
  • As always, you can email me at rob.sanders@gmail.com if you have any pressing questions.


    About Rob Sanders

    IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

    Leave a comment

    Your email address will not be published. Required fields are marked *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>