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.

SQL Data Services Getting Started

In my last post I talked about Windows Azure. One of the components of Azure is SQL Data Services (SDS), formally known as SQL Server Data Services (SSDS), which is Microsoft’s “database in the cloud”. If you want to start learning about Azure, SDS is a good place to start since you can use it without having to setup a full Azure development environment. In this posting I will discuss how to get setup to work with SDS.

The first step is to sign up for Azure Service Platform invitation codes, you can do that on this page http://www.microsoft.com/azure/register.mspx. Microsoft is trying to regulate how many developers get on the service so you may not be able to access all the parts of Azure immediately. Once you have applied you will receive a series of e-mails with the codes. The e-mails come in pairs, the first one gives you the code and the second lets you know that is has been activated. There are three different codes and you probably won’t get them all at the same time. The one needed to access SDS is the “Microsoft .NET Services and Microsoft SQL Services” code. I received my code for this service within 24 hours of signing up, but it may take longer. At the time of this writing I haven’t received codes for any of the other services.

Once you receive the code you will then have to sign up for the actual service. In the activation confirmation e-mail there will be a link to the page where you can enter the invitation code. At this point you will be asked to create a new solution. You just have to provide a name for the solution which will also become the username for logging into the service. You can only have one solution per invitation code. Once you have created the solution you will be provided a password for that solution.

The final step in setting up for SDS is to download and install the SDS SDK. Unlike the Azure SDK which requires Vista or Server 2008, the SDS SDK works under XP and even Windows 2000.

Once you have the SDK installed you can test things out. In the SDK folder on the Start Menu you will find a tool called SSDS explorer. When you start the tool you should see “https://data.database.windows.net/v1/” in the address bar and “from e in entities select e” in the query box. Click the Query button and a box will pop up allowing you to enter your username and password. The username is the name of the solution you created and the password is the one you received when you created the solution. The query should run without returning any errors.

Now we are finally ready to start working with SDS. I will start getting into the details of how SDS works in my next posting.