SQL Server 2016 New Features
1:- Always Encrypted
Starting off, we have always encrypted. This feature enables a client side encryption of table data. This is similar to column encryption, but unlike column encryption, the data type does not need to be varbinary.
This feature requires a driver on the client applications to communicate with the database. Encryption occurs at the client side, so the data is not plain text during transmission.
Another difference from column encryption (as a SQL only technology) is that the encryption key may either be deterministic or randomized. The benefit of deterministic is that it allows for the indexing of column data so that performance of queries that need to filter on the column are more efficient.
2:- Row Level Security
Row Level Security allows tables to be configures where some users may work with only a subset of rows in the table. This function requires setup on the DB side as well as coding requirements on the developer side. Working with this feature requires setting up inline table functions and security policies on tables that govern the filtering/blocking at the row level.
Some of the issues that may arise from this feature is inconsistent application functionality, since with the security policies set up, it’s possible to create an instance where a user may update/insert a record, but then not be able to view it.
3:- Dynamic Data Masking
This, as the name suggests, allows for data to be stored normally, but only certain users may see the information unmasked. Masking is performed as part of a table’s DDL; assigning masking at a column level.
There are new permissions to allow granular rights of unmasking to certain user groups. However, dbo always have unmasking rights. All queries accessing masked columns are automatically masked/unmasked depending of the permission of the user making the query.
Masking does not alter the underlying data, therefore, adding masking to columns is a table metadata change.
4:- Availability Groups
A few more improvements were made in Availability Groups. Automatic failover may now be performed over a set of 3 nodes, instead of 2. As well log transport to synchronize between the primary and secondary replica(s) was streamlined. In addition, build in load balancing of the replicas is possible. (2014 and earlier, all traffic was directed to one node unless it was unavailable, or 3rd party products needed to be used).
5 :- TempDB Enchancements
On installation tempdb will have created the recommended number of files (1 per logical processor up to 8). This can still be modified on installation to something besides the default. As well, trace flags 1117 and 1118 have been eliminated as their functionality has been built into tempdb. (1117 was uniform data file growth, 1118 was full extent allocation)
6 :- Query Store
One of the best new features, from a DBA’s perspective. Query Store is a per database implemented item. It is meant to greatly increase the ease of finding performance issues and troubleshooting.
The query store has two components, a plan store that persists the execution plans, and a run-time stats store, that persists the stats surrounding query execution (CPU, I/O, memory etc).
7:- R integration
R is a programming language widely used by data scientists for advanced analytics. SQL Server R Services is a result of a Microsoft acquisition in 2015. For SQL 2016, R services have been integrated into the SQL Server platform. R code may be executed directory in a sql database. This adds to te workload of the server, but allows to greater security and performance because data movement is minimized.
8:- SSRS
There are a number of improvement in 2016 (compared with almost no change in 2014).
Mobile reports are now supported with the integration of another Microsoft acquisition. Mobile reports and standard reports may be viewed through the same web portal interface of the report server.
As well, enhancements have been created to make administering ownership and subscriptions easier.
9 :- Polybase
Polybase is s transparent access layer that facilitates connectivity between SQL and Hadoop data sources. It’s purpose is to merge big data into SQL platforms. This integrations means you can execute T-SQL queries against this platform without knowing Map/Reduce, Hive or any other Hadoop related tools.
Hope it will be helpful. I'll try to write more on these features in detail in coming days.
Thanks
