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 503.914.6442

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

SQL Server Deprecated Features

Issue 5 Number 5 May 2013

Each time Microsoft introduces a new version of SQL Server they publish a new list of deprecated features that will be removed from SQL Server in some future version. The list is divided into two parts, those features which will definitely not be present in the next version and those features that will be removed in some unspecified later version.

Needless to say, if you are responsible for a SQL Server environment you need to be aware of features in this first list that will definitely not be available in the next version. You need to make sure that your next upgrade will not break your database applications. The older your code base, the more carefully you need to follow features scheduled to be removed in the next release.

Click HERE to view the current deprecation lists

The second list does not get as much attention because of the indeterminate time frame. The removal of these features does not present a clear and present danger. However there are currently some deprecated features on this list that might be difficult for you to replace. They should be addressed as early as possible.

There are two items in particular on this second list that you might want to look into well ahead of their removal.


In most cases, Microsoft deprecates a feature for good reason. Usually it is to make TSQL conform more closely to the ANSI standard for the SQL language or to retire an antiquated technology. However, the deprecation of Database Mirroring seems like it may have been proposed by the marketing department in Redmond rather than the technical architects.

Database Mirroring is a feature that is currently available in both Enterprise Edition and (with some limitations) in Standard Edition. It is deprecated now and will be removed in favor of Always On Availability Groups introduced in SQL Server 2012. Since Always On is a great improvement on SQL Server 2008 mirroring technology, the deprecation seems to make sense. That is, until you remember that Always On is only available in the Enterprise Edition.

What if your High Availability capabilities depend on Standard Edition mirroring and you can’t afford the huge price of Enterprise Edition? You might assume that surely the existing mirroring in Standard Edition would remain available. But you would be wrong. Microsoft states that your only option is to substitute Log Shipping, a technology that is over a decade old and has several disadvantages over mirroring.

You may require quite a bit of time to prepare for this removal, either to find the budget for Enterprise Edition or to integrate a third-party mirroring product into your platform architecture.


I am ambivalent in my attitude toward this deprecation. NOLOCK and READ_UNCOMMITTED query hints (two names for t the same hint) have been over-used and abused so badly that I am tempted to say good riddance. These hints were intended to solve specific blocking problems after carefully considering the consequences of dirty reads on data integrity. Now, in my work as a consultant I frequently run across code bases where a NOLOCK hint is routinely placed on all the tables joined in queries with little thought to the effect of dirty reads on data consistency.

By the same token, I can see that it would take a significant effort to remove the NOLOCK hints in these code bases without creating serious blocking problems.

It is true that Read-Committed Snapshot Isolation (RCSI) can work magic on many severe blocking problems without great effort. It is simple to enable RCSI on a database. However, it still requires a significant re-factoring of SQL code to make the change effective in codebases with a lot of locking hints. NOLOCK and READ_UNCOMMITTED hints override RCSI behavior. If you do not remove those hints from the code, the transactions still run under READ_UNCOMMITTED isolation and dirty reads are still possible even though you have enabled RCSI.

SQL Profiler and Trace Capabilities

This is a deprecation of a different sort. The removal of SQL Profiler is not going to break your database application code. But Profiler has been an essential tool in every DBA’s toolbox for many years and will be sorely missed by anyone responsible for the performance or troubleshooting of a SQL Server installation.

SQL Profiler and all the trace functions in SQL Server are being replaced by Extended Events (XE) technology. This is a major step forward because XE is unarguably more powerful and flexible than the tracing technology it replaces. However, there is a steep learning curve associated with XE

If you are the go-to person when something goes wrong with your SQL Server, I would get started now using XE. By default, SQL Server 2008 and later versions run a default XE session called system_health that captures a lot of very useful data. I would start by learning to extract data from this session. You will find examples on the web for queries that return everything you need to know about deadlocks, serious errors, etc. that occur on your server.

If you want to attack this learning curve in a structured way, I would recommend following Jonathan Kehayias’ series Extended Events

A Ray of Sunshine

You should be aware of deprecated features because they will eventually be removed from SQL Server but remember that it may take quite a long time for that to happen.

In any SQL Server development cycle, deprecations have a low priority. All other development has to be going pretty well before Microsoft will put much manpower into removing deprecated features. And, of course, you always have the option not to upgrade if features necessary to your platform are removed from SQL Server, However that decision might bring with it a whole different set of problems.

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 at 503.914.6442 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

SQL Server Deprecated Features

Elsewhere on this site:

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