Deprecated & Discontinued Database Engine Features in SQL Server 2016

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Jun 14, 2017

Deprecated & Discontinued Database Engine Features in SQL Server 2016

Full and Transaction Log Backups with media password are already obsolete however we can restore backups with media password in SQL Server 2016. This feature will be condemned in future release.

There are many new noteworthy features in SQL Server 2016 and other upcoming versions. However, sometimes we do not give as much importance to the deprecated feature and upgrade databases to a newer version. As a roll back feature is not available, we should always see the deprecated feature before migrating databases into production.

Introduction:

This article designates the deprecated SQL Server Database Engine features that are still available in SQL Server 2016. These features are scheduled to be soon removed in the future releases of the SQL Server.

The Following Features will be discontinued in the Future Versions

Backup & Restore:

Full and Transaction Log Backups with media password are already obsolete however we can restore backups with media password in SQL Server 2016. This feature will be condemned in future release.

RESTORE { DATABASE | LOG } WITH [MEDIA]PASSWORD continues to be deprecated.

BACKUP { DATABASE | LOG } WITH PASSWORD and  BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD are discontinued.

sql database log

Compatibility Levels:

Upgrades from version 110 (SQL Server 2008 and SQL Server 2008 R2) will not be possible. We have to upgrade the database to SQL Server 2012 and only then can we upgrade the database to the current version. However, database compatibility level 100 will be supported. Compatibility levels are only available for the last two versions.

sql server upgrade
Image Source: 
https://www.mssqltips.com/sqlservertip/3688/prepare-for-an-upgrade-with-the-sql-server-2016-upgrade-advisor/

Encryption:

Encryption using RC4 or RC4_128 is deprecated and is scheduled to be removed in the next version. Decrypting RC4 and RC4_128 is not deprecated. We should start using another encryption algorithm such as AES.

Remote Stored Procedure:

Remote stored procedure starts with Transact-SQL distributed transaction used by Microsoft Distributed Transaction Coordinator (MS DTC).

Remote servers are supported in SQL Server 2016 for backward compatibility only. New applications should use linked servers instead.

Table Hints: 

Table hint “WITH” keyword is a deprecated feature and this will be removed in future versions. Newly developed apps should be avoided using “WITH” keyword.

Separating Hints by Spaces rather Than Commas is a Deprecated Feature:

This feature will be removed in the upcoming version of Microsoft SQL Server. Do not use this feature in any new development work, and modify applications that currently use this feature as soon as possible.


SQLMaint Utility

SQLMmaint utility is used to execute database maintenance plans created with previous versions of the SQL Server. This feature will be obsolete in the next versions.

We should use the SQL Server maintenance plan feature as a replacement for it. 


Discontinued Features in SQL Server 2016

  • SQL Server 2016 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.

Conclusion:

The above deprecated features are reserved to be removed in a future release of SQL Server, however there is no timeline decided by Microsoft when these features will be removed. We should test old applications before migrating to the new versions of SQL Server. Deprecated features should not be used in any new development work. For any questions on the topic click below. You can also leave a comment in the field below.
Ask Anil