It’s been a long time since my last blog post, but evidently from today’s blog post headline you can only imagine where I was investing my time. Yes, SQL Server 2016, that is exactly where I had been dwelling all along. The RC3 was released recently around mid April meaning that we are ever so close to the RTM release as this was the final release candidate. With this, today I want to talk about top 10 features in SQL Server 2016 that has me excited from a developer as well as a DBA prospective. Let’s take a look at what they are:
- Always Encrypted
With the Always Encrypted feature enabled the 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 the SQL Server. This feature will now allow us to sleep at night knowing that our confidential data stored in a cloud managed database is always encrypted and out of the eyes of our cloud provider.
- Dynamic Data Masking
If you are interested in securing your confidential data so that some people can see it, while other people get an obscured version of confidential data then you might be interested in this feature. With dynamic data masking we can obscure confidential columns of data in a table to SQL Server for users that are not authorized to see all of the data. With this feature we can identify how the data will be obscured by defining masking rules, so unauthorized logins can only read data that they are allowed, whereas authorized logins can see all of the data.
- JSON Support
With SQL Server 2016 we can now 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 we can take relational data, and turn it into JSON formatted data. Microsoft has also added some new functions to provide support for querying JSON data stored in SQL Server.
- Multiple TempDB Database Files Configuration
It has been a best practice for a while to have more than one tempDB data file if we are running on a multi-core machine. In the past, up until SQL Server 2014, we always had to manually add the additional tempdb data files after we installed SQL Server. With SQL Server 2016 we can now configure the number of tempDB files we need while we are installing SQL Server.
This feature allows us to query distributed data sets. With the introduction of PolyBase we will be able to use Transact SQL statements to query Hadoop, or SQL Azure blob storage. By using PolyBase we 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 us to get data from Hadoop without knowing the internals of Hadoop. Additionally, we can leverage SQL Server’s on the fly column store indexing to optimize our queries against semi-structured data.
- Query Store
Now, this is one of the most, if not the most talked about feature in the new SQL Server 2016. Currently in version of SQL Server prior to 2016, we can see existing execution plans by using dynamic management views (DMVs). But, the DMVs only allow us to see the plans that are actively in the plan cache. We 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 us to now track execution plans performance for our queries over time.
- Row Level Security
With Row level security the SQL Database Engine will be able to restrict access to row data, based on a SQL Server login. Restricting rows will be done by filter predicates defined in inline table value function. Security policies will ensure the filer predicates get executed for every SELECT or DELETE operation. Implementing row level security at the database layer means application developers will no longer need to maintain code to restrict data from some logins, while allowing other logins to access all the data.
- R comes to SQL Server
With Microsoft’s purchase of the Revolution Analytics, they are now able to incorporate R to support advance analysis against big data right inside of SQL Server. By incorporating R processing into SQL Server, data scientists will be able to take their existing R code and run it right inside the SQL Server database engine. This will eliminate the need to export our SQL Server data in order to perform R processing against it.
- Stretch Database
This feature provides us a method to stretch the storage of our on-Premise database to Azure SQL Database. The interesting part of this feature is that having the stretch database feature allows us to have have our most frequently accessed data stored On-Premise, while our less accessed data is off-site in an Azure SQL database. When we enable a database to stretch, the older data starts moving over to the Azure SQL database behind the scenes. When we 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 us as if they had come from a single source. This feature will surely make it easy for DBA’s to archive information to a cheaper storage media without having to change any actual application code
- Temporal Table
Finally, a Temporal table is a table that holds old version of rows within a base table. By having temporal tables SQL Server can automatically manage moving old row version to the temporal table very time a row in the base table is updated. The temporal table is physically a different table than the base table, but is linked to the base table. If you have been building or plan to build your own method to managing row versioning then you might want to check out this new feature supported by SQL Server 2016.
SQL Server 2016 has many new features and I have talked about only the top 10 among them that I felt would have the most impact on their users. Some of the new features are enhancements to existing features, while others are entirely new features. And while this write-up only discusses some of those features in theory, I will definitely followup to this blog post with more details regarding the new features with examples and code samples as well.
With that, let me know, which new feature are you most interested in SQL Server 2016? Are there any new feature that you think SQL Server missed out on? or would be better off without? Let me know your thoughts on the new SQL Server 2016.