August 2009

Volume 24 Number 08

SQL Data Services - The Relational Database of the Azure Services Platform

By David Robinson | August 2009

.SidebarContainerA { width: 300px; height: 170px; float: right; border: 2px solid #008080; padding: 10px 20px 0px 20px; background-color: #e1e8f5; margin: 20px 0px 20px 10px; font-family: Verdana, Arial; font-size: 12px; padding-bottom: 7px; line-height: 16px; color: #333333; } .SidebarContainerB { width: 300px; height:290px; float: right; border: 2px solid #008080; padding: 10px 20px 0px 20px; background-color: #e1e8f5; margin: 20px 0px 20px 10px; font-family: Verdana, Arial; font-size: 12px; padding-bottom: 7px; line-height: 16px; color: #333333; } .imgleft { float: left; margin: 5px 0px 10px 15px; width:250px; font-weight: normal; font-size: 14px; color: #003399; font-family: "Segoe UI", Arial; } .imgright { float: left; margin: 5px 15px 10px 30px; width:200px; font-weight: normal; font-size: 14px; color: #003399; font-family: "Segoe UI", Arial; }

This article is based on a prerelease version of SQL Data Services. All information herein is subject to change.

Contents

Extending the SQL Data Platform to the Cloud
SDS Architecture
SQL Data Services Front End
SQL Data Services Back End
SQL Data Services Fabric
Connecting to SQL Data Services
Building Applications that Consume SQL Data Services
New Face of SQL Data Services

In March of 2008 at the annual MIX conference, Microsoft announced SQL Data Services (SDS), its first data store for the cloud. SDS was an Entity-Attribute-Value (EAV) store that could be accessed using industry standard Internet protocols. It included all the features you would expect from a cloud-based offering, including high availability, fault tolerance, and disaster recovery; all powered by the Microsoft SQL Server engine. Though the initial data model was EAV-based, the more relational features promised at MIX began to be delivered at the Professional Developers Conference in October 2008.

Over the months that followed, the SDS team gathered essential feedback from the user community, most importantly that while the current SDS offering provided a valuable data storage utility, it wasn't SQL Server. What customers wanted was a relational database offered as a service. In March 2009, the SQL Server team announced it was accelerating its plans to offer exactly that, and this was met by overwhelmingly positive feedback from the community. Microsoft has always provided a comprehensive data platform and the new relational capabilities of SDS continue that tradition. With SDS, Microsoft SQL Server now extends from handheld devices with SQL Server CE, to the desktop with SQL Server Express, to the enterprise with SQL Server (both standard and enterprise editions), and now, to the cloud. SDS is the relational database of the Azure Services Platform.

The TDS Protocol

The native protocol used by clients to communicate with Microsoft SQL Server is called Tabular Data Stream, or TDS. TDS is a well-documented protocol that is used by the underlying Microsoft client components to exchange data with the SQL Server engine. There are even General Public License (GPL) implementations of TDS that can be found on thse Internet.

Extending the SQL Data Platform to the Cloud

SDS is the relational database of the Azure Services Platform in the same way that SQL Server is the database of the Windows Server Platform. In the initial offering, only the core relational database features are provided. The research that the product team has done shows that the current feature set addresses about 95 percent of Web and departmental workloads. When you look at the SQL Server brand, the database engine is only one piece of a larger suite of products. Since SDS uses the same network protocol as the on-premises SQL Server product, all the existing ancillary products continue to work. But though the products will function, they must be run on-premises on your own network. The SQL Server team plans to enable the rest of the SQL Server stack, in the future, to function in the cloud. The end result will be a consistent development experience, whether your solution targets Windows Server or Azure. In fact, the same code will continue to work. All that will be required is a connection string change.

SDS Architecture

As mentioned earlier, SDS provides a SQL Server database as a utility service. Features like high availability, fault tolerance and disaster recovery are built in. Figure 1 provides a view of the SDS architecture. Let's take a look.

Figure 1 SQL Data Services Architecture

SQL Data Services Front End

The SDS front-end servers are the Internet-facing machines that expose the TDS protocol over port 1433. In addition to acting as the gateway to the service, these servers also provide some necessary customer features, such as account provisioning, billing, and usage monitoring. Most importantly, the servers are in charge of routing requests to the appropriate back-end server. SDS maintains a directory that keeps track of where on the SDS back-end servers your primary data and all the backup replicas are located. When you connect to SDS, the front end looks in the directory to see where your database is located and forwards the request to that specific back-end node.

Supported Features

In version 1, SDS will support

• Tables, indexes and views
• Stored procedures
• Triggers
• Constraints
• Table variables, session temp tables (#t)

The following are out of scope for SDS v1

• Distributed transactions
• Distributed query
• CLR
• Service Broker
• Spatial data types
• Physical server or catalog DDL and views

SQL Data Services Back End

The SDS back-end servers, or data nodes, are where the SQL Server engine lives, and it is in charge of providing all the relational database services that an application will consume. The product team is often asked why SDS provides only a subset of the features found in the on-premises SQL Server product. The reason for this is that the feature surface area of SQL Server is extremely large. A significant amount of engineering and testing goes into each feature area that is exposed in SDS, to ensure that the feature is hardened and that a customer's data is completely siloed from all the other SDS customer data. By providing the core relational features that address 95 percent of Web and departmental applications, the team could get the product to market sooner. And, because SDS is an Internet service, we are able to be much more agile and provide new features at a faster pace. Over time, you can expect to see most of the features in the on-premises product available in SDS.

The SDS back end receives the TDS connection from the front end and processes all CRUD (Create, Retrieve, Update, Delete) operations. What features are currently supported? Everything you have come to expect from a relational database, as listed in "Supported Features."

SQL Data Services Fabric

The SDS fabric is in charge of maintaining the fault tolerance and high availability of the system. The fabric plays a key role in the SDS system of automatic failure detection, self-healing and load balancing across all the SDS back-end data nodes. Earlier on, we discussed how SDS maintains a primary copy of your data as well as a series of backup replicas. The fabric provides SDS automatic failure detection. If the node where the primary copy of your data exists experiences a failure, the fabric automatically promotes one of the backup replicas to primary and reroutes the requests. Once the Fabric sees that the failover has occurred, it automatically rebuilds the backup replica in case another failure should occur.

Connecting to SQL Data Services

This is the part of the article where the SDS team hopes I put you to sleep. The fact of the matter is that because SDS exposes the TDS protocol, all the existing clients like ADO.Net and ODBC just work. Take, for example, the following ADO.Net connection string:

SqlConnection conn = new SqlConnection("Data Source=testserver; Database=northwind; encrypt=true; User ID=david; Password=M5DNR0ck5");

To connect to SDS, that string would look like this:

SqlConnection conn = new SqlConnection("Data Source=testserver.database.windows.net; Database=northwind; encrypt=true; User ID=david; Password=M5DNR0ck5");

All that's changed is where the server is located. Note that the string includes the optional parameter encrypt=true. This parameter is not optional for SDS, which requires that all communication be over an encrypted SSL channel. If you try to connect without encryption, the SDS front end will terminate the connection. Because of the TDS protocol, all your existing knowledge, tools and techniques developing against SQL Server still apply. The only thing you need to be concerned about is where your application will run and its proximity to the data.

Building Applications that Consume SQL Data Services

As previously mentioned, one of the main things you need to be concerned with when storing data in SDS is where your application code will run—whether your application follows a "Code Near" architecture or a "Code Far" architecture.

Code Near A Code Near application typically means that your data and your data access components are located on the same network segment, for example when you have your application running on your corporate network. In the case of the Azure Services Platform, it would mean having your application running in Azure and your data residing in SDS. When the Azure platform goes live later this year, you will have the option of picking the region where your application will be hosted as well as the region where your data will be hosted. As long as you choose the same region for both, your application code will be accessing data within the same datacenter, as shown in Figure 2.

Figure 2 Code Near Application

Code Far When your application is a Code Far application, this typically means having your data and data access components on separate networks as shown in Figure 3, often with the Internet in between. The Internet has been an incredible enabler for business and technology, but from a data-access perspective, it does pose some interesting challenges, depending on your application and its architecture.

Figure 3 Code Far Application

Suppose, for example, that your application provided some sort of data archival service to your customers. In this scenario, the typical pattern is write once, read seldom (or never), and latency would not be too much of a concern.

On the flip side, suppose your application was highly transactional with many reads and writes per second. The performance of this type of application would be poor if it was running on your corporate network and the data was located in SDS. Some sort of data cache, perhaps the project code-named "Velocity" might help, but as application architects and developers, we need to look at each application on a case-by-case basis to identify the best architecture for the application's purposes.

New Face of SQL Data Services

SDS is the relational database of the Azure Services Platform, which will be commercially available at PDC 09 this November. SDS currently provides the key relational features you have come to know and love from SQL Server. Over time, additional features will be enabled, as well as support for additional products in the SQL Server family, such as SQL Server Reporting Services and SQL Server Analysis Services. Because SDS is accessed over TDS—the same protocol as SQL Server—all the existing tools, client libraries and development techniques continue to work. I hope that by reading this article you have been given a glimpse of the new face of SDS, and that you can see that it is truly an extension of SQL Server in the cloud.

David Robinson is a Senior Program Manager on the SQL Server Data Services team at Microsoft. He spends his time driving new and compelling features into the product. He also enjoys doing presentations at community events and getting feedback from customers on SDS.