The Road to SQL Server 2017

Microsoft announced SQL Server 2017 yesterday (4/19/2017).  No release date was provided but CTP 2.0 is currently available so my guess is that the production release will be within the next three months.

This is the first time that there have been SQL Server releases in consecutive years.  Since 2000, the releases have been 2000, 2005, 2008, 2012, 2014, 2016 and now 2017.  (There was also 2008 R2 releases in 2010, which some at the time suggested should have been SQL Server 2010).

SQL Server 2012 focused on data warehousing and business intelligence with the addition of the column store index and substantial improvements in SQL Server Integration Services deployment in management. 

SQL Server 2014 shifted the focus back to OLTP with in-memory tables and compiled stored procedures providing an order of magnitude performance improvements for applications that could take advantage of it.  The data warehouse capabilities also improved in SQL 2014 with the clustered column store index.  The clustered column store provided performance improvements over SQL 2012's non-clustered column store.  Also, unlike SQL Server 2012, which required the column store index to be dropped and recreated (or handled via complex partition switching) when data changes were required, the 2014's clustered column store allows data modifcations.

SQL Server 2016 had three major themes:
1) Improved performance
2) R server integration
3) SQL Server Reporting Services (SSRS) overhaul

Internal improvements in SQL Server 2016 to take better advantage of modern hardware such as multi-core processors and SSDs provided performance and scalability improvements.  See https://blogs.msdn.microsoft.com/psssql/2016/02/23/sql-2016-it-just-runs-faster-announcement/ for details.

Microsoft incorporated the R technology obtained from its acquisition of Revolution R into SQL Server.  Historically, R "out of the box" had been limited by being single threaded.  Allowing it to run directly on SQL Server provides a much more scalable and production ready solution to get insights out of data.

Finally, by 2016 SSRS was showing its age since it had received minimal attention since SQL Server 2008.  SQL 2016 added an updated Reporting Services portal, mobile reporting, additional reports, and the ability to pin SSRS reports to a Power BI dashboard.

SQL Server 2016 SP1 also added edition parity for key features.  Previously, key performance and scalability features were limited to Enterprise Edition.  Starting with 2016 SP1, columnstore, in-memory OLTP and data compression were made available across all editions. See https://docs.microsoft.com/en-us/sql/sql-server/editions-and-supported-features-for-sql-server-2016.

So now that we have the historical context, what does SQL Server 2017 bring to the table?  At this point, the core database engine is pretty refined and this point there are no new index types left. The SQL Server 2017 focus areas are:
1) Multi-platform support: bringing SQL Server to Linux and Docker containers
2) Extending R server to support Python
3) Automatic tuning

The multi-platform support is important because many applications run on Linux or Docker, and adding a Windows server into the mix to run SQL Server isn't desirable from a management and operational standpoint.  For example, SAP Hybris is a popular e-commerce platform that runs on Linux.  It is compatible with SQL Server but most implementations are on Oracle or MySQL due to the SQL Server's requirement for Windows.

SQL 2017 renames R Services to Machine Learning Services to reflect its support for Python.  It has the same approach that was used for R, but now allows either R or Python code to be called as external stored procedure.  It also makes several R machine learning algorithms from Azure ML available.  See https://msdn.microsoft.com/en-us/microsoft-r/microsoftml/microsoftml?f=255&MSPPError=-2147217396 for details.

The SQL Server Database Engine's cost based optimizer looks at every query and the statistics of underlying indexes and tables it is accessing to try to determine the optimal plan.  However, sometimes a query's plan choice will result in a performance regression.  Enabling automatic tuning for a database will let the Database Engine automatically switch to the last known good plan whenever regression is detected.  See https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning for more details.

The edition parity from SQL Server 2016 SP1 also continues in SQL Server 2017.

Overall, the SQL Server 2017 release allows it to broaden the market to additional platforms, continue its leadership in supporting data science use cases, and makes it easier to provide consistent performance.

Comments

Popular posts from this blog

Jumping into Azure Data Lake

Power BI Licensing Basics

Getting Started with SQL Server 2017 - Installation