Migrating to newer SQL Server versions

SQL Server 2017 is not that new anymore, but it is still the fastest database everywhere you need it. But, as with each new release of SQL Server, most people are hesitating to start migrating to this new version for a variety of reasons. Let’s have a look at why we are convinced that it’s worth considering to take that step today.

1. No more service packs
One of the big changes in SQL Server 2017 is that Microsoft will no longer release Service Packs like they did in the past. It was a bit of a tradition that most companies wouldn’t start deploying a new SQL Server version until Service Pack 1 was released. From now on, only Cumulative Updates will be released for SQL Server. In the first year on a monthly base, and during the 4 remaining years of the SQL Server lifecycle, CUs will be released every quarter. At the moment 4 CU releases have been released since the launch of SQ Server 2017. The main reason why companies are hesitative to upgrade is that they don’t have a lot of confidence in the testing of CUs. Where SPs are considered to be much more profoundly tested, the general feeling is that CUs aren’t. Microsoft assured their customers that the CU testing will be as thorough as the SP testing.

But, since Microsoft created the Cloud First strategy, we know that there is a much larger test base. Each SQL Server bug that is found in Azure (and solved) will now be added in the CUs. So we are much more confident that the quality of a CU is a lot higher.

Furthermore, several SQL Server components – like SQL Server Management Studio and SQL Reporting Services – are now detached from the SQL Server setup, and receive separate updates.

2. Awesome query tuning
Microsoft continues its efforts in improving the query execution process. In SQL Server 2016 the Query Store was introduced. SQL Server 2017 continues on this path, and adds new performance optimization features.

Automatic tuning provides insight into potential query performance problems, recommends solutions, and can automatically fix identified problems.

Automatic plan correction, which is built on the Query Store in SQL Server 2016. You have the possibility to either execute a command proposed by the query tuning advisor that will force to use the correct execution plan (this is by the way the default setting). Or, you can even opt to let the system take these decisions by itself and force the use of the best possible execution plan.

This might not be the solution for mission critical systems, but for environments without a (permanent)DBA, it can be a big advantage as it brings a continues solution to plan regression.
This feature was already available in Azure since 2016 and has been tested very thoroughly, there has been plenty of time to fix issues that surfaced. This is again a good example of Microsoft’s Cloud First strategy.

But wait, there is more.

Next to Automatic plan correction, another feature called Adaptive Query Processing was introduced.

Adaptive query processing
a. Interleaved Executions
The query optimizer is now able to get accurate statistics for multi statement table valued functions, instead of always reverting to hardcode values.
b. Batch Mode Memory Grant Feedback
Memory grant feedback will calculate if the previous calculated memory grants are still sufficient. if not, memory grants will be increased or decreased. Columnstore index needs to be involved to be eligible for batch mode execution.
c. Batch Mode Adaptive Joins
Optimizer will choose between hash join or nested loop join at runtime instead of at compile time, depending on the actual input. Again, columnstore index needs to be involved

3. Linux & Docker
The biggest attraction of SQL Server 2017 is probably the availability of SQL Server under Linux, and in the Docker engine. Supported Linux environments are Suse Enterprise, Redhat Enterprise and Ubuntu. You can now run a fully functional SQL Server Database engine under Linux at a competitive price. Actually, you don’t even have to be on Windows anymore to run and work with SQL Server, plenty of client tools are available for other platforms. MSSQL-CLI, Visual Studio code are all available on Linux, MacOS and Windows. Even the new Powershell Core, provides access to Powershell on multiple platforms.

4. Also worth mentioning
Here are some other highlights next to the 3 important features mentioned above:
• Resumable online index rebuild (Enterprise Edition only)
In SQL Server 2017 you can now spread your index rebuild operations across multiple maintenance windows, and it also provides a way to continue with an index rebuild after an error (instead of restarting again from the beginning).
• Graph database
When handling big data, SQL Server 2017 provides graph database possibility to model data and complex relationships.
• Python can now be used next to R language in the Machine Learning Services, expanding the Advanced intelligence system built-in inside SQL Server. It allows you to perform analytics, run machine learning models, or handle most any kind of data-powered work. This feature is available in most of the SQL Server editions from Enterprise to even the Express edition.
• SQL Server Analysis Services 2017 introduces many enhancements for tabular models, which is now the default mode.
• SQL Server Integration Services 2017 (which is also available on Linux) introduces the Scale Out feature, which also supports high availability.
• Clusterless support for Availability groups was added.
• MSDTC is now fully supported in AG. Previously it was not entirely available or completely unavailable.

Patrick De Belder werkt als SQL Server DBA bij Kohera.