Sunday, April 21, 2019

[Latest] SQL Server 2019 New Features | SQL Server Developer Edition

SQL Server 2019 New Features 

Image Credit :- Blog

Microsoft's leading database is an best tool, with in-cloud and on-premises versions providing incredible storage and high analystic tools. It's additionally turned into an essential application for data scientists, giving a structure to building and testing machine learning models. There is more in SQL Server, and teh new release version can indicate where Microsoft thinks your data needs will go over the next few years.

Like, all the previous Microsoft SQL Server 2016, Microsoft SQL Server 2017, and so on versions, the Microsoft SQL Server 2019 is the Latest version also jam-packed with new features and capabilities. It will help organizations evaluate and test relational data with multiple integrations of various software. By creatinng a simplified view of data, users can have a comprehensive view of information.

SQL Server Developer Edition | SQL Server 2019 Release History

Below table lists the release history for SQL Server 2019 preview CTP releases.

VersionReleaseRelease Date
15.0.1400.75CTP 2.42019-3-27
15.0.1300.359CTP 2.32019-3-01
15.0.1200.24CTP 2.22018-12-11
15.0.1100.94CTP 2.12018-11-06
 15.0.1000.34CTP 2.02018-09-24

The new version of SQL Server 2019 is available in Linux and Windows versions and support for cotainers and Kubernetes is added. Using with Docker, including container support and the Linux Version of SQL Server is an intriguing alternative, as it will enable you to build SQL Server with huge Kubernetes based analytic engines that work across Azure facilitated data lakes using Apache Spark.

Download Latest Version : Download CTP 2.4

Top New Features of SQL Server 2019

Always On Availability Groups

Microsoft introduced Always On Availability Groups in SQL Server 2012, and has been making improvements to the feature in each release since.
Most biggest complaints most DBAs have about this feature, which allows multiple copies of a database to be replicated across multiple servers, is that there was no support for the system databases MSDB and Master, which contain log-ins and SQL Server Agent jobs, schedules and notifications.My personal favorite is DBA Tools, which runs PowerShell to keep log-ins and jobs synchronized between the availability group member nodes.

Microsoft is correcting in SQL Server 2019 this by addressing both Master and MSDB, which will simplify the administration of availability groups. Also it has the ability to have up to five synchronous replicas, up from the previous limit of three. Also you can have a total of nine replicas in your availability group, with the option to extend that further using distributed availability groups.

Accelerated Data Recovery

There are two features I'm writing about here that I thought were sheer magic the first time I saw them.

If you have ever killed a long-running transaction and waiting away for that transaction to roll back, this feature will be of interest to you. SQL Server's process was to identify the oldest page Log Sequence Number (LSN), then perform an undo and a redo phase. The undo phase involved traversing the transaction log file in reverse -- which, for large transactions, can take nearly the same amount of time as the original transaction. Another side-effect of this behavior is that the transaction log cannot be truncated while this rollback is taking place, which can result in disk space issues.

Currently , Accelerated Database Recovery (ADR) is available in public preview in Azure SQL Database, as well as Azure SQL Data Warehouse. It will be soon on by default for all existing and new databases or resultset in Azure. ADR will also be available in public preview in one of the upcoming CTP builds of SQL Server 2019, and is expected to be in the Release-To-Manufacturing (RTM) version of SQL Server 2019.

It works is by using a persisted version store in the database itself, as opposed to the TempDB version store for Read Committed Snapshot Isolation. This works in conjunction with a process called logical revert, which reads the version store and provides instantaneous rollback and undo. There are also some in-memory and clean-up components that provide background processing to support the overall process.

This feature does have some overhead, but any mission-critical system that has to be patched regularly or simply has long-running transactions that could be rolled back will benefit from the new behavior.

Scalar Function Inlining

The other feature that I refer to as simply magic was referenced in this Microsoft Research whitepaper released in the spring of 2018. The first time I tested it, I was blown away.

The Scalar functions that can be included in a query, is a concept that is very familiar to developers, who are used to writing in their object-oriented code. Sadly, this has been a problematic design pattern in SQL Server. Scalar functions are executed in a black box, giving no insight into the underlying query operations to process them and / through a combination of factors existing in the SQL Server codebase / are limited in performance. The two biggest problems are the fact that these functions are limited to single-threaded execution and, more importantly, are executed row by row.

Other New Features

There are number of other new features, including online columnstore index builds and rebuilds, static data masking (which allows for masked data to be persisted to other environments), and enhancements to Always Encrypted to allow more operations to be performed on encrypted data in order to make the feature more useful.

In SQL Server 2019, there are also many subtle enhancements to a wide array of features in SQL Server, including persistent memory options for data storage, and broader external database (Oracle, Teradata and MongoDB) support for PolyBase, it is aligns to the targets for SQL Server Big Data clusters.

SQL Server 2019 preview :

Download SQL Server 2019 preview to install on Windows.
Install on Linux for Red Hat Enterprise Server, SUSE Linux Enterprise Server, and Ubuntu.
Run on SQL Server 2019 preview on Docker.

Also See :

SQL Server 2019 Release Date - March 26, 2019
SQL Server 2018 Release Date -  Sep 24, 2018
SQL Server 2017 Release Date - Oct 2, 2017

Previous Post
Next Post

post written by: