Feature s of MS SQL SERVER 2016
- Always Encrypted: –
With the Always Encrypted feature enabled, SQL Server data will always be encrypted within SQL Server. Access to encrypted data will only be available to the applications calling SQL Server. Always Encrypted enables client application owners to control who gets access to see their applications confidential data. It does this by allowing the client application to be the one that has the encryption key. That encryption key is never passed to SQL Server. By doing this you can keep those nosey Database or Windows Administrators from poking around sensitive client application data In-Flight or At-Rest. This feature will now allow you to sleep at night knowing your confidential data stored in a cloud managed database is always encrypted and out of the eyes of your cloud provider.
- JSON Support: –
JSON stands for Java Script Object Notation. With SQL Server 2016, interchange JSON data between applications and the SQL Server database engine. By adding this support Microsoft has provided SQL Server the ability to parse JSON formatted data so it can be stored in a relation format. Additionally, with JSON support you can take relational data, and turn it into JSON formatted data. Microsoft has also added some new functions to provided support for querying JSON data stored in SQL Server. Having these additional JSON features built into SQL Server should make it easier for applications to exchange JSON data with SQL Server.
- PolyBase: –
PolyBase isn’t a new feature overall, having been part of the Microsoft Analytics Platform Service previously, but it is new to SQL Server in the 2016 release.
PolyBase allows you to query distributed data sets. With the introduction of PolyBase you will be able to use T-SQL statements to query Hadoop, or SQL Azure blob storage. By using PolyBase you can now write adhoc queries to join relational data from SQL Server with semi-structured data stored in Hadoop, or SQL Azure blob storage. This allows you to get data from Hadoop without knowing the internals of Hadoop. Additionally, you can leverage SQL Server’s on the fly column store indexing to optimize your queries against semi-structured data. As organizations spread data across many distributed locations, PolyBase will be a solution for them to leverage SQL Server technology to access their distributed semi-structured data.
- Stretch Database: –
The Stretch Database feature provides you a method to stretch the storage of your On-Premise database to Azure SQL Database. But having the stretch database feature allows you to have your most frequently accessed data stored On-Premise, while your less accessed data is off-site in an Azure SQL databases. When you enable a database to “stretch” the older data starts moving over to the Azure SQL database behind the scenes. When you need to run a query that might access active and historical information in a “stretched” database the database engine seamlessly queries both the On-Premise database as well as Azure SQL database and returns the results to you as if they had come from a single source. 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.
- Dynamic Data Masking: –
Dynamic data masking is a new security feature added to SQL Server 2016. It lets SQL Server database administrators hide some of their data from all but specific privileged users. Ordinary users will see some of the data blanked out, while the users with full access privileges can view all of the data. The data masking occurs during the output process based on the status of the person trying to access the data.
- Query Store: –
If you are into examining execution plans than you will like the new Query Store feature. Currently in versions of SQL Server prior to 2016 you can see existing execution plans by using dynamic management views (DMVs). But, the DMVs only allow you to see the plans that are actively in the plan cache. You can’t see any history for plans once they are rolled out of the plan cache. With the Query Store feature, SQL Server now saves historical execution plans. Not only that but it also saves the query statistics that go along with those historical plans. This is a great addition and will allow you to now track execution plans performance for your queries over time.
- In-Memory Enhancements: –
SQL Server 2014 introduced the concept of in-memory tables. These were optimally designed for high-speed loading of data with no locking issues or high-volume session state issues. While this feature sounded great on paper, there were a lot of limitations particularly around constraints and procedures. In SQL Server 2016, this feature is vastly improved, supporting foreign keys, check and unique constraints and parallelism. Additionally, tables up to 2TB are now supported (up from 256GB). Another part of in-memory is column store indexes, which are commonly used in data warehouse workloads. This feature was introduced in SQL 2012 and has been enhanced in each version since. In 2016 it receives some enhancements around sorting and better support with AlwaysOn Availability Groups.
- R Comes to SQL Server: –
Support for in-database analytics with R-integration SQL Server 2016 provides support for advanced analytics to facilitate a deeper insight into your data. This is the first version of Microsoft’s flagship database product to have an integrated support for Revolution R for statistical analysis of data.
- Mobile Reports: –
Reporting Services mobile reports are dedicated reports optimized for a wide variety of form factors and provide an optimal experience for users accessing reports on mobile devices. Mobile reports feature an assortment of visualizations, from time, category, and comparison charts, to treemaps and custom maps. Connect your mobile reports to a range of data sources, including on-premises SQL Server Analysis Services multidimensional and tabular data. Lay out your mobile reports on a design surface with adjusting grid rows and columns, and flexible mobile report elements that scale well to any screen size. Then save these mobile reports to a Reporting Service server, and view and interact with them in a browser or in the Power BI mobile app on iPads, iPhones, Android phones and Windows 10 devices.