With the recent announcement of SQL Server 2016, several promising new features have been identified that will help make your administration easier and more powerful.
1. Always Encryption
SQL Server 2016 is upgraded with the Always Encrypted feature, which enables you to encrypt an entire database. Those applications which call SQL Server will be able to access this encrypted data. This Always Encrypted feature allows client application owners to manage which application should get the access the confidential data in the database. This is done by letting the client application have the encryption key which is never passed to server.
2. SQL Server 2016 JSON Support
The latest version of SQL Server adds Java Script Object Notation (JSON). Many databases have added this support and SQL 2016 now supports this function like XML support is built in FOR JSON and OPENJSON. This facility allows parsing JSON formatted data which can be stored in relation format.
3. Dynamic Data Masking
Dynamic Data Masking allows users to mask the confidential columns of server data in a table for users who are not authorized for viewing this data. Users can define how the data should be masked according to the confidentiality of the data. For instance, confidential data is stored in a table and you want your staff to view only last few digits of credit card numbers, then you can adjust the settings accordingly. Those users with full authorization can view complete details.
4. Multiple TempDB
If your server is based on multi-core machine, it is always better to have multiple tempdb data files. In previous versions, it was necessary to add the additional tempdb data files after server installation. But in this latest version, users can configure the number of tempdb files while installing SQL Server. This means that you will not need to manually add additional tempdb files.
PolyBase is another significant addition which allows querying distributed data sets. This will allow you to use T-SQL statements to query Hadoop. Getting data from Hadoop without knowing the settings of Hadoop becomes easier. As data is stored across many applications and locations, PolyBase can be a better solution to leverage Server technology for accessing this data.
6. Row Level Security
SQL Server 2016 has restricted the access to row data using this row level security. This undoubtedly increases the security and allows the access to authorized users only. This row restriction is done by filter predicates, which is defined in inline table value function. This implementation of row security will reduce the need of maintaining code to restrict data from certain logins. Using this SQL Server 2016 new feature, users can make sure that when anyone queries a table comprising row level security they will not know if any rows of data was filtered out.
7. Temporal Table
Temporal table is defined as a table holding old versions of rows within a base table. If the database has temporal tables, SQL Server can automatically manage shifting old row versions to the temporal table whenever it is updated. Users who are planning to manage row versioning might find this modification quite helpful.