With the release of every version of SQL Server comes along with the new and improved features in SQL Server 2016. The main motive of the Microsoft team is to make database administrators able to administer the new features of each release. SQL Server 2016 is the recent and latest release of SQL Server version that has multiple features supported. The content will be focusing on studying the new features in SQL Server 2016.
What Is New Features In SQL Server 2016?
Microsoft officially announces the release of SQL Server 2016 at a conference during May 2015. The latest version of SQL Server i.e., SQL Server 2016 allows its users with a reliable database platform with every desired features built in like advanced in-memory performance, unique security systems, high availability, distinct analytics etc. Several features have been included, which we will be discussing in the next paragraph.
In-Depth Study of Some New Features in SQL Server 2016
- Always Encrypted
- Dynamic Data Masking
- Support for JSON
- Row Level Security
- PolyBase
- Query Store
- Stretch Database
- R in SQL Server
One new feature in SQL Server 2016 is Always Encrypted. This feature has been aimed to make the data that is being used more secured. It focuses on keeping the data encrypted even at the time of transaction and computation and gives access only to the clients by providing keys for decryption. Enabling Always Encrypted Feature present under the Security Tab will make the SQL Server data always encrypted within the SQL Server. The encrypted data will be available only to the client application owners using the keys. These keys is never passed to the SQL Server, which means Database or System administrators cannot peek on the client sensitive data. The clients can peacefully store their confidential data in the cloud-managed database with the Always Encrypted Feature enabled free of unauthorized access.
This new feature in SQL Server 2016, is for those purposes where only some part of the SQL Server data needs to be hidden for security while some data remains visible to other users. Enabling Dynamic Data Masking feature in SQL Server limits sensitive data exposure by masking it to unauthorized users. It is also a part of preventing unauthorized access to sensitive data by allowing users to decide how much percentage of data should be revealed with less impact on application layer. This feature can be set up using the masking rules defined by the users. Example: while storing the credit card numbers or phone numbers, rules can be setup like first four digits should be visible and remaining digits should be hidden.
SQL Server 2016 has a new feature that provides support for JSON, Java Script Object Notation. This feature allows the JSON data to be exchanged between applications and SQL Server database engine. JSON is based on subset of JavaScript programming language and is a human readable that is also easy for computer to parse and generate. With this support, Microsoft allows SQL Server to parse JSON formatted data allowing it to store in a relation format. In addition to this, it also allows relational data to turn it into JSON formatted data. This feature has been aimed to make the applications easy to exchange JSON data with SQL Server.
This new feature in SQL Server 2016 provides Row level Security that allows SQL Server to restrict which users can view what data in a table based on a function. It has been helpful in case of multi-tenant environments where data access needs to be keep in limited access based on customer ID. Restriction of Rows can be done by filter predicates defined in inline table-value function and security rules will make the filter predicates execute for every SELECT or DELETE operation. Enabling the feature at database layer does not require application developers to maintain code for restricting data from some logins while allowing other logins to access all data. Though this feature has been already supported by other databases, SQL Server introduces it for the first time considering the need to provide security to row data.
With the increasing usage of Hadoop and Big Data, SQL Server 2016 introduces PolyBase that acts as a SQL Server Connector to Hadoop and Azure Blob Storage to its data. This feature allows usage of transact SQL commands to query Hadoop or SQL Azure Blob Storage. The Semi-structured data present in Hadoop can be joined with relational data from SQL server using adhoc queries with this feature. Data can be taken from Hadoop without having much knowledge on how Hadoop works. PolyBase is helpful in accessing distributed semi-structured data, as most of the organizations spread data across multiple locations.
Considering the issues faced by organizations while upgrading newer versions of SQL Server due to changes in Query Optimizer leading to negative impact on performance, Query Store feature has been introduced. This feature keeps records of the history of all query execution plans that has been rolled out of the plan cache. It saves historical execution plans along with the query statistics. It identifies queries that has gotten slower performance and use the newer plan or the older one that is better if required.
This new feature in SQL Server helps in reducing the storage costs by storing some part of data in Azure SQL Database in the cloud. The most frequently accessed data will be stored in On- Premise server and remaining data is moved to Azure SQL database. When user tries to run a query for accessing active or historical data in Stretch database, the database engine will seamlessly queries both the On-Premise database and Azure SQL database and results are displayed as if it has come from single source. This feature allows hybrid cloud-storage making database administrators archive data to cheaper storage media without modifying application code.
SQL Server 2016 comes with the integration of R that supports advance analytics against big data inside SQL Server. As SQL Server R Services is associated the R language with SQL Server, users can keep analytics closer to data reducing the costs & security threats related to data movement. Enabling this feature removes the need of data scientists to export the SQL server data for R processing into SQL Server. It directly allows them to take existing R code and run it inside the SQL Server database engine.
Conclusion
As SQL Server 2016 supports many improved features, it is difficult to include all the features. Therefore, in this article we have been introduced only eight new features in SQL Server 2016.
EmoticonEmoticon