In SQL Server 2016, Microsoft introduced some significant enhancements, especially with Data Encryption. Here are some new features in SQL Server 2016.
1. Data Encryption
SQL Server 2014 enables data encryption features against the data stored in the sql database. If we want to access the encrypted data, then we only hose data using SQL server database. This feature always provide a control to the owner of SQL Server that who are eligible and who can access the encrypted data. It only provide access to those users who have the encryption key. It never pass to SQL Sever. This feature also provide us a secure database in cloud environment also.
2. Dynamic Data Masking
If we want to secure our SQL server database in such a way that some user can access the entire database and at the same time other users can get only an obscured version of the database, then we need to use this features. With this features, we can obscured confidential column data of a particular table in SQL Server so that other user can’t get those columns data. Suppose, we want to store that DOB of every employee in database, and want that some users when view those employee data, they can see only the last two digits of the DOB. For this, we can set a dynamic masking rule, so that when particular view those data, only last two digit will be visible in from that users.
3. JSON Support
What is JSON, we know all as it a javascript objects. In SQL server 2016, we can transfer JSON data between application and SQL Server directly. By providing this features, microsoft provide us a ability which can parse the json data into relational data and can store into the table and also, vice versa.
4. Multiple TempDB
In SQL Server 2016, we can configure multiple tempdb file when installing the SQL Server instance. By adding this features, we need not to add manual temp db file.
5. Polybase
This feature allows us to query distributed data in SQL Sever database. With this feature, we can use transaction statement to query some data from Hadoop or SQL Azure blob. Using this features, we can write complex query to get data been distributed between SQL Server and non relational database like Hadoop, etc.
6. Row Level Security
With this feature, SQL server database engine can restrict any user to access row wise data as per the user login credentials. Restricting rows will be done by filter predicates defined in inline table value function. Security policies will ensure the filter predicates get executed for every SELECT or DELETE operation.
7. Stretch Database
The Stretch Database feature provides us a method to stretch the data storage of our On-Premise database to Azure SQL Database. This feature will make it easy for DBA’s to archive information to a cheaper storage media without having to change any actual application code. By doing this you should be able to maximize performance on those active On-Premise queries.