Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Reasons to Upgrade to SQL Server 2017


By:   |   Last Updated: 2018-08-16   |   Comments (1)   |   Related Tips: More > Upgrades and Migrations

Problem

You are on the SQL Server 2012 platform and want to upgrade to SQL Server 2017 to take advantage of all the excellent new features that came out with SQL Server 2016 and a few more added in SQL Server 2017. You need to justify to upper management what benefits the upgrade will bring to your organization. You also want to make sure that you take all necessary steps to have a successful upgrade.

Solution

In this first tip of the series, we will discuss the most compelling reasons to upgrade to SQL Server 2017, if you or your upper management is yet to be convinced. In future tips, we will see what pre-upgrade tasks should be completed in order to ensure a successful SQL Server upgrade. We will go over the upgrade steps and finally look at a few post upgrade general issues.

Why you should upgrade to SQL Server 2017?

SQL Server 2016 came out with bunch of new and very cool features. It is one of the biggest SQL Server releases after SQL Server 2005, feature wise. You can read about all the new features and feature enhancements in SQL Server 2016 here. Of course, all these goodies and some more are available in SQL Server 2017 as well.

The following chart gives a brief description of some of the new features in SQL Server 2016 and 2017. This is not a full list, but it will give you a very good idea of 2 or 3 features that will be a game changer for your business and will generate more revenue or make your system more reliable and more secure. This will be a good point to start discussion on the benefits of upgrading to SQL Server 2017 with your boss and other team leads such as the Dev team lead, QA team lead and your Systems Administration team. The more you have buy in from other team leads, the easier it will become when you submit your upgrade project for approval to your upper management who writes the checks.

Features Benefits
Always Encrypted Always Encrypted makes PII information in the database more secure than ever before. Even DBAs and Sysadmins do not have the key to decrypt the data. Benefit to the business application is that now the data is totally encrypted when it is at rest and when transmitting over the wire.
Dynamic Data Masking Dynamic Data Masking does not change data in the database like Always Encrypted does, but it has its own use cases. A good use case of DDM can be; a developer queries production environment for troubleshooting purposes without violating compliance regulations. He or she will not be able to see sensitive PII data instead will see masked data in its place.
Row Level Security Row level security provides another layer of data protection. Data rows can only be accessed if user has permissions on them. This logic is set at an individual database level and then enabled at table level. The benefit for the business is that this makes your security system more reliable and robust by reducing the surface area of your security system in case a hacker tries to attack it.
Temporal Table Temporal tables automatically store all versions of updates and deletes in a history table. This provides for a query construct that gets data from the current and history table with very easy T-SQL syntax. This feature is enabled at the table level. Temporal Table provides benefits of a solid audit trail for Auditors and helps companies to meet the standard security compliance.
Query Store Query Store makes performance troubleshooting very robust and easy. You can easily compare Query Plans and can choose which plan a particular query should use going forward. This feature is enabled at the database level. It is better than DMVs because data is persisted for the amount of time you configure. Benefits of using Query Store is that it almost eliminates the need to buy third party performance monitoring tools.
Stretch Database On-premises storage becomes expensive when you have large amounts of data that you are keeping because you have a requirement to keep this historical data. It is rarely accessed by the users. Stretch database provides low cost solution to move this cold data to Azure environment and free up on-premises storage for your more critical data. You can always query that cold data just like you would query any table. Users will not see any difference.
PolyBase PolyBase provides a mechanism to get combined results to the users from SQL database and data stored in Hadoop file system or Azure Blob storage. All this is done from SQL Server using TSQL query. This shortens the curve of adopting this new feature as DBAs don't need to learn any Hadoop related technologies. Your business can benefit if you are already using semi structured data in Hadoop or any other file system because SQL server 2016 now have ability to query directly from that storage and there is no need to import all that data into SQL Server for processing.
New in SQL Server 2017
Adaptive Query Processing AAdaptive Query Processing, as the name suggests, adapt the query plan based on the feedback it receives from previous executions. With automatic feedback, AQP keeps getting smarter when it runs the same query next time. This is like engine does its own self-tuning without any human interaction.
Support for Graph Data Graph Database is another way to define relationship between your heavily interconnected tables.
Application decides where the query will get it data from; Relational database or Graph database. Graph database has a concept of Nodes and Edges and helps tremendously where application relationship is complex.
Runs on Linux and Docker Containers Linux is an open source Operating System. Docker containers are like separate SQL environments which runs on Linux. Multiple Docker containers can exist on single Linux host. And if you make any configuration changes inside a Docker, it will not affect SQL Server in another Docker on the same machine. This will allow you to quickly deliver application databases without lot of overhead. You will also save licensing cost of Windows Operating System.

Many features that have been historically only available in the Enterprise edition are now made available in Standard Edition of SQL Server 2016 with Service Pack 1 and these are ported to SQL Server 2017. There is now more of a reason to upgrade to these new higher versions of SQL Server. If you were previously using SQL Server 2012 Enterprise edition for certain enterprise level features (for example database snapshot and database compression, etc.), you can check and see if those features are available in SQL Server 2017 Standard edition. This will give you a huge cost savings in SQL Server licensing cost. For more information on which features are available, read here. Below is the chart of features available in Standard Edition of SQL Server 2016 RTM and Service Pack 1.

Features SQL Server 2016
RTM
SQL Server 2016
SP1 and 2017
  Standard Edition Standard Edition
Row Level Security
Dynamic Data Masking
Change Data Capture
Database Snapshot
Columnstore
Partitioning
Compression
In Memory OLTP
Always Encrypted
PolyBase
Fine grained auditing
Multiple Filestream containers
     
Source: https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/

Since Microsoft has sweeten the deal with Standard edition of SQL Server 2016 and up with SP1, there is one less hurdle; cost, in upgrading to SQL Server 2017. In fact, Microsoft is so confident about their SQL Server 2016 version that they are offering free licenses and product training if you migrate from Oracle. Microsoft says that a customer who switches away from Oracle to Microsoft’s platform for running transaction, data warehouse, data integration, business intelligence and advanced analytic workloads can save up to $10 million over three years. The assumption is based on annualized pricing using the Oracle US commercial list price and SQL Server open ERP EE price with 16 core servers.

Following is a chart that shows licensing cost comparison between standard and Enterprise Edition of SQL Server 2012, 2016 and 2017.

Version Edition License Cost 2 Quad core
Processors
4 Quad core
Processors
    Per Core 8 Cores 16 Cores
SQL Server 2012 Standard $1,793 $14,344 $28,688
SQL Server 2012 Enterprise $6,874 $54,992 $109,984
SQL Server 2016\ 2017 Standard $1,858 $14,864 $29,728
SQL Server 2016 \2017 Enterprise $7,128 $57,024 $114,048
http://www.itprotoday.com/microsoft-sql-server/microsoft-sql-server-2012s-editions-and-licensing

FFor some companies, upgrading SQL Server will provide an opportunity to refresh old Hardware and Operating System. Newer OS always are generally more secure with improved performance. Newer hardware saves you money in the long run because they have high power CPUs. Hence, you can now get the same or even more performance out of a lesser number of CPU cores and save tons on SQL Server licensing cost. Plus, with SQL Server 2017 you can save on Windows server licenses if you plan to install it on Linux OS which is fully supported by Microsoft. Microsoft offers a limited time discount on SQL Server 2017 on Linux.

save on sql server

Yet another reason to upgrade your OS could be that it may be running out of Support. The chart below shows which Windows OS version is supported by which SQL Server version. For example, you have your SQL Server 2012 installed on Windows server 2008 R2. Upgrading SQL Server 2012 to 2017 will require first to upgrade OS to Windows 2012 R2. But if you instead upgrade to Windows 2016, you will be able to take full advantage of all the enhancements made in SQL Server 2017.

SQL Server Version Windows
2008\2008 R2
Windows
2012\2012 R2
Windows
2016
SQL Server 2005
SQL Server 2008
SQL Server 2008 R2
SQL Server 2012
SQL Server 2012 R2
SQL Server 2014
SQL Server 2016
SQL Server 2017
Source: https://www.sqlskills.com/blogs/glenn/operating-system-support-for-sql-server-versions/

Here is one final reason to upgrade; mainstream support for SQL Server 2012 Service Pack 4 ended on November 2017 and if you are still using SQL Server 2012 you may be paying a lot for the extended support. On the other hand, SQL Server 2016 mainstream support ends in July 2021 and SQL Server 2017 ends in November 2022. Microsoft will now be releasing new version of SQL Server every year and will not be releasing any services packs starting SQL Server 2017. Only cumulative updates (CU) will be released quarterly.

Summary

SSQL Server 2017 Standard edition contains most of the features that used to be in the Enterprise SQL Server only. On top of that SQL Server 2017 can be installed on Linux OS. There are so many new features that have brought a paradigm shift in the SQL Server landscape that it is difficult to mention them all here. Lots of links have been provided in this tip for you to go on an adventure of discovering new SQL Server 2017 features and decide for yourself how it would benefit your company or provide higher level of security or deliver exceptional query performance and save you money. I hope this tip provides you enough reasons to convince your boss to upgrade your older version of SQL Server to SQL Server 2017.

Next Steps
  • Look forward to the next tips in this series.
  • Keep yourself up to date on latest versions of SQL Server.
  • View this 10 minute video describing new features in SQL Server 2017 by Microsoft or read here to understand all the features of SQL Server 2017.
  • Check out more tips on SQL Server 2017.


Last Updated: 2018-08-16


get scripts

next tip button



About the author
MSSQLTips author Ameena Lalani Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and started her journey with SQL Server 2000. She has implemented numerous High Availability and Disaster Recovery solutions.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, August 16, 2018 - 12:26:12 PM - Jimbo99 Back To Top

Meh, I stayed current and early adopted the 2016/2017 Linux version(s) thru the trial period, 180 days of experience with it, then a couple of interviews & the clients were Microsoft Windows environments and weren't impressed with the initiative to take on the Linux flavor of SQL Server. They were after 2-3 month contracts that were really week of Thanksgiving to week after New Years on projects tha the rate was a lowball & no benefits proposition and then you leave the place after any knowledge transfer(s). I learned my lesson the hard way, I guess I'm a better person for it. But that just means I can piddle around with it on the weekends as a hobby, when it's raining outside and I can't get outside and exercise.


Learn more about SQL Server tools