SDS Data Model

SQL Data Services is often referred to as “SQL Server in the cloud”. Despite this designation it actually operates quite a bit differently then a traditional relational database. In this post I will describe the SDS data model. There are three parts to the data model, Authorities, Containers and Entities.

Authorities are the highest level of organization in SDS. An authority corresponds to a specific SQL Server instance in one of Microsoft’s data centers. The authority name will be the first part of the DNS address used to access SDS.

The next lower level of the hierarchy is a container. Depending on how it’s used a container is akin to either an entire database, or a single table in a database. We will talk more about that when we get to Entities. You can have multiple containers in a single authority, and containers can contain zero or more entities, but cannot contain other containers. At present queries are restricted to a single container, you cannot query across multiple containers.

The lowest level in the hierarchy is the entity where your actual data is stored. Entities are akin to records in a traditional database, but unlike traditional databases SDS doesn’t use schemas. Each entity you create in a container can potentially have a different set of fields (called properties in SDS). If you choose to have every entity in a container have the same properties, then the container behaves like a table, but if you mix different entities in a container then it’s behaving more like a complete database. This is one of the areas where SDS diverges quite a bit from the operation of a traditional relational database.

There are two types of entities in SDS, non-blob which you will use most often, and blob entities used to store binary objects. We will just talk about non-blob entities here. Each entity contains a series of properties of which there are two types, metadata properties and flexible properties.

There are three predefined metadata properties. The first is ID which must contain a unique value for each entity in a container. This is a string value that can have up to 64 characters. The second property is a numeric value called Version. Version is automatically assigned by the server when the entity is created and a new version number is assigned each time the entity is updated. Version can be used to handle optimistic concurrency. The final metadata property is a Kind. Kind is an optional string value that can be used to identify the type of each entity. For example if you were doing an order entry system you could have kinds like “Invoice”, “Sales Order”, etc.

Finally an entity can have zero or more flexible properties. These properties contain the actual data that you want to store in the entity. Each property will have one of the following data types, string, binary, boolean, decimal or dateTime.

In my next post we will start working with the SSDS Explorer tool.