sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

Deprecated Features after SQL 2016

Issue 7 Number 6 June 2017

 

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.

https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016

 

Afterward by the Author

I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.

As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

Deprecated Features after SQL 2016

Elsewhere on this site:

 
Call SQL Consulting
 
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator