What CIOs need to know about SQL Server 2016

With SQL Server 2005 now out of support, if you haven’t already started migrating your older databases onto a newer, supported release to stay in compliance with regulations like PCI DSS, that’s now urgent.

But even if you don’t have an urgent need to migrate, there are several reasons why you may want to consider SQL Server 2016.

The new security options will be significant for many businesses. Integration with Azure gives businesses a new approach for both availability and bursting performance to the cloud. And Power BI is an option today that will become a key part of SQL Server reporting in the future. Plus, this is the version that Microsoft will be bringing to Linux, giving you a new option for moving off Oracle.

“Microsoft is hitting the right notes when it comes to features in the latest database release such as in-memory, cloud, scale, hybrid platform, simplified BI, improved security and extreme performance,” says Forrester Research analyst Noel Yuhanna. “All of these are what customers are demanding, plus Microsoft is also innovating in the hybrid cloud platform. This release is more than just an incremental improvement.”

Performance and security

Microsoft claims that SQL Server has fewer vulnerabilities than competing relational database systems, and it’s also adding more features to help you protect your data.

The dynamic data masking that you would have had to look to a third-party product for is built into SQL Server 2016. It’s not as sophisticated as a full data virtualization solution like Delphix, but you no longer need to let database admins and developers have access to the contents of every database they work with (think of it as the Edward Snowden problem).

The licensing rules for the fully-featured and essentially free SQL Server 2016 Developer Edition will encourage better data hygiene as well because you can’t use it in a production environment or with production data.

New row-level security lets you assign access to specific rows in a table, rather than the whole table, using roles and policies, so you can ensure that malicious — or just curious — insiders can’t see data they’re not supposed to be working with.

The new Always Encrypted option lets you protect your database at rest on the client and on the server as well as when the data is in motion and uses a second encryption key to protect the keys that actually encrypt the data inside your table. And you can query the data without unencrypting it, which means database admins will no longer be able to see the contents of databases they look after. Only the clients that you deploy the correct key to will ever show the data unencrypted. This is one of the features that make the integration with Azure secure enough for enterprises.

On the performance side, SQL Server already has an in-memory columnstore option for speeding up highly concurrent workloads where you’re analyzing large data sets by moving data into memory-optimized tables (it’s ideal for data warehouse workloads), and that now scales to tables up to 2TB in size. The new batch mode for executing these queries processes multiple rows together so it requires less CPU time, giving you faster queries with less contention.

It’s also easier to get those improvements. There’s a new transaction performance report to help you find tables that would benefit from in-memory optimization, a wizard to help you with migrating data to columnstore, and another that automatically optimizes the configuration of your database — all the way down to the temporary files that the database engine relies on to run efficiently — to match the CPUs on your server.

Keeping time

One of the most interesting new features in SQL Server 2016 is a form of time travel for data. Temporal Tables store the history of updates to database records and can be a quick fix for problems with database design. If, for example, your query looks up the current exchange rate rather than the historical one, you’ll get the wrong figures for last year’s international sales, but Temporal Tables let you extract the rate that was in the original query. More broadly, you can go back and query the database to see how it looked at a specific point in time (very handy for audits) and fast-forward through the data to see how a query has evolved over time. And you can do all that with logic inside the database rather than externally.

Analytics

A new memory-optimized online transaction processing (OLTP) engine means you can use those columnstore indexes for analytics that look a lot more like real time. Microsoft claims there’s no delay even if you’re running analytics and OLTP against the same table. Potentially, that means you might not need Extract, Transform and Load (ETL) or a separate data warehouse setup.

That includes advanced analytics and even machine learning, thanks to the integration with R Services.

Usually, advanced analytics applications move data out of the database itself and into an application tier where the analysis takes place. That means a lot of expensive, potentially slow data transfers, as well as needing to maintain analytical models outside the database. SQL Server 2016 moves the analytics into the database system itself.

Joins, aggregation and even machine learning can happen inside the database, so analytics apps can be simpler systems that query the tabular models in the database.

If your machine learning model updates once a day (so that you’re automatically extracting new insights from data as it changes), you don’t also have to update your analytics app every day. This also lets you centralize the analytical models you’re using, to make sure they’re consistent across the business.

“Customers are using these for operational analytics, like fraud analysis where you can rely on machine- to- machine autonomous decision based on algorithms,” Marko Hotti, a senior technical product manager in Microsoft’s SQL Server Product Group, tells CIO.com. “This is about making smart decisions faster. You’ll still need a warehouse for large scale and historical analytics, but you can start by using tabular models and see what you’ll need in the future.”

Yuhanna strikes a note of caution, however. “Some of the data will be fast data, that needs to be available quickly without any latency, so moving the data from OLTP to the operational platform to the analytical platform just adds to the latency that impacts business decisions. However, data warehouses are still needed in the traditional sense, since you want to do trends and predictive analytics that require crunching, transforming and aggregating data. Data warehouse are best suited for those.” Yuhanna says he expects that in the future organizations will have a combination of platforms.

If your analysis needs to include big data, the PolyBase technology in SQL Server 2016 makes it significantly easier to handle relational and non-relational data together by querying external data in Hadoop or Azure storage directly from SQL Server, using T-SQL.

“Today, most enterprises are realizing that they simply cannot store all of their data in relational databases, especially large volume of non-structured data, which fits well in Hadoop,” Yuhanna points out. “PolyBase technology helps bring these two environments closer, by querying data sets easily as if they were one. We have already seen many customers using this to support customer analytics, fraud detection, healthcare analytics, and real-time analytics.”

PolyBase is smarter than a simple connector. If the query will run faster on Hadoop using MapReduce, it will automatically run there. Working with PolyBase and SQL Server is far less complex than working with the Hadoop web interface, and you don’t need to train all your business analysts on Hadoop to be able to run analytics over your big data store.

Yuhanna suggests that PolyBase could democratize access to big data, and ease recruitment problems for businesses. A recent Forrester Research survey found that the majority of enterprises (85 percent) have fewer than 100 people using Hadoop for insights and analytics today, but 65 percent say they expect to have more than a thousand business users and analysts accessing Hadoop in the next two to three years. “Tools like PolyBase will certainly help organizations access data easily and in a self-service manner,” Yuhanna says.

On the everyday reporting side, SQL Server 2016 is also a significant update. It fixes some long-standing annoyances (for example, you no longer a need a browser plugin to print reports, so you’re not tied to Internet Explorer), the report layout tools get much-needed improvements and mobile reports allow you to work on a tablet or smartphone.

Reporting

To understand the future of SQL Server reporting, look at the integration with Microsoft’s Power BI service via the Enterprise Gateway that lets you visualize your data in Power BI while keeping it on your own servers. “Most customers still live in the on-premise world but they need to access data from anywhere,” Hotti points out.

The Mobile BI publishing tools for SQL Server and the Power BI Desktop app will also merge into a single tool in future. “We want to have a single tool for developing mobile reports and deploying them both to the cloud and your on-premises server,” Hotti says. Similarly, he says Power BI and the SQL Server reporting APIs will have the same features, eventually. “If you want to do Salesforce integration, Power BI has that today. It’s not in reporting services yet, but it’s hopefully coming in a service pack.”

To keep up with Power BI, Microsoft is building the on-premise reporting tools on a much faster cadence. “We’ve already started releasing updates with new features more often, and even more features will come as we try to catch up with the cloud,” says Hotti.

Cloud integration

The cloud is key for SQL Server 2016. This is the first version built using Microsoft’s new model of testing features in the cloud first, at scale, before bringing them to the on-premise products. That makes the new release stable enough that customers like DocuSign and Saxo Bank are already running it in production, even though it only reached general availability at the beginning of June.

As well as the Power BI integration, SQL Server 2016 takes advantage of Microsoft’s Azure cloud for devtest, for backup and recovery, and for extending your database applications into the cloud instead of paying for premium storage as your data grows. You can use that integration to put the secondary replicas for your Availability Groups on Azure, or you can use it to seamlessly extend your on-premise database to the cloud with the new Stretch Database option, where it’s always encrypted (and you don’t need to rewrite your applications to use it).

“Hot data can exist on-premise, while cold data moves onto the lower-cost cloud platform for long-term retention,” says Yuhanna. “Every organization is going through data explosion, so why keep data in more expensive servers when it can be offloaded with ease?” He sees it as a good fit for a hybrid cloud strategy.

Linux support

An even bigger change that’s coming with SQL Server 2016 is support for Linux. It’s obviously intended as a way to attract customers dissatisfied with Oracle who don’t want to move to Windows Server and to support customers already using Linux on Azure (much like Microsoft’s move to bring .NET to Linux). (http://www.cio.com/article/3026664/open-source-tools/the-real-reason-microsoft-open-sourced-net.html)

“Today, Linux is the most popular operating system for databases, whether on-premise or cloud,” said Yuhanna. “While the results of this move will surely come over time, we believe it’s the right move, a bit too late, but nevertheless it’s likely to put pressure on Oracle and Amazon AWS and take away some database market share.”

Pricing

Microsoft is fond of pointing out the price advantage of SQL Server by claiming that everything is built-in. That’s true of the Enterprise Edition, which you need to get Availability Groups, Always Encrypted, Temporal Tables and Mobile BI. “We think that for now, Always Encrypted is a very specific scenario for enterprise customers,” Hotti said. “Enterprise is not only about massive scale but it’s also about features we think are relevant to the enterprise. Mobile BI is right now is enterprise only, because it gives additional value.”

SQL Server 2016 does have the same list price as the previous two versions, but the SQL Server BI edition goes away. If you have Software Assurance, your licenses will be converted to Enterprise Edition, but that also means you switch to paying per core, rather than for the server and calls. The costs could be very different for some businesses and will likely balance out any savings you’ll make from the changes in developer licensing.