What’s new in SQL 2016? Detail about recent SQL 2016

SQL, a technology which has made an embark in database and is reaching out new heights with time progression, has recently come up with new version 2016. But before talking in detail about recent SQL 2016, let me start with little older times. In the year 1989 came the first version of SQL, launched by Microsoft as a relational database server meant for data storage and management.

After the first launch, there was a continuous progress in SQL features leading to release of multiple versions right from SQL Server 1.0 to SQL Server 2014. These versions have updated features which improve it qualities and soon it got accepted by large masses as a tool for data storage, management, analysis, reporting and Business Intelligence.

And now recently Microsoft has released SQL Server 2016 on June 1st, 2016, with an initial build number of 13.0.1601.5. which can be downloaded from TechNet Evaluation Center, MSDN Subscriber Downloads.

Multiple features present in SQL 2016 are like end-to-end mobile business intelligence on any device, in-database advanced analytics, in-memory capabilities optimized for all workloads, and a consistent experience from on-premises to the cloud. Below diagram from Microsoft datasheet describes major enhancement of SQL server 2016.

Source : Microsoft

Always Encrypted

One of the major security upgrade in SQL Server 2016 is Always Encrypted. Prior to launch of this feature, we have other features for security like Transparent Data Encryption (TDE) which were good. But this technology protects the data in rest as well as in motion (sent through the network). Always Encrypted also secures the sensitive data such as Secret card numbers or other identification numbers stored in database. It allows you to encrypt the sensitive data inside the client application itself and doesn’t reveal encryption keys to database engine also.

One other important feature is unlike TDE, we can encrypt particular column instead of encrypting complete database. This encryption includes two keys (Column Master Key and Column Encryption Key) which are depicted in below image from ‘Msdn’ (https://msdn.microsoft.com/en-us/library/mt163865.aspx)
Source : Microsoft

Column Master Key is stored in Application Machine and is used to protect the Column Encryption Key while Column Encryption Key is used to encrypt/decrypt the Always encrypted column.
With Always Encrypted, data is encrypted at the application layer via ADO.NET. This means you can encrypt your confidential data with your .NET application prior to the data being sent across the network to SQL Server.

Mobile Reports

Mobile reports feature enables you to develop multiple reports which are adaptable to different screen shapes, orientations and sizes. With this feature, SQL Server 2016 have enhanced from traditional Paginated Reports, now you can quickly create beautifully modern reports by using modern styles for charts, gauges, maps and other data visualizations. Two new chart types has been introduced – Tree maps and Sunburst Charts for visualizing hierarchical information.

SQL Server 2016 Reporting Services support Responsive Mobile Reports and also comes with modern web portal where you can view and manage all your reports.

Stretch Database

Stretch database feature enables you to store particular portions of your database into cloud. So you can migrate your cold data transparently to azure.
After you enable Stretch Database for a SQL Server instance, a database, and at least one table, Stretch Database silently begins to migrate your cold data to Azure.

1. If you store cold data in a separate table, you can migrate the entire table.
2. If your table contains both hot and cold data, you can specify a filter function to select the rows to migrate.

Multiple benefits of Stretch database include:

• Provides cost-effective transfer of your data to azure and your data is always online and available for query.
• You can access your data (warm and cold) anytime with queries or application without any change whether your data is on premise or stretched to cloud.
• Streamline and reduce your on-premises maintenance and storage for data.
• Keeps your data secure even during migration with the use of Always Encrypted.

Here are some other major improvements in SQL 2016:

• New Performance Enhancements – In-memory performance has been enhanced upto 30x faster transaction.
  • Query Data Store – Stores history of query execution plans with their performance data so now no worries during upgradation and it makes performance troubleshooting easy.
  • In-Memory OLTP enhancement – The limitations of SQL Server 2014 concept of in-memory tables have been lifted, with support of foreign keys, check and unique constraints and parallelism. Also tables up to 2TB is supported (previously 256GB).
  • Temporal Database – It automatically track history of rows.
• Row Level Security – Now administrator can define user rights for table rows.
• High Availability Enhancement – Always on feature has been enhanced compare to SQL Server 2014.
  • Now support up to three secondary synchronous replicas, with load balancing.
  • Distributed Transaction (DTC) and SSIS support.
• Dynamic Data Masking – Protect sensitive data by dynamically masking returned data
• Polybase – With this feature, we can write SQL like queries on top of Hadoop.
• Introduction of R – SQL Server 2016 comes with R service, so no need to export your data from SQL to R to analyze statistical data, we can run R script on SQL itslef.
• Easier set-up for SSAS in Power Pivot mode.
• Enhancement in Master Data Service like excel add in etc.

Most of the features are discussed in this blog, but do that means that this information in sufficient for adopting and using SQL 2016? Obviously not, so in my next blog you will gain information about features implementation process along with testing. So stay updated and look for my next blog.