Not only has the Microsoft SQL Server engineering team added more and stronger leadership, outstanding individual engineers and staff members, but it is also radically moving its entire development process into a “cloud-first” ethos. Now, I’d like to show one of the most salient results of this new development process-accelerated product releases. It was only a few years ago (and is still the case for other data management platforms) that a major new release takes at least 2, but more likely 3 years. With the “production-ready” release of SQL Server 2017 Community Technology Preview 2.0, we are only 1 year from the last production release.
Any rational skeptic would say, “Sure, it’s easy to release more often if you cut the number of major features per release.” But that’s not the case at all. SQL Server 2017 is every bit as substantial as any previous release of Microsoft’s flagship database product. Introduced during the online Microsoft Data Amp event, the newest release includes a raft-load of new features and enhancements. Here are my favorite highlights:
- Full support for SQL Server on Linux: SQL Server 2017 runs on Red Hat Enterprise Linux (RHEL), SUSE Linux Enterprise Server, SLES, and Ubuntu. More distributions will follow.
- Docker container support on Windows and Linux: Docker containers now work on Windows and Linux, including on a Mac. In fact, you’ll be able to download many special purpose containers for various SQL Server products.
- Python support within SQL Server: Microsoft incorporated R support into SQL Server 2016 and, to further extend its leadership in data science, it has now added Python support. While R is a programming language strictly for statistical analysis, Python is a general purpose language with a large number of open source libraries such as NumPy and Pandas. Plus, you can parallelize processing and even useGPU computing, especially for the benefit of machine learning, data science, and predictive analytics, going under the moniker of “Microsoft Machine Learning Services.”
- Built-in AI: You now have a large variety of pre-built and pre-trained neural network models for activities such as sentiment analysis, demand forecasting, or image analysis. These models also support Spark in new ways, such as providing access to SparklyR, SparkETL, and SparkSQL, along with GPU support for neural networks. New APIs, such as the Face API, Computer Vision API, and Content Moderator further strengthen the data platform.
- Resumable online index rebuild: If you have large tables that are accessed by users 24x7, it can be very difficult to find a time to perform preventative maintenance. This new feature enables DBAs to rebuild indexes while they’re still in use as well as pause and resume those rebuilds, picking up exactly where the rebuild left off, and offers the added bonus of using less log space than index rebuild operations on previous releases.
- Graph database features: We now have full CRUD support for node and edge models, along with extensive T-SQL programmatic extensions for multi-hop navigation without requiring JOINs.
- Adaptive Query Processing: Let’s keep SQL Server running at top performance without requiring as much attention from DBAs. First, SQL Server 2017 improves query performance in code containing multi-statement table valued functions by providing runtime cardinality estimates to the query optimizer, also known as “interleaved execution.” Next, batch mode processing is further strengthened, above the ability to adjust batch mode memory grants provided in the last release, by enabling deferral of choice for a query’s physical join algorithm until the query is actually executed, also known as “batch mode adaptive joins.” Finally, SQL Server 2017 automatically detects query plan regressions along with capabilities to automatically force an older, but better performing execution plan.
- Azure SQL Database Threat Detection: While SQL Server has had the least security vulnerabilities of the major relational database platforms for the last 7 years, Microsoft has decided to strengthen SQL Server’s security against web-based threats. By enabling SQL Database Threat Detection on your Azure SQL Databases, you enable real-time alerting for SQL injection attacks and anomalous login detection.
We’re out of room to further explore the new features of SQL Server 2017. But there’s a lot more to delve into. My advice at this point is to watch the two keynote videos from Microsoft Data Amp event and then drill down into the videos that demonstrate the features that most apply to your work.