We ain't in 1996 anymore, Dorothy: SQL Server 2016 proves it

Microsoft has had a database since 1989, initially working with Ashton-Tate and Sybase to create a variant of Sybase SQL Server for IBM’s OS/2.

But it wasn’t until 1995 that Microsoft really got serious with SQL Server 6 for Microsoft’s rock-solid server operating system Windows NT.

Back then, however, engines like SQL Server - along with IBM’s DB2, Oracle - were being billed as relational when they weren’t.

And so it was that the follow-on SQL Server 6.5 in 1996 struggled to provide basic relational features such as Declarative Referential Integrity (DRI arrived in 6.5 but didn’t include Cascade Delete and Update).

In those days, each shiny new version was examined to see how well it matched the basic feature set that Ted Codd had outlined for relational engines.

Two decades on and all of the basics have long since been covered, so the new features are often unique to each engine. This makes differentiation much easier and it also explains the slightly rag-bag nature of the features listed below.

So here, in no particular order, are what I think are the best new features in SQL Server 2016, released at the start of June.

Stretch database

Microsoft has gone large on the cloud, so it is not surprising that Stretch DB has appeared as part of SQL Server 2016. You can now create a function on table that will move rows meeting a given condition into the cloud.

Why would you want to do that? Well, imagine that you have a large table, maybe a terabyte or so - given that I am on a nostalgia trip, SQL Server 6.5 proudly boasted that databases of up to 100GB could be supported. Suppose that most of it is historical data that you rarely need to query but it has to be there for the times that you do.

You simply set up a function that moves rows older than a certain date into our nebulous friend and a 1TB table becomes 200GB on site and 800GB on cirrus. Any queries that run are simply written to run against the local table. If they request recent rows the local data is used to service the query; if not, a call is transparently made to the cloud. There is no need to backup the cloud part of the table, Microsoft will do that for you so, essentially, this feature can be used to create an online archive.

Of course, there is some bad news to balance the good. The function has to use an absolute date so you can’t set up, for example, “current date - I month.”

BI edition goes

SQL Server 2012 introduced the BI edition, and 2016 kills it. That edition always was a slightly strange mix of features so it comes as no surprise that it was never popular. It is unlikely to be much lamented and Microsoft says that it is dealing individually with the customers who did adopt it.

Security

SQL Server 6.5 saw the introduction of row-level locking; 20 years on we get row-level security which is a fair indication of how feature-rich the modern database engines have become. So, for example, we might set up security so that when a user runs a query, their username is used to look up the department to which they belong and then their query returns only the rows in the table that appertain to their department. Another user runs exactly the same query and they see only the rows for their department.

Data masking

Continuing with the security theme, we can store, for example, a customer’s credit card number in a table. If I - with my low security clearance - query the table I see, I receive not the full number, but a series of XXXX and the last 4 digits. You - ruler of the security world - run the same query and see the entire number.

Datazen

Last year Microsoft bought Datazen Software, a mobile BI provider. Essentially, Datazen offered the ability to sync with live data sources but also the ability to cache up to 100,000 records on the mobile device being used.

Given the data coverage in some parts of the country, this is a huge boon as it allows the user to continue working with the data when the connection fails. Datazen always did offer very good integration with SQL Server Analysis Services and the SQL Server platform in general. So it doesn’t come as a huge shock that its software is now integrated into Reporting Services.

Overall, Microsoft is trying to ensure reporting becomes more unified. A hint of that may be found on the SQL Server blog:

“To achieve these requirements, we are aligning our cloud and on-premises solutions. It is our intent that your reporting technology investments and expertise will transfer across these deployment modalities.”

Translated, I take that to mean that our expensive reporting technologies and hard-won expertise will work on both cloud and on-site deployments. Which is good. I’m pleased.

R Server

As an avid R fan, I am delighted to report that it is now possible to call R from within stored procedures. One might begin to wonder what other languages are in the pipeline.

The long and the short of it

Since those early steps of the mid 1990s, SQL Server has become firmly established in the league of extraordinary database engines.

What is apparent now, however, is that SQL Server is becoming more and more cloud-aware and this in turn may well be indicative of Microsoft’s changing attitude.

Twenty years ago, Microsoft desperately wanted to possess an enterprise level database engine and was very keen to sell you the product that it actually had. Now it has an enterprise level database engine and is very keen to sell you a service in the cloud, that cloud being its own - Azure. ®