Microsoft SQL Data Services (SDS)

1.    What is SDS?

Microsoft® SQL Data Services (SDS) offers highly scalable and Internet-facing distributed database services in the cloud for storing and processing relational queries. SDS can help you develop and provision new applications quickly with REST and SOAP based web protocols. The services are built on robust SQL Server database and Windows Server technologies, providing high availability and security.

2.   Why use SDS

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

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

 

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

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.

 

        public static void CreateAuthority(string userName, string password, string authorityName)
        {
            //create xml to represent the HTTP payload

            static XNamespace ns = "http://schemas.microsoft.com/sitka/2008/03/";
            var xml = new XElement(ns + "Authority",
                new XElement(ns + "Id", authorityName)
                );

             Console.WriteLine(xml);

         /************************

          <Authority xmlns="http://schemas.microsoft.com/sitka/2008/03/">
                 <Id>[authorityName]</Id>
          </Authority>

         ************************/

            string serviceUri="https://data.database.windows.net/v1/";

            var request = HttpWebRequest.Create(serviceUri);

            request.ContentType = "application/x-ssds+xml";
            request.Method = "POST";

            request.Credentials = new NetworkCredential(userName,password);
            /**********************************************************************
            string authToken = String.Format("{0}:{1}", config.Username, config.Password);
            byte[] credentialBuffer = new UTF8Encoding().GetBytes(authToken);
            request.Headers.Add(HttpRequestHeader.Authorization,
                                    "Basic " + Convert.ToBase64String(credentialBuffer));
             ****************************************************************************/
            var bytes = new UTF8Encoding().GetBytes(xml.ToString);

            using (var stream = request.GetRequestStream())
            {
                stream.Write(bytes, 0, bytes.Length);
            }

            var response = (HttpWebResponse)request.GetResponse();
            if (response.StatusCode == HttpStatusCode.Created)
            {
                Console.WriteLine("Success");
            }

        }

 

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.

public static void CreateContainer()
{
    //create xml to represent the HTTP payload
var xml = new XElement(ns + "Container",
        new XElement(ns + "Id", containerName)
        );
Console.WriteLine(xml);
            /***************************************
             <Container xmlns="http://schemas.microsoft.com/sitka/2008/03/">
                <Id>[containerName]</Id>
             </Container>
             * ************************************/

    //you post against the Authority to create a container

Uri uri=new Uri("https://[authorityName].data.database.windows.net/v1/");
var request = HttpWebRequest.Create(uri);

           request.ContentType = "application/x-ssds+xml";
            request.Method = "POST";

            request.Credentials = new NetworkCredential(Username,Password);         

            var bytes = new UTF8Encoding().GetBytes(xml.ToString);

            using (var stream = request.GetRequestStream())
            {
                stream.Write(bytes, 0, bytes.Length);
            }

            var response = (HttpWebResponse)request.GetResponse();
            if (response.StatusCode == HttpStatusCode.Created)
            {
                Console.WriteLine("Success");
            }

}

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.

 

        private static void CreateEntities()
        {
            XElement taskA = new XElement("Task",
                    new XAttribute(XNamespace.Xmlns + "s", Constants.ns),
                    new XAttribute(XNamespace.Xmlns + "x", Constants.x),
                    new XAttribute(XNamespace.Xmlns + "xsi", Constants.xsi),
                    new XElement(Constants.ns + "Id", "task_a"),
            AddProperty<String>("Subject", "Task A"),
            AddProperty<DateTime>("StartDate", DateTime.Now.AddDays(-20)),
            AddProperty<DateTime>("DueDate", DateTime.Now.AddDays(-10)),
            AddProperty<String>("Status", "Finished"),
            AddProperty<String>("Priority", "Normal"),
            AddProperty<bool>("IsComplete", false),
            AddProperty<int>("PercentComplete", 0)
                );

   //you post against the Authority to create a container

Uri uri=new Uri("https://[authorityName].data.database.windows.net/v1/[containerName]");
var request = HttpWebRequest.Create(uri);

           request.ContentType = "application/x-ssds+xml";
            request.Method = "POST";

            request.Credentials = new NetworkCredential(Username,Password);         

            var bytes = new UTF8Encoding().GetBytes(taskA.ToString());

            using (var stream = request.GetRequestStream())
            {
                stream.Write(bytes, 0, bytes.Length);
            }

            var response = (HttpWebResponse)request.GetResponse();
            if (response.StatusCode == HttpStatusCode.Created)
            {
                Console.WriteLine("Success");
            }

        }

 

        private static XElement AddProperty<T>(string propName, T instance)
        {
            string propVal = null;
            string propType = null;

            switch (Type.GetTypeCode(typeof(T)))
            {
                case TypeCode.Boolean:
                    propVal = instance.ToString().ToLower();
                    propType = "x:boolean";
                    break;
                case TypeCode.DateTime:
                    propVal = ((DateTime)Convert.ChangeType(instance, typeof(DateTime))).ToUniversalTime().ToString("u");
                    propType = "x:dateTime";
                    break;
                case TypeCode.Object:
                    if (instance as byte[] != null)
                        propVal = Convert.ToBase64String((byte[])Convert.ChangeType(instance, typeof(byte[])));
                    propType = "x:base64Binary";
                    break;
                case TypeCode.Int32:
                    propVal = instance.ToString();
                    propType = "x:decimal";
                    break;
                default:
                    propVal = instance.ToString();
                    propType = "x:string";
                    break;
            }

            return new XElement(propName,
                new XAttribute(Constants.xsi + "type", propType),
                propVal
                );
        }

4.   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.