SQL - Amazing features in SQL Server 2016

Microsoft SQL Server 2016 is now generally available to everyone from Jun 2016. It is fully loaded with several new features, including more speed, more security and more value. It provides a secure, scalable database platform that has everything built in, from advanced analytics to unparalleled in-memory performance.

For example, SQL Server R Services in SQL Server 2016 an amazing offering for analysing our data with R while being able to scale, integrate with applications in production and keep your costs down. SQL Server R Services provides familiar Transact-SQL syntax for interfacing your production applications with calls to the R run-time that retrieve predictions and visuals. 

Mobile BI apps provide live, interactive, mobile access to our important business information and interact with our data easily in a touch-optimized experience with the Power BI native Windows, iOS, and Android apps.

Microsoft SQL Server 2016 is loaded with the following new features:

Highest Performing Data Warehouse: Provides a trusted infrastructure that gives users confidence in the credibility and consistency of the data. Query both traditional relational data and these new data types with common T-SQL commands using Polybase. 

Scale to petabytes of data for enterprise-grade relational data warehousing and integrate with non-relational sources like Hadoop which is using scale out, massively parallel processing from Microsoft Analytics Platform System (APS). 

It supports small data marts to large enterprise data warehouses while reducing storage needs with enhanced data compression. 

It Enable users to get results from their queries in near real-time with streaming technologies. Queries that took hours can be reduced to minutes or seconds through in-memory.

Data warehouse and big data solutions from Microsoft provide a trusted infrastructure that can handle all types of data, and scale from terabytes to petabytes, with real-time performance.

R Services: In-Database Advanced Analytics: In support of Enterprise Scale Data Science, R Services is introduced as a new feature of SQL Server 2016. By adding this feature, enterprises are able to perform high level advanced analytical calculations at the database level that enterprises will derive the most benefit. 
The incorporation of R is important for data scientists because they won't have to take code out of the database in order to run the R programming language. R allows the user to run queries on server data.
SQL Server R Services is a set of SQL Server tools and technologies that offer superior performance, security, reliability and manageability.  In SQL Server 2016, SQL Server R Services provides both server and client components.
Open Source R packages run single-threaded and can therefore only consume datasets that fit into available memory on the local machine.
R Services support in-database analytics allowing us to keep the data in SQL Server 2016 and have applications call R scripts via a T-SQL system stored procedure. 

Always Encrypted:  means protect your data at rest and in motion. As we know that SQL Server has long supported both column-level encryption, encryption at rest, and encryption in transit. This is the new capability of SQL Server 2016 where data will always be encrypted within SQL Server and data stays encrypted in transit, at rest and while it is alive in the database. 

This feature enables client application owners to control who gets the access to see their application's confidential data. This action is covered by the encryption key which is never passed to SQL Server. The best thing is that all the things work without impacting the database performances which is the biggest concern for any enterprise. 

By using this feature, you will be aware that your confidential data stored in a cloud managed database is encrypted which provide a much better security layer. 

Encrypted data will be accessible through the business applications which is calling SQL Server.

PolyBase: A technology that accesses and combines both non-relational and relational data, all from within SQL Server. It allows you to run queries on external data in Hadoop or Azure blob storage. The queries are optimised to push computation to Hadoop via T-SQL programs. 

Users are storing data in cost-effective distributed and scalable systems, such as Hadoop. Polybase makes it easy to query the data by using T-SQL.

Azure blob storage is a convenient place to store data for use by Azure services. PolyBase makes it easy to access the data by using T-SQL.

Leverage the speed of Microsoft SQL's columnstore technology and analysis capabilities by importing data from Hadoop or Azure blob storage into relational tables where no need for a separate ETL or import tool.

Archive data to Hadoop or Azure blob storage to achieve cost-effective storage and keep it online for easy access.

Use PolyBase with Microsoft’s business intelligence and analysis stack, or use any third party tools that is compatible with SQL Server.

Stretch Database: SQL Server Stretch Database lets you dynamically stretch warm and cold transactional data from Microsoft SQL Server 2016 to Azure. With Stretch Database feature, we have the capability to provide longer data retention times without breaking the bank. Rather than scaling expensive, on-premises storage, stretch data to the cloud; Azure storage can be up to 80 percent less expensive than adding more enterprise storage. 

Seamlessly access SQL Server data regardless of whether it is on-premises or stretched to the cloud. We have  advanced security options those also work with Stretch Database and move it into Azure SQL Database in the cloud in a secure fashion.

With Stretch Database, backups for your on-premises data run faster and complete more easily than they did on the un-stretched data and easy to turn on Stretch Database from SQL Server Management studio. Use the Azure portal to configure additional settings and choose a performance level, scaling up or down as needed while maintaining control over cost.

Real-Time Operational analytics- Instead of requiring the extraction of data to an outside analytical application by using different ETLs approaches, business organisation can analyze transactions on a real-time basis from within the database itself. 

To provide the real time analytics in SQL Server 2016, you can enable Non-Clustered ColumnStore Index (NCCI) on your operational data table and no need to do any changes on your operation application.  

If you create directly NCCI on your operation database that means there are no transactions will happen. To avoid these things, SQL Server is introducing them as Updateable NCCI or CCI on your operational tables. 

In SQL Server 2016, you will have the ability to create NCCI (Non-Cluster Columnstore Index) on the In-Memory Table also. In this way, you would have the both technologies such as In-memory table as well as NCCI on your In-memory tables also which will provide us real time data analytics functionality also. This is the uniqueness feature which is coming with SQL Server 2016.
The main thing is that to improve the performance, you need to define the limits where you could say that this data could not be available for NCCI for HOT/WARM (Predicate). The most important point in SQL Server 2016, NCCI and CCI will share the same code means same performances is applicable for the both indexes.

End To End Mobile BI: This is the best feature to visualise your data on the e-devices within Modern data visualisation techniques such as mobiles. In fact, SQL Server 2016 delivers built-in mobile BI capabilities. It provides the real-time query capabilities to business professionals to get access to insights and intuitive reports optimised for their choice of platform, from desktops to major mobile devices with BI and analytics that seamlessly combine existing enterprise data, external data, and unstructured big data. 

Deliver the power of data to every user in your organization with a comprehensive, end-to-end BI and analytics solution that’s built into SQL Server. 

It will extend our existing BI and reporting investments to the cloud and transform our data into intelligent action with built-in data modeling and advanced analytics models and deploy them in an on-premises database, in the cloud, or even to Linux, Hadoop, and Teradata systems and easily access to thousands of R scripts and models in CRAN (comprehensive R archival network)

At a glance

SQL Server R Services integrates the R language with SQL Server, making it easy to build, retrain, and score models by calling Transact-SQL stored procedures and it is capable to provides multi-platform, scalable support for R in the enterprise, and supports data sources such as Hadoop and Teradata.

With SQL Server in-memory columnstore, we’ll get up to 100x faster query performance. And with the Polybase capabilities in Microsoft’s Analytics Platform System (APS), and in SQL Server 2016, we’ll have the ability to query across relational and non-relational sources like Hadoop. 

It provides the powerful modeling with 250+ built-in analytical function to visualise our data on the user friendly devices with online and offline access features. We can access our data anywhere, anytime.

Backup is automatic for stretched data, further reducing the amount of time you spend on maintenance.

Reference: Microsoft and Microsoft  Azure