Features and Enhancements in SQL Server 2016, 2017 & 2019 Release.

Microsoft is moving very fast with feature enhancements, improving security, performance, enhancing the capabilities of machine learning, data integration, etc.
The post summarizes the features and its enhancement in each SQL Server release. View Microsoft SQL Server versions to compare features and find the right version to meet your data platform needs.

SQL Server 2016
SQL Server 2017
SQL Server 2019
Installation Enhancement
·   Separate line for Install SQL Server Management Tools and Install SQL Server Data Tools.
·   A new component is PolyBase Query Service for External Data can be selected.
·   Enable the Instance File Initialization, which is used to reclaim the used disk space without filling that space with zeros.
·   TempDB page, where you can specify the size and growth parameters for the TempDB data and log files
Installation Enhancement
·   Reporting Services has now been removed from the core installation
·   Machine Learning there is also Python support and that SSIS has new scale out capabilities
·   Reporting Services – Native installation has been removed.
·   No more SharePoint integration.

Installation Enhancement
·   The new preview provides three types of installation options – Basic, Custom and Download Media. 
·   Installation process remains the same as SQL Server 2017.
·   By default, a new installation of SQL Server on Linux creates multiple tempdb data files based on the number of logical cores (with up to 8 data files). 




Not Available in this release.
SQL Server on Linux & MAC
·   Available with different flavors of the Linux operating systems.
·   You can also develop applications with SQL Server on Linux, Windows, Ubuntu, or Docker and deploy them on these platforms.
Enhancement On Linux
·   Replication Support
·   MSDTC Support
·   Machine Learning on Linux.
·   New Container registry
·   AOAG on Docker containers with Kubernetes.
New RHEL-based container images
Maintenance Plan Enhancement
·   SQL Server 2016 comes with new improvements to the index maintenance tasks that provides more control to the Rebuild Index and Reorganize Index tasks.
Resumable online index rebuild
·   This feature resumes an online index rebuild operation from where it stopped after events such as database failovers, running out of disk space, or pauses.
Resumable online index creation
·   SQL Server 2019 also provides support for resumable online index creation similar to resumable online index rebuilds in SQL Server 2017.
SQL Server machine learning services (R language)
·  SQL Server 2016 integrated the R programming language, which can be run within the database server and can be embedded into a Transact-SQL (T-SQL) script.
SQL Server machine learning services (Python)
·   In SQL Server 2017, you can execute the Python script within the database server itself.
·   Both R and Python are popular programming languages that provide extensive support for data analytics along with natural language processing capability
Java Language Extensions
·   In SQL Server 2019, Java is supported.
·   The relational data can be used in the external code using the extensibility framework.
·   Language Extensions provides Data Security, Speed and Ease of deployment and Integration.
Query Store
·  This feature in SQL Server 2016 allows you to track previous execution plans and performance by tracing your queries over a long period of time.
Adaptive Query Processing
·   SQL Server 2017 adapts optimization strategies to your application workload’s runtime conditions.
·   It includes adaptive query processing features that you can use to improve query performance in SQL Server and SQL Database.
Intelligent query processing
·   Meant to rectify some of the common query performance problems by taking some automatic corrective approaches during run-time.
·   It leverages a feedback loop based on statistics collected from past executions.
Clustered columnstore enhancement
·   SQL Server 2016 supports primary keys and foreign keys by using a B-tree index to enforce these constraints on a clustered columnstore index. 
·   Clustered columnstore index supports the snapshot isolation and read-committed snapshot isolation levels.
·   Provides the ability to create a columnstore index on top of a memory-optimized table.
Clustered columnstore enhancement
·   Online Non-Clustered Columnstore index build and rebuild support.
·   Clustered Columnstore Indexes now support LOB columns nvarchar(max), varchar(max), varbinary (max)).Columnstore index can have a non-persisted computed columns.
Clustered columnstore enhancement
·   Columnstore index stats update in clone databases
·   Compression Estimates for Columnstore
·   Resumable online index creation.
Always Encrypted
·   This feature protects data and enables the SQL Server to perform encrypted data operations so that the owners can protect their confidential data by using an encryption key.
·   This feature ensures that your important data stored in the cloud managed database remains encrypted and protected.





No enhancement in this release.
Always Encrypted with Secure Enclaves
·   SQL Server 2019 introduces the secure enclave technology. A secure enclave extends client applications, data trust to the server side.
·   It secures the data from the malware and privileged users
Temporal Tables
·   Is a new type of a table that allows full tracking of any changes that the primary table has undergone.
·   The great benefit is that it allows the DBA to know the state of the data at any point in time.
Temporal Tables Enhancement
·   Retention policy for historical data is an important aspect of planning and managing the lifecycle of every temporal table.
·   Temporal history retention can be configured at the individual table level, which allows users to create flexible aging polices.




No enhancement in this release.
PolyBase
·   This feature works as a connector between SQL Server and Hadoop so that data processing involving large text files can be stored conveniently in Azure Blob Storage or Hadoop.
·   This technology bridges the gap between SQL Server and Hadoop to make data storage easy.






No enhancement in this release.
PolyBase Enhancement
·  SQL 2019 provides enhancement to PolyBase to access data from various data sources. Users can access these data from external tables similar to a relational database table.
·  These external tables are linked to the data sources and when we execute any query, data from an external table is retrieved and shown to the user.
AlwaysOn Enhancement
·   New availability groups functionality (3-sync).
·   It also relies on Distributed Transaction Coordinator support for load balancing and manages automatic failover.
AlwaysOn Enhancement
·   Availability groups can now be set up without an underlying cluster (WSFC) and across mixed environments (instances on Windows and Linux or Docker).
·   SQL Server 2017 supports distributed transactions for databases in availability groups.
AlwaysOn Enhancement
·   Up to Five Synchronous Replica Pairs for Availability Groups.
·   Enable High Availability Configurations for SQL Server Running in Containers.
·   Better Scale-out with Automatic Redirection of Connections Based on read/write Intent.
Temporal Tables
·   Is a new type of a table that allows full tracking of any changes that the primary table has undergone.
·   The great benefit is that it allows the DBA to know the state of the data at any point in time.
Temporal Tables Enhancement
·   Retention policy for historical data is an important aspect of planning and managing the lifecycle of every temporal table
·   Temporal history retention can be configured at the individual table level, which allows users to create flexible aging polices.




No enhancement in this release.
Row Level Security
·   The Row Level Security feature restricts some users to view data in tables by using an SQL Server login.
·   It allows you to implement row level security so that new users will not be able to detect whether the rows of data were filtered for restricting data.
Security Enhancement
·   You can now grant, deny, or revoke permissions on database-scoped credentials such as CONTROL, ALTER, REFERENCES, TAKE OWNERSHIP, and VIEW DEFINITION permissions
Support to Persistent Memory (PMEM) Devices
·   SQL Server 2019 provides support to Persistent Memory (PMEM) devices.
·   SQL Server directly accesses the device, by-passing the storage stack of the operating system for the files placed on the PMEM device.
Dynamic Data Masking
·   Row Level Security (RLS) is a feature that enables fine grained control over access to rows in a table.
·   RLS allows you to easily control which users can access which data with complete transparency to the application.
·   This enables us to easily restrict the data based on the user identity or security context.
Graph Data Capabilities
·   SQL Server 2017 introduces graph data capabilities. You can now store the data directly in SQL Server itself in graph format using nodes and edges, and you can query it using node/edge query syntax.
·   Store relationships using nodes and edges
·   Analyze interconnected data using node/edge query syntax
Certificate Management Functionality
·   In SQL Server 2019, certificate management is integrated into the SQL Server Configuration Manager.
·   We can view, validate the certificates being used and installed in SQL Server instance.
·   This feature helps to manage certificates in a better way.
Managed Backup to Azure Enhancement
Following enhancement have been done in SQL Server 2016
·   Databases in Full, Bulk logged, and Simple recovery model are supported.
·   System databases can be backed up with managed backups.
·   Block blobs are now used, with the same benefits as listed for Backup to Microsoft Azure.
Smart Backups
Smart Differential Backup feature allows DBAs to build smart backup solutions, which perform differential backups if the percentage of changed pages in the database is below a threshold. Otherwise, they perform a full database backup.
Smart transaction log backup approach will help to implement the backup solution which will take log backups based on database activities and will also prevent the transaction log to grow continuously until the disk is full.
Big Data Clusters
There are a couple of cool things about the BDC version:
·   Runs on Kubernetes
·   Integrates HDFS (a distributed file storage)
·   Integrates Spark. Services both Spark and HDFS run behind an Apache Knox Gateway.

Stretch Database
·   Stretch Database feature lets you dynamically, securely archive data from a local SQL Server database to an Azure SQL database in the cloud.
·   SQL Server automatically queries both local and remote data in the linked databases.
Automatic Database Tuning
·   This feature provides insight into potential query performance problems, recommends solutions, and can automatically fix identified problems.
·   Automatic Tuning has the following two features available to improve query performance Automatic Plan Correction & Automatic Index Management.
Data Virtualization
·   SQL Server 2019 provides a data virtualization solution which is an improvement to the ETL process.
·   Data virtualization allows integrating data from different sources such as MongoDB, Oracle, DB2, Cosmos, and Hadoop Distributed File System (HDFS) without moving data around.

No comments:

Post a Comment