• Categories

  • Recent Posts

  • Archives

  • Copyright Notice

    Copyright © Nancy Hidy Wilson, 2010-2013. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Nancy Hidy Wilson and nancyhidywilson.wordpress.com with appropriate and specific direction to the original content.

Deprecated Features in SQL Server 2012


Earlier in the year, I wrote a series of posts on Deprecated Features with a promise to revisit as the final version of SQL Server “Denali”, now known as SQL Server 2012, neared. With RC0 recently released, now is the time for that review!

To recap, deprecated features are still available in a version, but are targeted for removal in a future version. Thus, you should avoid using these features in any new development work and consider replacing them where currently used as soon as convenient. So without further delay, the features which are still available in SQL Server 2012 Database Engine, but now deprecated include:

Data programmability features which should be replaced with Windows Communications Foundation (WCF) or ASP.NET:

Slipstream functionality will go away – sort of. Starting with SQL Server 2012, the command line parameters /PCUSource and /CUSource should be replaced with the Product Update feature (/UpdateSource) which I have previously discussed. Although the old parameters are still valid, I’d switch to the new parameters ASAP as they are much easier to use and maintaining your source is also simplified.  

There are also Database Engine Backup and Restore T-SQL command changes: RESTORE { DATABASE | LOG } WITH [MEDIA]PASSWORD is deprecated. While BACKUP { DATABASE | LOG } WITH PASSWORD and BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD are actually discontinued in SQL Server 2012. These commands have no replacement.

Database Compatibility level 90 and upgrades from SQL Server 2005 will not be supported in the version after SQL Server 2012.  This is in keeping with Microsoft’s stated support model of only supporting 2 down-level versions for upgrades. You should be well aware of this by now, but I’m including it as a reminder for those new to SQL Server version management. For example, SQL Server 2000 (or database compatibility level 80) is not a supported version to upgrade to SQL Server 2012.   

The ability to return results sets from triggers will not be supported post-SQL Server 2012.

As stronger encryption algorithms are now available, encryption using RC4 and RC4_128 has been deprecated. However, decrypting RC4 and RC4_128 has not been deprecated.

Several stored procedures, a global variable, and SET command related to remote servers have been deprecated. It has been recommended for several versions now to replace remote servers with linked servers.  Hopefully you have already done this task.

SET ROWCOUNT for INSERT, UPDATE, and DELETE has been replaced by the TOP keyword, so if you have been using SET ROWCOUNT to limit the number of rows affected by your INSERT, UPDATE, or DELETE statements, you’ll need to switch these to use the TOP keyword instead.

If you’ve been using the table hint HOLDLOCK without parenthesis, then you will need to add the parenthesis in the next release of SQL Server.

On the tools side of things, the SQLMAINT utility has been deprecated with the recommendation to use SQL Server maintenance plans as the replacement.

And if you want to really get a jump on things, along with the list of deprecated features just described for the “next” version of SQL Server (after 2012), you can start eliminating the usage of features listed in “Features Not Supported in a Future Version of SQL Server” – both can be found here:  http://msdn.microsoft.com/en-us/library/ms143729(v=SQL.110).aspx

If you are using the Full-Text feature of SQL Server then you’ll want to check-out the list of deprecated items specific to that feature here: http://msdn.microsoft.com/en-us/library/cc646010(v=SQL.110).aspx. These include many stored procedures (e.g. sp_fulltext_catalog) which have been replaced with new T-SQL syntax (e.g. CREATE\ALTER).

And, finally, if you are using components other than the Database Engine (i.e. SSAS, SSIS, SSRS), then you’ll need to review the deprecated/discontinued lists for them in their respective Backward Compatibility sections of Books Online.

As I pointed out in Part 3 of my earlier blog series, you can detect most deprecated features by using the SQL Server: Deprecated Features object performance counter and trace events.   A future blog will discuss which previously deprecated features have actually been discontinued in SQL Server 2012 – meaning you will have to address them before upgrading.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: