SQL Server 2016 - List of new features and resources

Hey guys,
Good morning!

Next Thursday, April 7th, Microsoft will hold an event in São Paulo to launch SQL Server 2016 in Brazil. With each new version, we are seeing great advances and improvements in the DBMS, which is becoming increasingly complete, fast, secure and ahead of other database solutions on the market.

According to Microsoft itself, the new version has, in addition to several corrections and optimizations, the following benefits:

• Enhanced in-memory performance provides 30x faster transactions, 100x faster queries than disk-based relational databases, and real-time operational analytics
• New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with in-app master keys, without changes to the app
• Stretch Database technology keeps more of your customers' historical data at your fingertips by seamlessly extending your hot and cold OLTP data to Microsoft Azure securely without application changes.
• Built-in advanced analytics provides the scalability and performance benefits of building and running your advanced analytics algorithms directly on the main SQL Server transactional database
• Business insights through rich mobile visualizations with native apps for Windows, iOS and Android
• Simplify relational and non-relational data management by querying both with T-SQL using PolyBase
• Faster hybrid backups, high availability, and disaster recovery scenarios for backing up and restoring your on-premises databases to Microsoft Azure and placing SQL Server AlwaysOn secondaries on Azure

If you are interested in downloading preview versions, such as RC2, which Microsoft released on April 1st, you can access this link and check out the news in this post up close.

New features breakdown

Always Encrypted

This is a very interesting feature in my opinion and something that can complement the TDE(Transparent Data Encryption) feature introduced in Microsoft SQL Server 2008, with the purpose of enabling native encryption at the database level. Always Encrypted further ensures that your data is stored securely through this encryption feature, as well as during data handling processes. Its main feature is to allow the possibility of encrypting data within applications that are accessing SQL Server, having the ability to use encryption keys that are never revealed within the process that encrypts the data. As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should not have access).

Strech Database

Another new feature that is highly anticipated especially for Azure users, through Strech Database, it will be possible to store portions (parts) of a table in Azure SQL Database, you may be wondering, what do you mean parts of a table in another location and not in my database, I can say that I was also surprised, but everything has an explanation.

Through this feature, we have the ability to store historical data contained in a table in a secure and transparent way directly in the cloud, or better said in Microsoft Azure. From the moment this feature is enabled, data considered historical is silently migrated to a SQL Azure database, all of this is done by SQL Server without requiring any code changes to your query or application.

Live Query Statistics

One of the features that can surprise the lives of DBAs and developers, Live Query Statistisc allows you to display the live query statistics of an active query, providing insights in real time. This is surprising, knowing live and in color what a query that is active, running in SQL Server, can propose a decision.

Row-Level Security

This new functionality could be considered something quite revolutionary when it comes to visibility and access to data in a table. Row-Level Security will allow DBAs and database professionals to control access to data stored in certain tables, through the use of functions known as Predicate, thus limiting the ability of a possible column and its respective value to be consulted.

Ex: You can limit a specific user or group of users to have access to data only for customers with an ID between 1 and 10. The rest of the customers will not appear in the result of a select * from table.

QueryStore

Another fantastic feature that will greatly help DBAs in their long journey of analyzing execution plans, the Query Store will be able to analyze an execution plan that may be presenting performance problems through an “indication” or “guidance” from SQL Server, being able to choose an execution plan to process a query, it looks like something revolutionary.

A common issue that many organizations face when upgrading SQL Server versions is changes to the query optimizer (which happen with each version) negatively impacting performance. Without comprehensive testing, this has traditionally been a difficult problem to identify and resolve. The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have slowed down recently, allowing administrators or developers to force the use of an older plan if necessary. Query Store is configured per database.

Dynamic Data Masking

Dynamic Data Masking limits the exposure of confidential data by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by allowing customers to designate how much sensitive data to reveal with minimal impact on the application layer. It is a security feature that hides the data in the result set of a query over designated database fields, as long as the data in the database is not changed. Considered easy to use with existing applications, as long as masking rules are applied to the query results. Many applications can mask sensitive data without modifying existing queries.

Dynamic data masking is complementary to other security features of SQL Server (auditing, encryption, row-level security) and it is highly recommended to use this feature in conjunction with them in addition in order to better protect sensitive data in the database.

Temporal Tables

Temporal tables basically consist of a table that provides a display of data at a given moment in time. That's right, you will be able to obtain information about the data in a table, through a historical view, as if you were going back to the past, going back in time to the position of the data in that table.

Multiple TempDB Files

Functionality long awaited for years by professionals in the database area, more specifically those who work with SQL Server, where from version 2016 onwards, we will have the possibility during the installation of SQL Server to configure and define the number of data files that should form the structure of the TempDB system database, where the number of files is defined based on the number of processors that the 2016 instance will be running on.

FOR JSON – Native JSON Support

A very interesting novelty that shows how much Microsoft is dedicated to following the evolution of Cloud Computing and BigData technologies. FOR JSON consists of a clause in the Transact-SQL language created to help SQL Server enable the presentation and output of data in the JSON format natively, something that goes far beyond presenting the data, but rather having the ability to format this data interpreted by JSON in the format desired by the user.

Polybase

In my opinion, it's a great novelty, honestly something that goes far beyond a new feature, but rather a new horizon for professionals, developers and database administrators, a feature that will allow a huge advance in terms of SQL Server Interoperability with other Non-SQL technologies, as well as data access and storage technologies, including Hadoop. PolyBase is a new technology that integrates the Microsoft SQL Server Parallel Data Warehouse (PDW) product with Hadoop. It is designed to enable queries across relational data stored in PDW and non-relational data stored in Hadoop in a distributed manner across the Hadoop File System (HDFS), bypassing distributed MapReduce, the recognized Hadoop engine that is typically used to read data from HDFS. You can create an external table in PDW that references the Hadoop data (like a linked server) and you can query this with SQL, in essence adding structure to unstructured data.

Managed Backup to Azure

The resource related to Microsoft Azure and known as “Managed Backup” was designed with the purpose of automating backups to Azure Blob storage. This functionality is really cool, its biggest difference is precisely the way in which we will be able to manage and administer backups of our databases stored in the Azure structure.

Additionally, Backup to Azure is a feature designed to let you take a backup of your on-premises database directly to Azure blog storage, something that today can be done through third-party resources and tools, but will be fully integrated and working natively in the 2016 version.

In-Memory Enhancements

SQL Server 2014 introduced the concept of In-Memory tables. These are designed for high speed loading of data without blocking issues or high data volume issues. While this feature looked great on paper, there were a number of limitations particularly surrounding restrictions and procedures. In SQL Server 2016, this functionality is much better, supporting foreign keys, Unique Key contraints and parallelism. Additionally, tables up to 2TB are now supported (previously the maximum was 256GB). Another part of in-memory is column-store indexes, which are commonly used in data warehouse workloads. This functionality was introduced in SQL 2012 and has been improved in each version since then. In version 2016 it received some improvements regarding ordering and better support for AlwaysOn Availability Groups.

Revolution R Open Services

Another big new feature in SQL Server 2016 is support for Revolution R Open services, an open-source programming language focused on BigData. With the purchase of Revolution Analytics, Microsoft is now able to incorporate R to support early big data analytics within SQL Server. By incorporating R processing into SQL Server, data scientists will be able to take their existing R code and run it within the SQL Server database engine. This will eliminate the need to export the data from the SQL server and then perform R processing on it. This new feature brings R language processing closer to the data.

Mobile BI

Microsoft wants to increase the transaction process in the BI area so that it is possible to provide advanced and more accessible analysis. With the acquisition of Datazen and the launch of Power BI, Microsoft natively incorporated into SQL Server 2016 solutions optimized for the SQL Server Data Platform and allow rich and interactive data visualization through KPIs on the most diverse mobile platforms (Windows, iOS and Android), in addition to improvements in all tools (Integration Services, Analysis Services, Visual Studio and SQL Server) to achieve a more modern look of the tools and visual improvements in reports.

Source(s): https://pedrogalvaojunior.wordpress.com

Thank you everyone for visiting and see you in the next post!