In discussions with ISV’s recently it’s become apparent that a lot of people are interested in investigating the Azure Services but it’s finding the time to get started. It’s a big topic and there are lots of sources of information around already. What I thought I try to do is provide a few pointers that would get someone started as quickly as possible. In this article I’m going to focus on the SQL Data Services (SDS) portion of the platform.
Let’s begin with a quick overview. It’s a Cloud based data platform that runs across multiple servers. The great thing about the way SDS operates is I don’t need to worry about machine boundaries. All I do is place my data within SDS and then access it using REST or SOAP based protocols. In order to do this I do need to understand a little about how my data is organised but we’ll cover that later in this article.
So to begin with we need to get an account. If you just want to investigate the SQL Data Services then you can register here. However as far as I can see that requires a US credit card as a form of ID so it may be easier for UK ISV’s to register for the entire Azure Platform here. When you go to the site, click on the Register for Services link on the main body of the page. The link should take you to Microsoft Connect where you will be invited to join the Azure program. If you are not already a member of the connect program then you will need to register using your passport account and answer some basic questions. Once you’ve registered you should see the following screen
Once you receive the invitation code via email you can sign up. Having talked to a few people the sign up steps can be a bit confusing. Jenny Lo has an excellent post in her blog here that details all the steps you need to take to successfully complete the signup. If you want to understand more fully the differences wrt. SDS between the two signups then Jenny has also posted a bog entry here .
Now that you have a functioning account you need to understand the basic ACE concept i.e. Authority, Container and Entity within SQL Data Services. It’s easier to discuss these in reverse order
A Entity is a collection of properties. It most closely matches the concept of a record in SQL Server terminology. However unlike SQL Server these entities have no schema associate with them and therefore these is no requirement for any two entities to have the same structure. Each entity also has a few pieces of metadata associated with it. One of these is the KIND property. Using this property, different entities can be tagged with the same label, and therefore processed as a group. So the kind property can be used to define groupings that are loosely analogous to tables.
These entities are defined within the scope of a Container. So a container is loosely analogous to a database. A container defines the scope of a query and currently has a 100Mb limit for non-blob entities. A user can define multiple containers within a single Authority. A user’s containers are not necessarily placed on the same physical machine but can be located anywhere within the server farm. By both keeping redundant copies of containers and spreading them across multiple machines SDS provides both scalability and availability. There is a good explanation of this in the PDC presentations see https://sessions.microsoftpdc.com/public/timeline.aspx and search for presentations BB03 and BB14.
An Authority is defined the geographical location of the data and the unit of billing. An authority can include multiple containers. For completeness, its worth mentioning that the account that you’ve just created can have multiple Authorities.
There are a number of places that provide a fuller explanation of the ACE concept a good starting point are the whitepapers Getting Started with Microsoft SQL Data Services – A Developer’s Focus and Microsoft SQL Data Services – Under the Hood.
Once signed up my recommendation would be to download and install the SDS SDK here. Once installed run SDS Explorer the documentation is located here. Now we can create out first Authority, Contain and Entities. Begin by clicking Authority and it will add a template where you simply enter the name of your authority eg “authorityexample”. Clicking on the Post button will create the authority. Note you will be prompted for your username and password. The username is the name of the solution you created during the signup.
Similarly clicking on the container template with the address as in the screenshot below will create the container
Finally the screen below shows the syntax to add a customer record from the northwind database. You can build this up by clicking on the appropriate prop buttons. Again click on post to submit.
So there are two ways to access SDS, either via the REST or the SOAP endpoints. Using the REST interface it’s possible to query the data we’ve just added from a URI. So for example putting the following into IE will obtain a list of authorities (and associated information) for the user
The following would retrieve a list of containers for the authorityexample authority
And the following would retrieve all the entities for the containerexample container
We could select just the customer entities where the city was Berlin and order them in ascending order of company using the following
https://authorityexample.data.database.windows.net/v1/containerexample?q='from c in entities.OfKind("Customers") where c["City"]=="Berlin" orderby c["CompanyName"] select c'
Note that by default IE does not know how to deal with a content type of “application/x-ssds+xml”. The easiest way to fix this is to install SDS SDK. If you did this previously it will have updated the mime associations for you. If not then a quick web search will provide the information you need to manually update the association in the registry.
By now you will probably be itching to delve into code and start to put something together yourself. Start by having a look at some basic example of using the REST and SOAP interfaces from code that are located here. These will illustrate how to programmatically create delete and update the various ACE components.
So we’ve only scratched the surface of this topic but hopefully I’ve given you some
pointers on how to get started. There are a lot of other resources available. For example there are a set of data connectors available for SQL Server Integration Services so you can create SSIS packages move data from SQL Server to SDS or vice versa. See the Codeplex project here. There is also work taking place to provide an ADO.NET Data Services interface for SDS and you can find a preview here . There is also a prototype of Reporting Services working on SDS located here. I’ve even found a Ruby library for SDS located at http://www.azurejournal.com/2008/10/ruby-and-sql-data-services-sdk/
So to finish for now here are some further references worth a look.
SQL Services labs: http://www.microsoft.com/azure/sqllabs.mspx This is a good place to look for futures projects related to SQL Data Services. I’m particularly fond of the cloud data mining bits.
MSDN Developer Centre: http://msdn.microsoft.com/en-us/sqlserver/dataservices/default.aspx
Getting started forum. http://social.msdn.microsoft.com/forums/en-US/ssdsgetstarted/threads/. There is a great set of SDS discussion threads here.
PDC 2008 content on SQL Data Services https://sessions.microsoftpdc.com/public/timeline.aspx There are some interesting session available to watch here. Once you understand the basics I’d especially recommend: BB14 SQL Services: Futures