Microsoft is always upgrading SQL Server. This is necessary in order to keep up with new technologies but it often requires changes and removal of existing features that many users and legacy applications have been depending on.
When these applications are connected to a newer version of SQL that no longer has the features that the applications need, then the application might need to be modified or be entirely rewritten.
Going, Going…
Microsoft gives warnings about the features it wants to remove from SQL Server. To do so, it publishes two lists prior to the actual removal. The first list contains features that will be removed at some unknown time in a future version. This is to give you a heads up on what’s coming so you can avoid using any of the deprecated features in new development.
…Gone
The second list contains features that definitely will be removed from the next version of SQL Server. This is the list we are going to talk about. It contains the deprecated SQL Server database engine features that are still available in SQL Server 2016 but will not be supported in future versions of SQL Server. There are hundreds of them.
I don’t have any accurate statistics about how many users will be affected by each change and I don’t think Microsoft does either. I am basing my comments on my knowledge of hundreds of clients over a period of twenty some years.
There are a lot of changes. However most of these changes will not affect you so I won’t discuss all of all of them.
Instead I am going to point you to a Microsoft page that lists all the changes that will happen in the version following SQL 2016. But first I am going to highlight what I see as the changes that, in my opinion,will be the most likely to be a problem for you.
SQL Profiler is Gone
Profiler, the tool that every DBA used to monitor problems in SQL Server was deprecated in favor of Extended Events a couple of versions ago. Now Microsoft has said that it will be gone in the next version.
In my opinion this is a mistake. I know that many DBA’s working for my clients have not made the change to Extended Events. (EE). It is true that EE is much more powerful than Profiler but the learning curve is steeper. I am sure that it will be missed by many DBA’s.
Many BLOB Datatypes are Disappearing
Text, ntext and image datatypes must be replaced by varchar(max), nvarchar(max), and varbinary(max) data types. There are a lot of legacy applications that were created using these datatypes.It might take a good deal of time to make the necessary changes.
Compatibility levels
Compatibility levels will only be available for the previous two versions of SQL Server. I know that several of my clients have legacy applications that need compatibility back to much earlier versions of SQL Server in order to run correctly. They may be in trouble when it comes time to upgrade.
DBCC Comands that are going away
DBCC INDEXDEFRAG, DBCC INDEXDEFRAG, DBCC SHOWCONTIG DBCC DBCC PINTABLE. All of them will be gone. If you currently use these commands you will need to learn the replacement commands listed in the link below.
My opinions expressed above are just that, opinions, and are incomplete. Here is the link to a page you should read that has all the details regarding deprecated features.