By: Ameena Lalani | Comments (3) | Related: > 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.
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.
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
