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