SQL Server 2016: Broader access, better reporting, faster analytics

We want to make it easier for businesses to use their data. Otherwise, what’s the point? In SQL Server 2016 you’ll find a virtual tool chest full of features, all with one primary goal: unlocking your data and helping you create new ways of analyzing, visualizing and sharing it.

Building data-rich biz apps

The advent of machine learning and natural language processing made it easier to analyze unstructured data. The challenge was effectively integrating it with structured data, leading to more meaningful discoveries.

Enter SQL Server PolyBase, a feature in SQL Server 2014 that was specific to the Microsoft Analytics Platform System, through which you could access data in a Hadoop Distributed File System. With SQL Server 2016 we cut the strings, making it possible to query data in Hadoop, as well as Azure Blob Storage. Now you can combine the results of your findings with relational data stored in SQL Server.

But that’s just the beginning. PolyBase becomes a data-rich foundation upon which to build powerful business applications. It dynamically creates columnar tables for your structured data, parallelizes the extraction of data from Hadoop and Azure—even pushing data to Hadoop clusters for additional processing.

Meanwhile, on the front end users can continue using their apps, consuming data and discovering new insights, all without needing to understand the finer points of data base management.

You can install PolyBase in one of two ways: using the SQL Server Installation Wizard or from the command-prompt window. See below for an example of the installation script.

Installing PolyBase

Subsequently, you’ll also need to configure to PolyBase to connect either to Hadoop or Azure Blog Storage, using one of the following values:

  • 0 Disable Hadoop connectivity
  • 1 Hortonworks HDP 1.3 on Windows Server and blob storage
  • 2 Hortonworks HDP 1.3 on Linux
  • 3 Cloudera CDH 4.3 on Linux
  • 4 Hortonworks HDP 2.0 on Windows Server and blob storage
  • 5 Hortonworks HDP 2.0 on Linux
  • 6 Cloudera 5.1 on Linux
  • 7 Hortonworks 2.1 and 2.2 on Linux, Hortonworks 2.2 on Windows Server and blob storage

To achieve optimum app performance, even when dealing with larger datasets, consider creating a PolyBase scale-out group. PolyBase scale-out groups include one or more compute nodes, each of which includes a database engine and database movement service, as well as a head node that includes the SQL Server database engine, the PolyBase engine service and the PolyBase movement service. The following diagram gives you a better sense of how PolyBase scale-out groups can keep the data flowing.

Polybase scale out groups

Advanced analytics, at scale

For the first time, SQL Server 2016 gives you the ability to work directly with R, introducing a range of new capabilities around advanced analytics, data exploration and modeling.

SQL Server R Services provides a platform for developing intelligent applications that uncover new insights. You can use the rich and powerful R language and the many open source packages to create models and generate predictions using your SQL Server data. Because SQL Server R Services integrates the R language with SQL Server, you can keep analytics close to the data and eliminate the costs and security risks associated with data movement.

With SQL Server R Services you can tap into the robust capabilities of R and combine them with a comprehensive set of SQL Server tools and technologies that offer superior performance, security, reliability and manageability. You can deploy R solutions using convenient, familiar tools, and your production applications can call the R runtime and retrieve predictions and visuals using Transact-SQL. With Enterprise Edition, you also get the Scale R libraries to overcome R’s inherent performance and scale limitations.

SQL Server R Services

To store a procedure you must first serialize it as a hexadecimal string, which is sent to the server and stored in a varbinary(max) column. Check out the sample script:

Storing an R procedure

Making data mobile

These days, there is so much that can be done with a smart phone and an Internet connection. As such, SQL Server 2016 Reporting Services has made it easier to build concise and consumable mobile reports. And with the Power BI mobile apps you can view, interact with and share data through the Power BI dashboard and SQL Server Reporting Services web portal. Check out this Power BI blog post for the unique capabilities of Power BI for iOS, Power BI app for Android phones and Power BI mobile app for Windows 10.

With the Mobile Report Publisher you can create reports based on shared data sources. An extensive array of charts, gauges, grids and other visuals help users gain greater clarity when consuming data via their mobile device. And with navigator elements, users can filter data based on time, date, location or other pre-determined values.

SQL Server Mobile Report Publisher

When designing a report, the Mobile Report Publisher optimizes the report for mobile devices and automatically populates it based on simulated data, allowing you to review with your business user, find the right layout before you connect the report to the data. Once ready, you can import data either from a local file, or from a report server. The first time you select the report server, the “Connect to a Server” prompt appears, at which point you’ll need to enter the following information:

  • In the Server Address Box: <servername>/reports, where servername is the name of the server hosting Reporting Services.
    NOTE: If the report server is not configured to use Secure Socket Layer, clear the Use Secure Connection box.
    NOTE: If the data set uses a database login, or if you want to use a different Windows account, clear the Use Current Windows Account box and supply the appropriate credentials.

Summary

SQL Server 2016 offers a range of new features that transform information into insight and help your employees to make more decisive and strategic decisions, wherever they are. To learn more about the features in SQL Server 2016, download the SQL Server 2016 e-book, or visit the  SQL Server 2016 product page.