SQL Server 2016 - News and Features List

Reading Time: 8 minutes

Hello people,
Good Morning!

Next Thursday, April 7, Microsoft will hold an event in Sao Paulo for the release of SQL Server 2016 in Brazil. With each new release, we are seeing major 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 fixes and optimizations, the following benefits:

• Enhanced in-memory performance provides times faster 30 transactions, times faster 100 queries than disk-based relational databases, and real-time operational analytics
• New Always Encrypted Technology Helps Secure Your Still and Moving Data, On-Site and Cloud, with Application Master Keys, No Application Changes
• Stretch Database technology keeps more historical customer data at your fingertips by transparently extending your hot and cold OLTP data to Microsoft Azure securely without application changes.
• Built-in advanced analytics provide the scalability and performance benefits of building and executing your advanced analytics algorithms directly from the primary SQL Server transactional database.
• Business insights through rich mobile views with native apps for Windows, iOS, and Android
• Simplify relational and nonrelational 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 local databases on Microsoft Azure and SQL Server AlwaysOn Secondaries on Azure

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

Breakdown of new features

Always Encrypted

This is a very interesting feature in my opinion and something that can complement the Transparent Data Encryption (TDE) feature introduced in Microsoft SQL Server 2008 for the purpose of allowing database-level native encryption. Always Encrypted further ensures that your data is securely stored through this encryption feature, as well as during its handling processes. Its main feature is to enable the ability to encrypt data within applications that are accessing SQL Server, having the ability to use encryption keys never revealed within the process that performs data encryption. 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 much anticipated especially for Azure users, through Strech Database, will be to store portions (parts) of a table in Azure SQL Database, you may be wondering, as well as parts of a table in another location and not in My database, I can say that I was surprised too, but everything has an explanation.

Through this feature, we have the ability to store historical data contained in a table securely and transparently directly in the cloud, or rather in Microsoft Azure. Once this feature is enabled, silently historical data is migrated to a SQL Azure database, all of which 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 enables you to view live query statistics from an active query, providing real-time insights, which is amazing, knowing live and in color. that a query that is active, running on SQL Server may make us decision.

Row-level security

This new feature could be considered quite revolutionary when it comes to the visibility and access to data in a table. Row-Level Security will enable DBAs and database professionals to control access to data that is stored in certain tables by using functions known as Predicate, thereby limiting a possible column and its respective column. value be consulted.

Eg You can limit that a specific user or user group has access to data only from customers with ID between 1 and 10. The rest of the clients will not appear in the result of a select * from table.

Query Store

Another fantastic feature that will greatly assist DBAs in their long journey of analysis of execution plans, the Query Store will chance to make the analysis of an execution plan that may be experiencing performance issues through a "statement" or "orientation "by the SQL Server, to choose an execution plan to process a query looks something revolutionary.

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

Dynamic Data Masking

Dynamic Data Masking limits the exposure of sensitive data by masking it for 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 designated database field query, while the data in the database is unchanged. Considered easy to use with existing applications as long as masking rules are applied to query results. Many applications can mask sensitive data without modifying existing queries.

Dynamic data masking is complementary to other SQL Server security features (auditing, encryption, row level security), and we strongly recommend that you use this feature in conjunction with them in order to better protect sensitive data in the database. of data.

Temporal Tables

The temporal tables, basically consists of a table that provides a display of data at a given time, that is, you will be able to get information about the data of a table, through a historical view, as if you were going back to the past. , going back in time the data position of that table.

Multiple TempDB Files

Long-awaited functionality for years by database professionals, specifically those working with SQL Server, where from the 2016 release onwards we will be able to configure and define the amount of data files that will be installed during SQL Server installation. must form the structure of the TempDB system database, where the number of files is defined based on their number of processors that the 2016 instance will be running on.

FOR JSON - Native JSON Support

A very interesting news that shows how much Microsoft is dedicated to keeping up with the evolution of Cloud Computing and BigData technologies. FOR JSON consists of a Transact-SQL language clause designed to help SQL Server enable native presentation and output of data in JSON format, which goes far beyond presenting data, but has the ability to format this data interpreted by JSON in the format desired by the user.

Polybase

In my opinion, a tremendous novelty, truly something that goes far beyond a new feature, but a new horizon for professionals, developers and database administrators, a feature that will allow a huge breakthrough in Interoperability. SQL Server with other Non-SQL technologies, as well as data access and storage technologies including Hadoop. PolyBase is a new technology that integrates the product with Microsoft SQL Server Parallel Data Warehouse (PDW) with Hadoop. It is designed to allow queries through relational data stored in PDW and nonrelational data stored in Hadoop in a distributed way through the Hadoop file system (HDFS), bypassing distributed MapReduce, recognized as the Hadoop engine that is commonly used to read data from Hadoop. HDFS. You can create an external table in PDW that references Hadoop data (such as a linked server) and you can query this with SQL, in essence, by adding structure to unstructured data.

Managed Backup to Azure

The feature related to Microsoft Azure and known as “Managed Backup” was designed for the purpose of automating backups to Azure Blob storage, very nice this feature, its big difference is precisely in the way that we will be able to manage and administer the backups of our databases stored in the Azure framework.

In addition, Backup for Azure is a feature designed to allow you to back up your on-premises database directly to Azure blog storage, something that can be done today through third party features and tools, but will be fully supported. 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 data loading without blocking issues or high data 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. In addition, tables up to 2TB are now supported (previously the maximum was 256 GB). 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 every version since then. In version 2016 you have received some sorting improvements and better support for AlwaysOn Availability Groups.

Revolution R Open Services

New to SQL Server 2016 is the support for the services of Revolution R Open, an open-source programming language geared toward 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 use their existing R code and execute it within the SQL Server database engine. This will eliminate the need to export SQL server data and then perform R processing on it. This new feature brings R language processing closer to the data.

Mobile BI

Microsoft wants to streamline the BI transaction process so it can provide advanced and more affordable analytics. With the acquisition of Datazen and the launch of Power BI, Microsoft has natively incorporated SQL Server 2016 solutions optimized for the SQL Server Data Platform and enables rich and interactive data visualization through KPIs across a wide range of mobile platforms (Windows, iOS and Android), as well as enhancements to all tools (Integration Services, Analysis Services, Visual Studio, and SQL Server) to achieve a more modern look of tools and visual improvements in reporting.

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

Thank you all for visiting and until the next post!