Why the cloud is key to on-premise SQL Server 2016

Posted 09 June 2015 - 08:30 AM

sincity

Advanced Member

Members

2282 posts

63Getting Better

Introduction and stretching to Azure

The fact that Microsoft runs its Azure cloud with the SQL Database service is changing what it's putting into the next version of SQL Server, and how the company is building it as well, Microsoft's T.K. Rangarajan told TechRadar Pro.

One of the most important new features seems tailor-made for security in the cloud – that's what Microsoft is calling Always Encrypted. "Our customers could keep data in the cloud that we don't understand, that governments don't understand. No matter who gets the data it's only the customer that has the ability to interpret it – but they can run queries on it.

"We essentially encrypt the data in the client and keep the master key with the customer," he explains. "As soon as it leave the client, in the server it's never in clear text; it's always in cyphertext." But that doesn't mean you have to pull data into a client app to work with it.

"We are able to do some sets of operations even with the encrypted text – that is the exciting part. The cyphertext is encrypted inside the client using the master key that only the client knows. It's a similar concept to a password hash, but the beautiful thing is you can still do all the usual performance database optimisations, range scan, hash into the right place and all of that – that's possible because of this.

"You can search and query against the cyphertext. And we've done it in a clever way, so for example, if you need to change the master key, you won't need to go re-encrypt the full database over again; we've done some optimisations there."

Rangarajan claims being able to work against encrypted data is unique: "We are the first in the world to do this kind of thing in the server. This is comparable to the holy grail in encryption." But he also says it's only the beginning. "There is some good wizardry there but we think this is only the beginning of a slew of innovation coming in the future."

Stretch when you're cold

An even more obviously cloud-based feature is the option to 'stretch' a database to Azure by automatically archiving old data. "The idea is if you have an on-premise SQL Server database and you have data, say your order lines, that is historical, that keeps accumulating in append-only type of tables. Today, you keep them for a certain time then you drop partitions from time to time or you archive them in some other way. With Stretch we say it's an infinite database – where I have data that's older than a certain date, that fits certain criteria, automatically, behind my back, dribble it to the sky, stretch it to Azure."

But this doesn't work like a backup where you have to wait until it's all finished. "Once you set this up, data gets moved from time to time and we have the flexibility to move it in interesting chunks, but along the way all your transactions succeed correctly with all the same acid guarantees, so you have no changes to make to applications on premises – but the historical data gets stored in Azure."

Not only does that take the load of the databases you use daily, it also puts your historical data in the right place for using it with cloud services for business intelligence and other analytics like machine learning.

Tip of the iceberg

"Think of the database on premises as the tip of the iceberg," Rangarajan suggests, "so the frequently updated hot data remains in SQL Server on-premise and the 'SQL in the cloud' which is essentially SQL Database, holds the big bulk of the data – the cold data sits in the cloud which is optimised for large volumes of data, nicely distributed and all that good stuff." If that sounds familiar, he compares it to "Storsimple for relational tables".

But SQL Server 2016 will also simplify doing real-time analytics against your live server. "We give you the ability to have real-time analytics on the fully in-memory optimised transaction tables," says Rangarajan. "We have leadership in in-memory OLTP, SQL 2014 had that, and we also had a separate columns store that is optimised, so you could take a table into one or the other.

"Now we're saying that same table that is optimised for OLTP, you can create the column store indexes for that and then you can access the same table, at the same time, for both types of workloads. What that means is a dramatic simplification for BI scenarios. You no longer have to move data just for separating out analytics workloads, you can run it right on SQL Server."

Some of the other new features will take advantage of this – the built-in integration with R, Datazen (the mobile BI company Microsoft bought recently) and Hadoop (using the PolyBase technology for working with both relational and non-relational data using T-SQL).

Building server software using the cloud

Azure is also changing how Microsoft is working on the preview of SQL Server 2016, which will reach general availability "sometime in 2016". Customers will still get regular Community Technology Previews, just monthly rather than every six months. And what they get in the CTPs will already have been tested in production – in the Azure Database service.

"We've really synchronised the codebases for all of our SQL assets," explains Rangarajan. "So the current code is the current code across all SQL assets. When we first build a technology, the easiest place for us to share that with the world is to deploy it in SQL Database, where we are fully managed, where we face all the consequences of futzing something up. If it backfires it's us that has to manage.

"We're very careful, we flight that, we incrementally roll out at all hours, we face the music for it if anything goes wrong. So we first roll that out and we stabilise it, we ruggedize it, and then we roll out this capability to our preview customers every month."

There might well be problems with new code along the way: "I'm sure we'll have a couple of hiccups and we'll overcome them," he predicts confidently. "By the time the release is available, we expect most of capabilities of that release to be well baked in production."

Cloud confidence

Thanks to that, he thinks the final release will be ready for customers to adopt quickly. "My expectation is that the box will be more ready to be in production as it comes out." Ironically, Azure is the key to improving the server software. "It's only because of the cloud that we're able to speed up the on-premise world," he points out.

"If we didn't have a cloud, we wouldn't have the confidence that we could distribute this to even CTP customers and have some level of confidence that it will work out fine, because we will have this in a million databases. That gives you a level of confidence that's hard to match."

"This a dramatic switch in our point of view from the previous releases to this release, from SQL Server 2014 to SQL Server 2016. In SQL Server 2014 we said 'let's not mess with the on-premise stuff that runs most of the world's businesses, let's leave it alone and keep it safe and nice and separate'. And we ran SQL Database [on Azure], innovating in the new world. Now we feel very comfortable with the new world and we are taking this deeper cloud-first lifestyle to the server."

But he hopes the process will give customers confidence in Azure too. "We have the opportunity to do these sorts of gentle introductions to the cloud, leading our customers to the cloud. And frankly I think we also have the obligation… Our customers have bet on our technologies. They have spent a lot of lives and their careers becoming experts on these technologies, and we have an obligation to take them to the new world with all of this stuff that is valuable."