Feb 122009

SQL Server is a mature product, and as the product grows older, new features are added (we all know about those!) and some features are removed.  However, to avoid having customers upgrade to the latest and greatest version of SQL Server, Microsoft provide warnings that features will be deprecated (a fancy word for “removed”) in the next version, or in some future version.

If you’re considering upgrading your application, the first test you should do is detect if you’re using deprecated features.  You can either skim the list of deprecated features (look up “Deprecated features” in the Books Online index), or you can run a Profiler trace.

There are two Deprecation events in Profiler, residing in the not-so-hidden category of “Deprecation”.  These are:

  • Deprecation Announcement – captures those events that will be around for the next version, but will be removed in future.
  • Deprecation Final Support – captures those events that will NOT be in the next version.

Set up a Profiler trace on these two events.  Note that you should tick the “Show All Events” and “Show All Columns” options to be able to capture all relevant information.  Failure to “Show All Columns” can result in not capturing anything.

That done, sit back and wait for the messages to come rolling in.  On the other hand, if you’re running this on a test server with zero workload, you’ll need to produce some deprecated events to capture.  Again, look up “Deprecated Featres” in the Books Online index (on the web at: http://msdn.microsoft.com/en-us/library/ms143729.aspx, with a link to the 2005 list), and find a feature to use.


The deprecation warning message is kind enough to give you a hint on how to perform the function in future.

That’s all there is to it!  Now you can upgrade to SQL Server 2008 with a stronger feeling of confidence that Microsoft haven’t dropped support for a particular feature.  This doesn’t replace testing, of course, but will at least reduce the number of errors you get when you do upgrade.