Dmitry Karasev - Fotolia

Manage Learn to apply best practices and optimize your operations.

New SQL Server 2017 features give good reasons for fast upgrades

The new SQL Server 2017 adds support for Linux and a variety of other new features that could justify upgrades to the revamped database platform, even coming so soon after the 2016 release.

Ready or not, Microsoft is continuing its torrid development pace with the planned general release of SQL Server 2017 this summer, hot on the heels of SQL Server 2016's shipment in the middle of last year. Businesses that are typically slow to upgrade to new versions of core IT infrastructure products may have a tough time keeping up with this rapid release cycle.

But there are a lot of new SQL Server 2017 features that could make upgrading worthwhile. Let's take a look at the major additions, starting with the most notable one: SQL Server on Linux.

Without a doubt, the biggest news for SQL Server 2017 is the added support for running SQL Server on Linux systems. To make that possible, Microsoft used a platform abstraction layer that lets the software's Windows code be executed on Linux. The company is making all of the editions of SQL Server available on Linux, under the same licensing model as the Windows version. Microsoft will initially support the following Linux distributions: Red Hat Enterprise Linux 7.2, Ubuntu Linux 16.04 and SUSE Enterprise Server v12 SP2.

SQL Server 2017 not all there on Linux

As you might expect for an initial release, SQL Server on Linux doesn't have full feature parity with the Windows platform at this point. Notably, there's no support for SQL Server Analysis Services, Reporting Services or Machine Learning Services (In-Database) -- the latter being the renamed and expanded version of what originally was called R Services. In addition, not all of the core Database Engine features are supported. For instance, FileTables, Stretch Database, PolyBase and R integration are all missing.

Beyond the basic functionality of the Database Engine itself, the primary new SQL Server 2017 features initially available on Linux include the .NET framework, the In-Memory online transaction processing engine, columnstore indexes, partitioning and data compression, the SQL Server Query Store and support for Transact-SQL (T-SQL), extensbile markup language and JavaScript Object Notation. Supported security tools and functions include Always Encrypted, Row-Level Security, dynamic data masking and Transparent Data Encryption.

For high availability, SQL Server 2017 on Linux will support instance-level failover, as well as Always On availability groups that protect multiple databases, with automatic failover to synchronous and asynchronous secondary databases. These functions are supported through the use of the Linux Pacemaker add-on, and they provide the same features as their Windows counterparts. It's also possible to mix Windows and Linux availability groups, but there's no support for automatic failover between different instances running on Linux and Windows.

New Database Engine, business intelligence features

In spite of what you might think, SQL Server 2017 isn't a Linux-only release. Microsoft has also made many enhancements to other areas of the database management system. New SQL Server 2017 features added to the Database Engine include adaptive query processing, which provides the ability to identify a bad data-join choice in a query execution plan and then dynamically switch to a better join strategy.

In SQL Server 2017, the Database Engine also has a new resumable online index rebuild feature that enables you to resume a paused index rebuild operation from where it was interrupted instead of having to restart the entire operation again. Support for clusterless availability groups has also been added, along with graph database capabilities for representing relationships between data elements, backup improvements for small databases on high-end servers and various other enhancements.

New business intelligence and analytics features are primarily in the SQL Server 2017 for Windows version. The most notable one is the addition of support for the Python programming language. Python integration expands on the R language support in SQL Server 2016; Python is a widely used open source language that combines strong scripting capabilities with external statistical packages and analytics libraries for use in analyzing data.

Just like with R, Python scripts can be run directly on the database server or called from T-SQL. As mentioned above, with the advent of SQL Server 2017, SQL Server R Services has been renamed Machine Learning Services (In-Database), which runs both R and Python applications.

A new look for Reporting Services

SQL Server 2017 also provides a new Reporting Services web portal, which replaces the Report Manager tool from previous releases. Shown in Figure 1, the new portal has an HTML5 rendering engine and incorporates key performance indicators, mobile reports and paginated reports, as well as Excel and Power BI Desktop files. You can optionally customize the portal with your organization's logo and look using a branding pack provided by Microsoft.

In addition, the Power BI mobile app for iOS on iPad and iPhone can now display SQL Server mobile reports hosted on your local report server. Other reporting-related SQL Server 2017 features include support for the PowerPoint (PPTX) format as a Reporting Services rendering extension. That lets you export reports in the PPTX format from Report Builder, Report Designer and the web portal. You can also create native Data Analysis Expressions queries against tabular data models in SQL Server Analysis Services using Report Builder and SQL Server Data Tools.

New Reporting Services web portal
Figure 1. New Reporting Services web portal in SQL Server 2017

Although many businesses are sure to have trouble adopting a new release of SQL Server so soon after the last one, there's no doubt that Microsoft has packed a lot of functionality into SQL Server 2017. Existing users might find good reasons to upgrade among the new features. And if you're looking to replace Linux-based Oracle systems or take advantage of SQL Server DevOps with Linux-based containers, SQL Server 2017 could be what you've been waiting for.

Next Steps

SQL Server trends affecting database administrators in 2017

More on the SQL Server 2017 improvements you should know about

Redgate brings DevOps to Visual Studio 2017

The morphing of Document DB into Azure Cosmos DB

Dig Deeper on SQL Server Data Warehousing