Microsoft SQL Server 2017 Deprecated & Discontinued Features- Go Through

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Scenario:-

One of my UK based client , wanted to upgrade its production database to SQL server 2017 version (Enterprise edition) from existing SQL server 2014 (Enterprise edition).

Obviously one must start upgrading from Non-prod first then prod approach. But before putting your hand in this requirement, we first need to understand/know about deprecated and discontinued features of SQL server 2017 so that our upgrade go smoothly on functional testing.

So lets first understand the two concepts here:-

1. Deprecated (list of features that will be discontinued or removed from future version of SQL server so use those features accordingly )

2. Discontinued (list of features that has been removed and you will not see those features so keep this in mind before upgrade)

Below describes the Database Engine features that are no longer available in SQL Server 2017 anymore.
Reference:-
https://docs.microsoft.com/en-us/sql/database-engine/discontinued-database-engine-functionality-in-sql-server?view=sqlserver-ver15

Please note: – No features were discontinued in SQL Server 2017 (14.x) so we will look here what features were discontinued in SQL server 2016 and obviously same will not be present in SQL server 2017 and so on.

Discontinued features in SQL Server 2016 (13.x)

SQL Server 2016 (13.x) is a 64-bit application. 32-bit installation is discontinued though some elements run as 32-bit components.
Compatibility level 90 is discontinued.
ActiveX subsystem is discontinued. Use command line or PowerShell scripts instead
Startup parameters -h and -g. For more information, see Database Engine Service Startup Options.
Secure Sockets Layer (SSL) encryption is discontinued. Use Transport Layer Security (TLS) instead.

Note: – From development points, there are no such changes between SQL 2014 and SQL 2017 except below few.

Breaking Changes to Database Engine Features in SQL Server 2017 (14.x)

Below piece describes breaking changes in the SQL Server 2017 (14.x) Database Engine. These changes might break applications, scripts, or functionalities that are based on earlier versions of SQL Server. You
might encounter these issues when you upgrade.

Reference:-

https://docs.microsoft.com/en-us/sql/database-engine/breaking-changes-to-database-engine-features-in-sql-server-2017?view=sql-server-ver15

CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. Beginning with SQL Server 2017 (14.x) Database Engine, an sp_configure option called clr strict security is introduced to enhance the security of CLR assemblies. clr strict security is enabled by default, and treats SAFE and EXTERNAL_ACCESS CLR assemblies as if they were marked UNSAFE. The clr strict security option can be disabled for backward compatibility, but this is not recommended. When clr strict security is disabled, a CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. After enabling strict security, any assemblies that are not signed will fail to load. Also, if a database has SAFE or EXTERNAL_ACCESS assemblies, RESTORE or ATTACH DATABASE statements can complete, but the assemblies may fail to load. To load the assemblies, you must either alter or drop and recreate each assembly so that it is signed with a certificate or asymmetric key that has a corresponding login with the UNSAFE ASSEMBLY permission on the server

The MD2, MD4, MD5, SHA, and SHA1 algorithms are deprecated in SQL Server 2016 (13.x). Up to SQL Server 2016 (13.x), a self-signed certificate is created using SHA1. Starting with SQL Server 2017 (14.x), a self-signed certificate is created using SHA2_256.

Breaking Changes to Database Engine Features in SQL Server 2016 (13.x), same apply to SQL
2017

Reference:-

https://docs.microsoft.com/en-us/sql/database-engine/breaking-changes-to-database-engine-features-in-sql-server-2016?view=sql-server-ver15

The sample_ms column of sys.dm_io_virtual_file_stats has expanded from an int to a bigint data type. The TimeStamp column of sys.fn_virtualfilestats has expanded from an int to a bigint data type.
Under database compatibility level 130, implicit conversions from datetime to datetime2 data types show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values. Use explicit casting to datetime2 datatype whenever a mixed comparison scenario between datetime and datetime2 datatypes exists. For more information, see this Microsoft Support Article.
Under database compatibility level 130, operations that perform implicit conversions between certain numeric and datetime data types show improved accuracy and can result in different converted values. This includes usage of functions that require calculations such as, for example DATEDIFF and ROUND.

Deprecated Database Engine Features in SQL Server 2017

Below piece describes the deprecated SQL Server Database Engine features that are still available in SQL Server 2017 (14.x). Deprecated features should not be used in new applications.
When a feature is marked deprecated, it means:

The feature is in maintenance mode only. No new changes will be done, including those related to interoperability with new features.
We strive not to remove a deprecated feature from future releases to make upgrades easier. However, under rare situations, we may choose to permanently remove the feature from SQL Server if it limits future innovations.
For new development work, we do not recommend using deprecated features.
You can monitor the use of deprecated features by using the SQL Server Deprecated Features Object performance counter and trace events.

The value of these counters are also available by executing the following statement:

SELECT * FROM sys.dm_os_performance_counters
 WHERE object_name = 'SQLServer:Deprecated Features';

Note:-
Since deprecated features still available with SQL 2017 version, however it may be discontinued from future version of SQL example: – SQL 2019 and so on.

Please refer below link for the complete list of deprecated features and its replacement.

https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-ver15