For Day 2, lets talk about the top five reasons that will help you make the case for an upgrade and migration effort to SQL Server 2016 or SQL Server 2017.
- Useful new features that are not available in legacy versions of SQL Server
- Enhancements for existing features compared to legacy versions of SQL Server
- New programmability features available in SQL Server 2016 Standard Edition
- Higher license limits in SQL Server 2016
- Support for new hardware and storage features
Useful New and Improved Features
The list of new features that might be relevant for you depends on your workload and usage scenarios. It also depends on how old the legacy version of SQL Server is compared to the version you would like to upgrade to. For example, it makes a big difference whether you are moving from SQL Server 2005 to SQL Server 2017, or whether you are just moving from SQL Server 2014 to SQL Server 2017. The bigger the gap, the more new features that are going to be available.
It also depends of what SQL Server components you will be installing and using. This series is going to primarily cover Database Engine information, but if you are going to be using other major components, such as SSRS, SSIS, or SSAS, you will have to do additional research.
Microsoft does an uneven job of listing and fully documenting/explaining all of the new and improved features in each recent version of SQL Server, as shown in the links listed below:
What’s New (Database Engine) – SQL Server 2014
What’s New (Database Engine) – SQL Server 2012
If you dig deeper, there are more technical blogs from Microsoft that give a lot more detail and also include additional enhancements that often are not listed in the official documentation. Here are some of the best sources of that type of information:
New Programmability Features in SQL Server Standard Edition
With the release of SQL Server 2016 Service Pack 1, many programmability features that were formerly on available in SQL Server 2016 Enterprise Edition were made available in SQL Server 2016 Standard Edition (and also in Web Edition and Express Edition). This makes it more attractive and feasible for someone to upgrade from a legacy version of SQL Server Standard Edition to SQL Server 2016 Standard Edition.
In some cases, you may also be able to go from a legacy version of SQL Server Enterprise Edition to SQL Server 2016 Standard Edition, although this may be more difficult than it seems at first due to throttling of some programmability features in Standard Edition and low SQL Server 2016 License limits for memory, sockets, and cores. Here are some links with more information about this:
SQL Server 2016 Service Pack 1 (SP1) released !!!
Higher License Limits in SQL Server 2016
Microsoft raised the physical core count limit (per instance) in SQL Server 2016 Standard Edition from 16 to 24. Unfortunately, they did not raise the memory limit (per instance), which remains at 128GB. SQL Server 2016 Standard Edition SP1 did indirectly raise the memory limit somewhat, by adding separate pools for Columnstore indexes and for in-memory OLTP, as discussed here.
Support for new Hardware and Storage Features
SQL Server 2014 added a new feature called Buffer Pool Extension (BPE) that can be helpful in some scenarios with OLTP workloads. It is mainly interesting with SQL Server Standard Edition (due to its low memory license limit). Unfortunately, Microsoft has not chosen to invest any additional development resources in the feature in newer releases.
Microsoft did do some interesting work in SQL Server 2016 with adding support for specific processor instruction sets for some types of operations that would benefit from that support. They also added support for tail of the log caching using persistent memory (PM) in SQL Server 2016 (when running on Windows Server 2016). I mapped some of these new features to specific processor families here:
I am going to be going into more detail about this information and many other upgrade and migration subjects during the course of this series, so stay tuned!
Additional Resources
I have a new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.
Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.
Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017.
Here is a link to the complete series about upgrading SQL Server.