SQL Server 2016 is about to launch with a long list of shiny new built-in features along with much-needed improvements…
to important but humdrum capabilities database administrators rely on.
The upcoming release, slated for June 1, marks Microsoft’s initial go at a cloud-first version of SQL Server. It also happens to be one of the biggest releases in its history, with something for everyone, said Andrew Snodgrass, aresearch vice president at Directions on Microsoft, an independent analysis firm in Kirkland, Wash.
Some of the most notable SQL Server 2016 features include performance tuning, real-time operational analytics, visualization on mobile devices, and new hybrid support that allows admins to run their databases on premises and on public cloud services. Microsoft also invested in less sexy, but important, SQL Server 2016 features that hadn’t been improved in some time.
Indeed, SQL Server 2016 is an exciting release for reporting and ETL practitioners, according to Tim Mitchell, principal atTyleris Data Solutions, a data management services provider in Dallas.
SQL Server Reporting Services (SSRS), long suffering from release after release of few remarkable changes, received a significant makeover, he said. The classic Report Manager interface has given way to a brand new portal that looks and acts like a modern Web app — and it’s brandable, he noted.
The new KPI functionality makes building dashboards much easier, and the mobile reporting tools Microsoft added from the 2015 Datazen acquisition have made SSRS relevant for companies that support reporting for mobile users, according to Mitchell.
Performance tuning with the new Query Store is one of those ‘about time’ solutions.
Andrew Snodgrassresearch vice president, Directions on Microsoft
The changes in SQL Server Integration Services (SSIS) are more subtle, but significant. When the SSIS catalog was introduced in 2012, it brought many changes but one significant limitation: SSIS packages could no longer be deployed individually; instead, the entire project had to be deployed at once, said Mitchell, who is also a data platform MVP.
“To their credit, Microsoft heard the roar of negative feedback and have changed this in 2016, once again allowing package-by-package deployment,” he said.
For those boxed in by the limitations of SSIS catalog logging, a new feature that supports custom logging levels brings freedom. Also, for those who were previously forced to install multiple versions of SQL Server Data Tools to support the various versions of SSIS, the new SQL Server Data Tool designer allows for targeting of a specific SQL Server Integration Services version when developing SSIS projects, Mitchell said.
Performance tuning, In-Memory OLTP and PolyBase
Perhaps the most useful SQL Server 2016 feature for database administrators involves performance tuning, which allows DBAs to monitor and record the full history of query execution plans to diagnose issues and optimize plans. It will be invaluable for upgrades and patching to see where changes have impacted performance, Directions on Microsoft’s Snodgrass said.
“Performance tuning with the new Query Store is one of those ‘about time’ solutions,” he added.
Other notable improvements to SQL Server 2016 are PolyBase integration, and performance features with In-Memory OLTP and columnstore indexes are finally mature enough for most companies to deploy them, according to Snodgrass.
“The supported feature set, as compared to on-disk tables, was not on parity and it made it difficult to migrate to In-Memory tables without a great deal of effort,” he said.
In addition, Microsoft raised the size limit on memory-optimized tables to 2 TB, and those memory-optimized tables can be edited. Another important SQL Server 2016 feature is the ability to combine In-Memory OLTP and columnstore indexes on a single table.
“It’s not for everyone, but there are cases where it would be great to have real-time statistics and trends available from live, transactional data,” Snodgrass said. “Right now the process is time-delayed, since it usually requires grabbing transactions at a point in time and performing analysis somewhere other than on the transactional table.”
However, Snodgrass cautioned, DBAs shouldn’t try this without the proper infrastructure. “You’d better have beefy equipment and failover configured before trying this,” he said.
PolyBase, which provides the ability to access unstructured data in Hadoop, has been in specialized versions of SQL Server since 2012. It will be included in SQL Server 2016 Enterprise edition. That means organizations that didn’t want to spend the money on big equipment can now use existing SQL Server installations to pull unstructured data, Snodgrass said.
“Of course, that doesn’t immediately solve the problem of deploying Hadoop, but it is good for the SQL guys,” he added.
JSON, live queries and analytics
JSON support is an important feature because it allows users to read and write JSON-based documents. This provides a controlled gateway for sharing organizational data with more mobile platforms. Companies have struggled to write database apps for mobile devices, because the data storage options weren’t compatible with on-premises data platforms, Snodgrass said.
“This provides a much easier method for transporting that data between mobile/Web solutions and relational database applications,” he said.
Other SQL Server 2016 features users are excited about are Query Store, Live Query Statistics and Live Query Plans (in Management Studio), according to Gareth Swanepoel, a senior data platform and BI consultant at Pragmatic Works Inc., a SQL Server software and training provider in Middleburg, Fla.
“These [Query features] represent a major improvement to performance tuning on a system,” Swanepoel said. “DBAs will have access to vastly enhanced metrics.”
In addition, SQL Server Management Studio’s release schedule has been separated from the main SQL Server releases, and it will be updated more frequently than before.
Perhaps least impressive of the new SQL Server 2016 features, according to Snodgrass, is SQL Server R Services, which supports advanced analytics with the R programming language.
“The ability to incorporate R scripts in stored procedures is interesting, but the audience is very limited and other tools out there do a good job of this,” he said. “It’s important for the long term, but I suspect adoption will be slow in the beginning.”
SQL Server 2016 editions will include Enterprise, Standard, Express and Developer. The SQL Server 2016 Developer edition, with the full capabilities of the latest SQL Server release, will be free.
