SQL Data Services Team Blog

Exchange Hosted Archive - A True Testament of Scalability

Hi everyone, this is Shankar Pal. I am a Principal Program Manager on the SQL Data Services (SDS) team. I spend my time working on the backend design for large, enterprise applications.

I wanted to share with you some of my experiences on the scalability of SQL Data Services and how this is best exemplified by one of our online services, the Microsoft Exchange Hosted Archive (EHA). This is a very rich service for e-mail archive, e-Discovery and regulatory compliance for corporate customers and large organizations. The next generation EHA uses the same relational database service infrastructure as SDS. I will focus on the section of the service pertaining to the scale aspects of the workload, and discuss how the relational database service addresses the scale requirements of EHA.  

First, a brief introduction to the characteristics of the workload. Archived messages accumulate in the system and are governed by the retention policies of the customers. The message lifecycle goes from archival of messages in the system, retention based on retention policies (e.g. 3 years) and purging the messages at the end of the retention period. Inserted messages are full-text indexed on the header, subject line, message body and a variety of common business attachments such as Word documents. E-discovery consists of structured and full-text query of the messages. Examples are searches based on various properties such as the send time, the sender, or full-text search of the message body.

EHA looked for a long-term solution in a relational database service which would scale to a much higher archiving limit per customer than the current system, be easy to administer, provide the required availability and keep pace with the rapid growth of the service. The result is the next generation EHA which is powered by the SDS relational database service platform. The service allows the seat limit per customer to become many fold larger; this is achieved by distributing the archived emails from each customer to a large number of servers rather than to a specific server. Performance enhancements are seen during message insertions, as well as in structured and full-text queries across the system. For more information about the backend architecture, you can view a presentation from Gopal Kakivaya, a Distinguished Engineer in the SDS team, from last year’s PDC. That video can be found at http://mschnlnine.vo.llnwd.net/d1/pdc08/WMV-HQ/BB03.wmv.

Email is a very prolific form of communication. The high volume of incoming data is automatically replicated within the database service to provide fault-tolerance against various types of failures. The platform provides high availability whether storing gigabytes, terabytes, or petabytes of data. Each cluster of machines has a capacity to store hundreds of terabytes for email archive. Together with the replication and the backup requirements, the total capacity of the EHA cluster is petabytes of data, a testament to the scalability of the SDS relational database service platform.

The enormous scale is achieved with surprising simple design principles. Mail messages can be partitioned in several ways, the most obvious being by customer or user. Such segments can grow quite large, so for more parallelism, each customer or user’s messages can be partitioned further, most notably by the send time. A variation of this partitioning scheme is used for the EHA application. The partitions for each customer are scattered over many servers. This increases the throughput of the system for message insertion by distributing the write operations over a large number of physical servers. The net result is much higher insertion rate compared to the current EHA system.

Queries benefit from the physical distribution of the data as well by executing on multiple partitions scattered over many server machines. The process of running queries in parallel, sometimes referred to as fan out, and aggregating the responses pays greater rewards the more complex the query and the bigger and more distributed the overall data set. Customers, especially in heavily regulated industries, frequently perform full-text searches using date ranges and other qualifiers. The more structured the query the more relevant the results. Our measurements with real customer messages show that queries with a high degree of fan-out often execute an order of magnitude faster compared to a single instance of a server.

The new generation archive will be available later this summer. It is very exciting to build a system which uses physical distribution of data to meet the scale and performance requirements of a large enterprise application. The self-managing system simplifies a host of administrative functions and makes those more reliable.

One more TechEd Video - The New Face of Microsoft SQL Data Services

Here is one last video we filmed a few weeks back during TechEd. In this one, we discuss the new features of SDS including the development model, tools support, and how the services interoperate with the other building block services of the Azure Services Platform

The video can be found here

Scaling Out with SQL Data Services

Hi Folks,

Here is another video from TechEd. In this one, Rick Negrin and I talk about scaling out your database with SQL Data Services and the performance gains you will see. The video can be seen here.

Enjoy!!!

Dave

The Role of a DBA with SQL Data Services

Hi Folks,

Here is another Tech-Ed Online video. In this one, Rick and Zhongwei talk about the responsibilities of a DBA with SDS. 

I hope you enjoy,

Dave

Provisioning an SDS Database

The SDS team was extremely busy last week at TechEd North America in L.A.. We presented 3 sessions, recorded 4 TechEd online videos and spent a great deal of time speaking with customers.

Here is a video we did on what the provisioning experience will be like for SDS in the Azure Developer Portal. You can see the video here - Provisioning an SDS Database in the Azure Developer Portal

Enjoy!!!

Dave

Accessing SDS from C# and Java

Hey Everyone,

Jeff Currier, one of our superstar, non-slacker, dev leads has put together some examples for accessing SDS from both C# and Java. The C# example can be found here and the Java one here. In the C# example Jeff also gives a good explanation of our logical server and user database model. Check it out!!!

Any questions, just ask :)

-Dave

Accessing SDS From PHP

Hi folks, this is Nigel Ellis and I’m an architect on the SQL Services team.   I’ve just returned from MIX2009 where I announced our new relational service in my What's New with SQL Data Services talk.   You can watch the full video and download the slides from here.

During my talk, I demonstrated running an application on Windows Azure using PHP to interact with a database hosted in our new relational SQL data service.  I used the unified ODBC support built in to PHP to interact with SDS.    I’ve included a sample code snippet below which uses an SDS server server.data.dev.mscds.com:

In this example, I’m using the SQL Server native ODBC driver which ships out of band from the operating system.    Other driver choices may be used as outlined in the table below.

For those familiar with ODBC you’ll note this pattern is also used to connect to a regular SQL Server instance.   The only change in the code is the name of the server, use of encryption and the use of SQL standard security (username and password).

In addition to the ODBC driver patterns, there is an open source Native PHP driver for SQL Server.  This driver is a native client library built over the SQL 2005 Native Client.    Today the driver requires the target SQL server support MARS.    This prevents the driver from working with SQL 7.0, 2000 and SQL Data Services as MARS is not supported.   The good news is that we’re working with the native driver folks to expose MARS as a configuration option; this support is already provided by the underlying ODBC driver.  This change allows the use of the PHP Native driver with SDS without requiring SDS to support MARS.

In the end, you’ll see the patterns used to access SDS are identical to those you use to access SQL Server.  

Nigel.

Mix ’09 Update #2

Hello again from MIX ’09! 

Nigel Ellis just finished up the MIX session on SDS: What’s New in Microsoft SQL Data Services (MIX09-T06F).  (Edit: The talk is available for online viewing from here).

Attendance was surprisingly good for 9am on Friday in Las Vegas.  As expected Nigel did an awesome job on the talk and the overall messages and drill down information seemed to resonate well with the audience.  In particular, Nigel did a really nice demo on the compatibility SDS v1 will have with on-premise SQL and how that will make porting existing applications to a cloud environment really easy.  A very strong message particularly in conjunction with the ability to run web and worker processes in Windows Azure Compute.  Together, these capabilities really provide a great foundation for delivering next generation services based applications.

One surprise was that the message around support for PHP, Ruby, Java and other breadth development environments – through the support in Windows Azure for fastCGI and our support for existing SQL client libraries like the SQL PHP client library – didn’t seem to resonate as much with the MIX audience.  I think it will take some time for folks to digest some of the additional nuances of our overall direction so that may be part of the reason.  Also, even at MIX we may still be initially “self selecting” to an audience that is broader than the traditional core web developer. 

Lots of follow up interest and good questions at the end, including questions on:

• Load balancing and flexible capacity – esp. for handling burst periods for applications
• Data sync and how we will leverage the Microsoft Sync Framework technology
• How to get started, esp. when doing local development in conjunction with Windows Azure compute
• Support in v1 for other capabilities – including full-text indexing and spatial
• Integration and compatibility with other key parts of the data platform – Reporting Services, Integration Services, etc.
  • This message really seems to resonate broadly.  There is lots of value we’re offering here even in v1 with SDS being a well integrated part of the overall SQL Data Platform
• Request for additional guidance and patterns and practices for scale-out database applications.  Nigel did a good job of leveraging this interest to drill down on our support for SaaS
• ISVs, both in v1 and beyond.
• Geo-location support
• And a request to make the slides available :)

All in all, a great conference.  We really appreciate all the interest and MIX was just a fantastic opportunity to meet with and get additional feedback from many of you who are investing in the service.  It will be quite a fun ride as we drive toward our v1 delivery.

Patric

Accessing the New Relational SDS with REST

Hi Everyone! This is Lev Novik, one of the architects on the SQL Data Services Team.

We get a number of questions about whether or not we have abandoned or downgraded REST access to SDS, and if so, why. To summarize: not at all --- rather than abandoning REST, we intend to support it in precisely the ways and places where we believe most people want it. Allow me to explain.

Consider an average (web) application today. Most often, it consists of a:

• client (often running in a browser),
• mid-tier business logic (often running in a web server), and
• back-end storage (often a relational database).

What are the protocols that are typically used? Well, the protocol between the client and the mid-tier is usually HTTP and often REST; the protocol between the mid-tier and the back-end storage is typically the native protocol of the back-end database.

Well, with SQL Data Services, the situation is quite similar. In these scenarios:

• SDS takes the place of the back-end database,
• mid-tier business logic is running maybe in Windows Azure, or maybe outside,
• and the client, well, the client is unchanged

In this world, we believe that you will continue to want to use the most powerful mechanism to talk from your mid-tier to SDS, together with the nicest database tools; and you will continue to want to be able to easily expose your web application to its clients in the most simple, elegant, and web-friendly way. This is why we concentrate on:

• Exposing TDS (SQL Server native protocol) as the full-featured way to talk to SDS
• Making it very easy for you to expose REST access to your SDS-based application.

To achieve the latter, we rely on ADO.NET Data Services (aka “Astoria”). Its very purpose in life is to make building data-driven REST services as easy as possible. For those who haven’t played with ADO.NET DS, I highly recommend it --- grab a favorite beverage, watch a quick introductory movie, get hold of a database of your own, and see what it takes to make a REST web service out of it (you might just have some of the beverage still remaining at this point).

But of course, your application is unlikely to be just a one-to-one mapping of a database --- you will want to build your own business logic, your own security policies, your own views, etc. ADO.NET Data Services allows you to implement these, and to make your creation into a real non-toy service (though this will certainly involve a few more trips into the kitchen!)

But that was SQL Server on your own dev box --- how does that work with SDS and Windows Azure? Well, we intend to make it work in much the same way:

1. Get set-up to develop Windows Azure services.
2. Create an SDS Database in the cloud, and load it up with your schema.
3. Create a Windows Azure Web Cloud Service Project in Visual Studio.
4. Add ADO.NET Data Service project item, and point it at the SDS Database (or at a local database with the same schema).
5. Develop your ADO.NET Data Service (security, biz logic, templates, etc)
6. Change the connection string to point to the real SDS Database in the cloud (if you used a local database for development)
7. Publish your service to Windows Azure.

And at the end, you have an SDS-based REST Service, configured just the way you like it!

Hope this made some sense!

--Lev

Update from MIX ‘09

Hi!  For those who I haven’t met yet, I’m Patric McElroy and I’m the GPM for the SDS team.  I’ve been with the team since last summer and it has been an amazing ride so far – lots of progress, lots of learning and lots of cool stuff ahead of us.  For most of my career at Microsoft, I’ve worked in the enterprise server space (BizTalk Server, SQL Server) as both a GPM and PUM.

You’ve heard of “Internet Time”?  Well, apparently this blog post is on “Vegas Time” – where everything is behind schedule because you got talked into going out after the MIX Influentials Party and are subsequently way behind.   Hopefully, the rest will “…stay in Vegas”.

Yesterday was a fun day at MIX ‘09.  ScottGu’s keynote was amazing and although it was only a couple of minutes in amongst a 2-hour keynote, ScottGu did talk about the Azure platform announcements with the SDS announcement from last week being a key announcement.  Even a week after the original announcement, ScottGu’s mention of it got a smattering of spontaneous applause during the keynote.  Scott talked about our plans to support TDS/T-SQL, talked about this as part of the overall database roadmap for the SQL data platform, and emphasized that this enables access from any client library – .Net or OSS.  Finally, he emphasized the value proposition for web developers of being able to develop/debug applications locally and then deploy and run them in the cloud.  (The Azure platform material starts about 47:30 into the video feed for the Day 1 keynote which can be found here: http://live.visitmix.com/).

I also got pretty excited at many of the other capabilities that were highlighted during the keynote as well.  In particular I think that the Windows Web App Gallery will be a great option for developers (see the 10 minutes preceding the SDS material).  One thing I’ll take back to the team is to make sure we are doing everything necessary to enable these apps to be built on and leverage SDS.  The goal of the gallery is very much in line with the core value prop of SDS v1 of enabling a very friction-free provisioning experience.  It would be very cool if developers had an easy way to ensure that their web apps could have the option to provision an SDS database automatically as part of their install.

One of the coolest parts of these conferences is the opportunity to talk with a lot of customers and partners.  The response to our plans to accelerate delivery of true relational database capabilities as a service continues to be very positive.  On Tuesday night I attended an event hosted by the D&PE group where I spoke with an ISV partner who builds LOB software for the insurance industry that they deliver as a service.  Lots of fantastic feedback from someone who currently makes their living building a running services-based software.  Their app currently runs on SQL Server which they host in their own datacenters.  His initial comment was the most interesting.  He said “This is great.  This will allow me to consider how and when I can migrate my application to the Azure platform.”  I spent a good part of yesterday afternoon talking with press and analysts and the reaction/feedback was the same. 

Nigel’s session on SDS is tomorrow (Fri) morning so keep an eye out for the video feed on the above link.

Patric

First round of Questions and Answers

Tuesday was an exciting day for the team, and more importantly, for the industry. I think one of the best responses I got from someone was that they “stood up from their chair and did a dance of joy”.

Here is the first round of questions I have received and the answers you are looking for.

How will existing accounts be migrated over to the new version?

We haven’t worked out all the details yet, but our current plans are to send invites to all registered users when we launch the CTP by mid-year 2009.

Are we losing some BASE capabilities to grant ACID capabilities?

The whole theory behind BASE (basically available, soft state, eventually consistent) is to gain scalability at the cost of consistency. We have always supported full ACID capabilities in the service and will continue to do so.

Are we losing scalability or partition support to guarantee consistency?

The short answer is no. The foundation of SDS has not changed. All of the guarantees around scalability and consistency still apply. In the ACE model, each authority had one or more containers with each container being the unit of consistency for query and update.  The new SDS model hasn’t changed this if you think of each authority now being a server and each container a database.  You now get the richness of the T-SQL model over your databases!

When? or to quote JamieT  “When do I get to party on this new service”?

We’re on track to deliver a public CTP mid-calendar year 2009 and ship in the second half of calendar year 2009.

If Microsoft can deliver on the features for SQL Data Services (SDS) announced today then they have set the bar for SQL and Cloud database vendors

Yes, we believe we have.

Will SDS support Database Encryption (certificate and key management).  Any support for row level versioning?

Database encryption? Not initially, but it’s on the list and as we have demonstrated – if there is sufficient customer demand, it will be one of the first things we add after v1. As far as row level versioning, this can mean a few different things. Do we support statement level snapshot transactions? Yes. Do we support Change Data Capture? It is still being evaluated.

The blog entry states “If it works with SQL Server, it will largely work with SQL Data Services.”. That word “largely” bothers me a little – it suggests the functionality is going to be reduced slightly. Details please?

We will be providing documentation soon on what is and is not supported in SDS. I’ll post an entry to the blog once the guidance is available and you can also keep an eye out for it on our MSDN Dev Center. But, to answer the question – We say *largely* due to the fact that there are things that just don’t apply in a cloud based world like setting the location of a data or log file or making server wide configuration changes.  In v1 we expect to deliver a surface area that will support the vast majority of SQL Server database applications.

What artificial limits will be in place?

The database size will be capped. We are still evaluating what the cap will be, but the plan is to ensure that the allowed database size supports most, if not all, departmental and web application workloads.

Do we have to pay per instance, per MB of storage or per bandwidth usage?

The pricing options and levels are still being finalized.

Will it be SQL Server 2005 or 2008? Enterprise or Standard?

The core engine is based on the SQL Server 2008 technology foundation.   The feature set does not map to a specific SQL Server SKU – in fact you should think of the service as its own SKU.

Will we be able to connect to our cloud instances from SQL Server Management Studio (SSMS)? And will we be able to buy just the client tools without an on-premises server license?

Our plans are to support SSMS, but it might be a requirement to install a new version or a hotfix or two. As far as buying just the client tools, using SSMS Express is a great option. You can find out more info here.

Will replication between instances be offered to aid with Business Intelligence?

Not initially. After you start working with the service let us know what functionality to include or scenarios to support. We want to hear from you!

Will you offer hosted SSIS/SSAS/SSRS?

It’s on the product roadmap, but I can’t comment on specifics or timing.

The blog entry states that only SQL Server authentication (username/password) will be offered initially. Can we assume that eventually the Microsoft Services Connector will be used to offer Windows Authentication?

Post v1 you can expect a more robust authentication story. I can’t discuss implementation details yet.

Will we have the option to expose a REST head using ADO.NET Data Services as part of SDS’s offerings (i.e. at the flip of a switch) or will we have to implement ADO.NET Data Services separately ourselves on Windows Azure?

During the v1 timeframe you will need to implement this yourself on the Azure Services Platform.  This approach is very similar to using ADO.NET Data Services against  an on-premises SQL Server.  There are many key capabilities of the framework that you specify (security, etc.) and so this does require some coding.

You say, you will no longer support the ACE model since Windows Azure has the same data model. If I’m going to put my application in Windows Azure (as I did) my best option for data is SDS, right? As I understood, Microsoft’s approach for cloud computing was Windows Azure as the platform for developers and SDS to store the data. And now you say I can use Windows Azure storage… what’s the difference? What’s the path to follow?

The  best storage option for an Azure Services Platform application depends on your application. At a very high level, if you require the features of a relational database, use SDS. If you require basic blob or “schemaless” storage, then Windows Azure Storage is for you.  Both will be key capabilities available to developers in the overall Azure Services platform.

Will the new SDS features support accessing SQL Server in the Cloud using LINQ to Entities and the ADO.NET Entity Framework?

It will work based on our client library story.  Interop through ADO.NET is one of our top scenarios.

I just finished reading the latest post on Data Platform Insider Blog. It says that customers who wish to use REST based interfaces to SDS can do it through customized ADO.NET Data Services. Does this mean that customers can consume the REST based ADO.NET Data Services through any platform?

Yes that is correct. I would also like to point out that we have drivers for SQL Server to work with most popular development stacks like PHP, Java and Ruby. Those drivers work with SDS as well.

Your latest blog posting says that the ACE model will no longer be supported – I guess you are referring to the SDS storage, does this also include the Windows Azure sStorage?

These announcements do not affect Windows Azure Storage in any way. That is still a key foundational piece of the Azure Services Platform.

How can an existing applications based on the ACE model be migrated to the newer version?

There is no direct migration path from the ACE based SDS to the relational based SDS. What you can do, however, is use the existing SSIS adaptor to move the data from your containers down to tables within a SQL Server or SQL Server Express database and then start using all of the relational features in the product. When the CTP of SDS goes live mid-calendar year, just deploy your schema and database to SDS via the available tools (e.g. Visual Studio)

Does Windows Azure Storage and SDS both use SQL Server under the hood? Is there really a difference between the two storage options?

 I don’t have all the details on how Windows Azure Storage works under the hood, but these are two distinct storage options of the Azure Services Platform – similar to the difference between saving data to your local file system and saving it to a database.

With ADO.NET Data Services compatibility, do you mean I can work against the System.Data.SqlClient and in the theory only change my connection string to switch between a local database and SDS?

That’s exactly what I mean. Pretty cool isn’t it?

SDS @ Mix09

Just some quick info on the SQL Data Services session at Mix next week. The session details are as follows:

The no spin details on the new SDS features

Today we announced the details of our plans to accelerate the delivery of core relational database features as part of SDS.  There has been quite a bit of buzz about SDS over the past couple weeks and it is great to be able to share the details more broadly.

If we flash back about a year ago to Mix 08, Nigel Ellis got up on stage to introduce the community to SDS which, at the time, was a flexible entity based cloud database that you accessed using standard internet protocols. We made this announcement with the promise that more relational capabilities would be coming - and they did. But the universal feedback we received from our TAP partners and other early adopters was the need for a relational database delivered as a service. This was extremely valuable feedback and drove us to more aggressively investigate ways in which we could deliver these features. As a result of that work and based on the progress we’ve since made in the product team, we are announcing that SDS will deliver full relational database capabilities as a service.

While we knew we needed to accelerate our plans we also knew we needed to hold true to some on the founding principles we had when we started our journey. Things like High Availability, Fault Tolerance, Friction Free Provisioning, Pay As You Grow Scaling, Immediate Consistency. We are still delivering on these promises and have added to the mix true relational capabilities, T-SQL and compatibility with the existing developer and management tools ecosystem.

What does this mean for developers? Developers will be able to very easily provision themselves a logical server and database and begin developing against it immediately using the existing tools and technologies that they are accustomed to. We are providing an experience where a developer can take an existing application and just change the connection string to point it to the cloud and have it just work.

How will we do it? Three letters TDS. TDS stands for Tabular Data Stream and it's the published protocol that clients use to communicate with SQL Server. From its inception, SDS has always been built on the SQL Server technology foundation and it just made sense to allow our users to access their data via TDS. Most importantly for developers, this means symmetric SQL Server functionality and behavior combined with compatibility with the existing tools you are familiar with.

                Tables?...Check

                Stored Procedures?...Check

                Triggers?...Check

                Views?...Check

                Indexes?...Check

                Visual Studio Compatibility?...Check

                ADO.Net Compatibility?...Check

                ODBC Compatibility?...Check

To be clear, the above is not a complete list of supported features.  However, given the feature set we are planning to support in SDS v1, a majority of database applications will “just work”, allowing developers to target on and off-premises deployments with essentially the same code base.  The initial scenarios we are targeting are things like web and departmental applications. We will be posting some content to our MSDN Dev Center in the coming weeks with specifics and getting started guidance but I encourage everyone to download SQL Express and the Windows Azure SDK to get started.

The core foundational components of SDS have not changed. This is still the same architecture that we have been telling you about for the past year and that underlies the current CTP bits. It is the same architecture that is powering some of Microsoft's key service properties and in the next few months will be used to store 100’s of terabytes of data in production deployments. Our early adopters (both internal and external) have shaken it down pretty well and we feel very confident about these bits.  The only difference is we are now providing a rich SQL model while maintaining the high availability, fault tolerant and scale aspects of the system.

What about the ACE (Authority, Container, Entity) data model and developer experience? Since Windows Azure storage has a similar data model (property bag) and developer experience, we will stop supporting the current ACE Model sometime in the future. Does this mean you can't access your relational data via internet friendly protocols like REST?  Not at all. You can still access your relational data (located on premises or in the cloud) via HTTP/REST using the ADO.Net Data Services framework. The compatibility with existing tools and technologies is a really important point to drive home and a super important value add that Microsoft provides.

Breadth and OSS developer support will continue to be a high priority for us and we will continue to support and provide breadth development libraries for all mainstream development technologies including PHP, Ruby and Java.  If it works with SQL Server, it will largely work with SQL Data Services.

What about Security? All communications with our service is SSL encrypted and our initial authentication will be using SQL Authentication.

Of course, SQL Data Services remains one of the key developer services of the Azure Services Platform - that hasn't changed. Consuming SDS from within an Azure application has never been easier and we will continue to ensure this is a feature rich, friction-free experience.

I have said a lot, so go ahead and digest all of this. What else do you want to know? As I am sure you all have more questions, feel free to email me at david.robinson@microsoft.com and I'll post the questions and answers for all to see.

Interview posted of Nigel Ellis and Niraj Nagrani from PDC 08

Just a heads up...

Nigel and Niraj were interviewed by the folks over at Deep Fried Bytes during PDC a couple months back. Its a really cool interview you should all check out. You can listen to it here http://deepfriedbytes.com/podcast/episode-26-discovering-azure-sql-services/

-Dave

SQL Data Services – What’s with the silence?