• 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.
  • Advertisements

Discontinued Features in SQL Server 2012 – Part 4

In the previous installment on this topic, I looked at discontinued features in SQL Server 2012 which may mostly impact your administration scripts.  Today, we are widening the net and looking at discontinued features which may impact either the application layer or administration programs and scripts. Most of these items have been deprecated as far back as SQL Server 2000, so it isn’t like they have gone away overnight. However, if you are still running on SQL Server 2000, then you really haven’t had a need to go replace them…until now.

SQL Mail is finally gone! It would have been totally fine with me if SQL Mail had been discontinued as soon as Database Mail was added in SQL Server 2005.  However, you do lose functionality (receive mail processing) with Database Mail, but the trade-off is much improved reliability, security, supportability, and scalability. For example, you no longer need an Extended MAPI client (e.g. Outlook) installed; you don’t run the risk of attacks via reading mail; and Database Mail is supported on clusters. So, if you are still using the read\process mail functionality of SQL Mail, you’ll need to find another method for your application to use before upgrading to SQL Server 2012.  If all you are doing are send mail tasks, including notifications from SQLAgent, then the switch to Database Mail is a no-brainer and you can do it now in SQL Server 2005/2008/2008R2. However, this is more than just a configuration change; it does require usage coding changes for example from xp_sendmail to sp_send_dbmail.  

Unless you’ve been stuck on SQL Server 2000, you have probably already started converting applications using SQL-DMO (Database Management Objects) to use SQL-SMO (SQL Server Management Objects) when you found you wanted to access newer features in SQL Server 2005+ that were not available via SQL-DMO.  If you want to use your SQL-DMO scripts against SQL Server 2012 – you must convert to SQL-SMO; SQL-DMO is discontinued.

If you have databases in compatibility mode 80, it might be because the code using them is still using the old non-ANSI outer join syntax “*=” or “=*”.  This syntax is not supported in later compatibility modes and because compatibility mode 80 is not supported in SQL Server 2012, this syntax is no longer recognized in SQL Server 2012 in any scenario.  You’ll need to get all of this code up-to-date with current ANSI join syntax used in the FROM clause.

Another T-SQL syntax which is disappearing is the COMPUTE / COMPUTE BY clause in SELECT statements; you will need to convert these statements to use the ROLLUP option of the GROUP BY clause.

If you tried to speed up the results returned to your client or force a particular query plan by using the FASTFIRSTROW query hint, you’ll need to switch to the OPTION (FAST n) syntax as FASTFIRSTROW is no longer available.  For more info on the overall impact of using this option, see this blog from the SQL Server Query Optimization team and this one from SQL MVP Grant Fritchey. As with all query hints, you should re-evaluate these periodically as they may no longer be as beneficial as when originally added.

In SQL Server 2012, you can no longer invoke an ad-hoc error message number using RAISERROR integer ‘message’ syntax as in: RAISERROR 50111 ‘Sample error message’.  You’ll need to convert to the RAISERROR() syntax:  RAISERROR (‘Sample error message’, 10, 1). But, if you want to really get your code current, convert to TRY…CATCH (available since SQL Server 2005) and use THROW (new in SQL Server 2012). There are some differences in behavior between RAISERROR and THROW and these are documented in BOL’s THROW entry.

I’ve only highlighted a sample of discontinued features in this set of articles; for the complete list of discontinued database engine features in SQL Server 2012, please reference BOL. And for help finding out if you are using these features (which were deprecated in earlier releases), I covered that here.

Happy upgrading!


Discontinued Features in SQL Server 2012 – Part 3

Part 1 discussed some discrepancies between documentation of discontinued features in SQL Server 2012 and actual implementation. Part 2 discussed the database compatibility level minimum requirement. Now, in this installment, let’s look at some features which have disappeared from SQL Server 2012 which may impact your administration scripts.

The first one, the DATABASEPROPERTY function, I already covered in Part 1. While it doesn’t appear to be completely gone, the documentation states that it is gone. So, as you edit your scripts for all the other items mentioned next don’t forget to make the change to use DATABASEPROPERTYEX instead of DATABASEPROPERTY.

Next up, sp_dboption is gone. That’s right – it’s not there… You get a nice red error message in SSMS when you try to use it.

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure ‘sp_dboption’.

Everything you needed to set using sp_dboption is now available via the ALTER DATABASE syntax – and pretty much has been since SQL Server 2000. But, if you are as “experienced” as I am (going back to 4.21), then you may still have some old scripts laying around with sp_dboption in them.  Unless you still have some 7.0 or earlier systems on which to use those scripts – update them to use ALTER DATABASE and they should work on SQL Server 2000 and up (with an exception for the database ownership chaining option which was added in SQL Server 2000 SP3 and only available to set via sp_dboption in SQL Server 2000).

Now – how about a review of your Backup and Restore scripts?  Did you make use of the WITH PASSWORD or WITH MEDIAPASSWORD options on your BACKUP DATABASE and BACKUP LOG commands? If so, you’ll need to remove those options for use on SQL Server 2012. These options have been deprecated since SQL Server 2005, so in theory you should have stopped using them at that point in time. You can still use these options with the RESTORE command for those old backups which used a password, but you can’t make any new backups using the password options.  Keep in mind though that the ability to use the RESTORE command for backups with passwords is deprecated and will be removed in future version.

This next one will really date you (and me!) – do you have RESTORE DATABASE…WITH DBO_ONLY syntax in any scripts?  If so, you should have replaced WITH DBO_ONLY as WITH RESTRICTED_USER starting with SQL Server 2000.

Do you run scripts to dump the meta-data from the system tables\views?  Do they include the sys.database_principal_aliases view? If so, it is gone now, too. You’ll receive error 208 (invalid object name) if you reference it.  Aliases have been replaced by roles.

OK – so now you know what to look for, but how about an easy way to find where these keywords are used in your scripts?  Here’s a quick PowerShell command example (adapted from http://guyellisrocks.com/powershell/powershell-grep/) to help: 

Get-ChildItem -include *.sql -recurse | Select-String “sp_dboption”

Have fun hunting down these obsolete keywords!

Discontinued Features in SQL Server 2012 – Part 2

Previously I discussed a couple of features where there is a discrepancy between the documentation and the implementation as to their status as either discontinued or deprecated in SQL Server 2012. Today, I’m looking at another discontinued feature which you will need to identify and address before upgrading to SQL Server 2012 – database compatibility level 80.

A database’s compatibility level is used by the database engine to ensure that most behavior is consistent to that version for the specific database.  This database-level setting is intended as a work around to allow applications to upgrade to a newer version of the database engine for which usually all databases can derive some benefits without requiring the application to make any changes (in theory). If an individual application database is using a feature or syntax previously supported for which the behavior changed in the later version, then you can set a down-level compatibility to keep the original behavior from that version….until your application development team or your vendor can make the code compatible with the current version of SQL Server.  

Beginning with SQL Server 2008, Microsoft’s announced life cycle policy for SQL Server was that a new version will attempt backwards compatibility with only the 2 previous versions. Since databases in SQL Server 2012 are compatibility level 110, then only compatibility level 100 (used for both SQL Server 2008 and SQL Server 2008 R2) and compatibility level 90 (SQL Server 2005) are additionally supported.  Thus, database compatibility level 80 (SQL Server 2000) will not be supported in SQL Server 2012 and is therefore classified as a discontinued feature.

If you are a fairly new DBA who inherited your database systems and don’t know their history, you’d better check all the databases carefully for compatibility level.  While SQL Server 2008 and SQL Server 2008 R2 both support databases in 80, 90, and 100 compatibility mode, SQL Server 2005 supports databases in 70, 80, and 90 compatibility modes (and allows 60 & 65!).  Thus, although you can upgrade a SQL Server 2005 instance to SQL Server 2012 – you need to check that all of the application databases are at compatibility level 90. You might be surprised (or not) to find that you still have databases which exist with compatibility level 6x on your SQL Server 2005 instances! 

So, how do you find these? One way is to use a multi-server query using a CMS group containing all your servers…

Select [name],[compatibility_level] From sys.databases

Where [compatibility_level]< 90;

How do you fix it? Well, ultimately you have to run one of these commands:

— If source system is SQL 2005

ALTER DATABASE reallyold_database


— If source system is SQL 2008 or 2008 R2 and compatibility_level is 80,

— then you might as well take it as far as you can go!

ALTER DATABASE reallyold_database


But, first you’ll have to figure out what is keeping it from being at the higher compatibility level already.  Check the SQL Server documentation under “ALTER DATABASE Compatibility Level (Transact-SQL)” for details of differences between compatibility levels. Be sure to check the SQL Server 2008 version of the documentation for differences between the 80 and 90 compatibility levels. In some cases, it is simply that the database was upgraded and the former DBA didn’t realize that they needed to change the compatibility level after the upgrade.  And, of course, don’t you now forget to change upgraded databases to compatibility level 110 after you complete the upgrade to SQL Server 2012!


Discontinued Features in SQL Server 2012 – Part 1

Now that SQL Server 2012 has officially been released, it is time to start revisiting deprecated features.  But, first, a definition reminder:

  • “Discontinued” means that the feature is not available in SQL Server 2012.
  • “Deprecated” means that the feature will be removed in a future version.  “Deprecated” also typically means that there are no updates to this feature in the current release.

Thus, I will first review a couple of the features which you may have used in prior versions which were deprecated in earlier versions and now discontinued in SQL Server 2012 – or are they?

SOAP/HTTP Endpoints

Per the note in the CREATE ENDPOINT BOL link this feature is discontinued. However, it is also currently listed in BOL under “Deprecated SQL Server Features in SQL Server 2012” which implies it won’t go away until a release after SQL Server 2012.  So which is it – gone already or still waiting to go?  A test of running sample code to create such an endpoint in SQL Server 2012 results in the error message shown below supporting it is now actually discontinued, not deprecated for removal later. Thus, if you have used these endpoint types, you will need to find them and convert to Windows Communication Foundation (WCF) or ASP.NET before upgrading to SQL Server 2012. Check this BOL reference for assistance.

Here’s the sample code that was executed which was copied from SQL Server 2008 R2 BOL:


This function has been replaced and superseded with the DATABASEPROPERTYEX function since SQL Server 2005.  Intellisense in SSMS 2012 will flag that it doesn’t recognize this as valid syntax (see image below).  The unexpected thing here is when you execute a query using DATABASEPROPERTY in SQL Server 2012 – you don’t get an error!  It seems to still work (see results image below). I don’t know about you – but this was not the result I expected.  If DATABASEPROPERTY is no longer valid as indicated in SSMS (and BOL) – shouldn’t the execution return an invalid function error?  At any rate, it appears you may have a little longer to track down your usage of this function and update it to use DATABASEPROPERTYEX as DATABASEPROPERTY does still work for the time being.   

Obviously, it doesn’t hurt to start replacing the usage of deprecated features as soon as possible. Then, you don’t have to worry as much about which future version actually discontinues the feature, if you have already discontinued your usage of it.

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.

SQL Server Deprecated Features – Part 4

In part 1 of this series, I discussed how Microsoft notifies us of changes between product versions and provided links to the “Backward Compatibility” sections in BOL (Books Online) for SQL Server 2000/2005/2008/2008R2.  In part 2, I discussed some compatibility issues with deprecated features which I’d run across.  In part 3, I covered tools to detect the usage of deprecated features.  Now, in part 4, I’ll take a look at what is coming in the next release of SQL Server – code-named “Denali” aka SQL 11.

While there is not an announced release date yet for Denali, the first CTP (Community Technology Preview) was released in November 2010 and included the availability of BOL for Denali.  This includes the “Backward Compatibility” section for Denali. When reviewing any of the Denali BOL articles, you’ll note that there is a disclaimer at the top of each page that this is “preview only” and “subject to change”.  So, you may want to check back frequently between now and RTM for any changes – especially as some sections still contain either placeholders or content from 2008/R2 – particularly for components other than the database engine.

If you are currently in the process of upgrading to SQL Server 2008 or R2 and making changes to your applications, then you’ll probably want to go ahead and review the changes announced for Denali and beyond.  If it makes sense, go ahead and start incorporating those changes now.  You should focus first on the discontinued features in Denali – meaning you will have to make a change to move to Denali if you rely on these features; so why not get rid of them now?  (Side note: the good news is that this list is currently shorter than expected based on the SQL 2008 R2 list of “Features Not Supported in the Next Version of SQL Server”. But that could change by RTM since it appears to have been Microsoft’s intent to fully remove them in Denali when R2 BOL was released.)

Currently on the discontinued list:

  • Databases in 80 compatibility mode; must be in 90 compatibility mode to upgrade to Denali (SQL 11)
  • PWDCOMPARE function parameter for to designate a login prior to SQL 2000
  • No AWE support; No support for over 4GB of physical memory on 32-bit OS

Next, tackle the Denali deprecated features list in the section labeled “Features Not Supported in the Next Version of SQL Server”. This would include the features expected for removal in SQL 12.  Yes, that is a long way off, but most of the items in the list have been announced for deprecation since at least SQL 2005, so it’s time to consider their replacement and/or retirement.  Items up for consideration:

  • Databases in 90 compatibility mode; expected to require 100 compatibility mode to upgrade to SQL 12
  • replace the DATABASEPROPERTY function with the DATABASEPROPERTYEX function
  • replacing sp_dboption stored procedure with ALTER DATABASE
  • remove usage of *= or =* (use the ANSI Join syntax)
  • sqlmaint utility
  • And more…considering many of these were carry-overs from SQL 2008/R2 and expected for removal in Denali, they should already be on your priority list as they may still be removed in Denali since the Denali BOL notice still includes the “subject to change” wording.

If you are really ambitious, then start perusing the list of “Features Not Supported in a Future Version of SQL Server”.  And, yes, some of these look familiar as they have been previously announced. Most notable that you may have missed in SQL 2008 deprecation announcements: Not ending Transact-SQL statements with a semicolon.  Yes – you read that right! At some point in a future release of SQL Server, T-SQL statements will finally be required to end with a semicolon (;).  That’s an easy one – this is an ANSI SQL standard and has been supported since at least SQL Server 2000. It is already required for some T-SQL statements (such as the statement prior to a WITH defining a CTE and the MERGE statement), so just start typing that extra character and get used to it.

As RTM for Denali gets closer, I’ll revisit this topic to note any significant changes.

SQL Server Deprecated Features – Part 3

In this third installment of my mini-series on SQL Server deprecated features (see part 1 and part 2), I will look further at tools to help detect the usage of deprecated features.

SQL Server 2000

For SQL Server 2000 instances, you are pretty much stuck with using the Upgrade Advisor tool for the version of SQL Server to which you are upgrading. In addition, you need to read BOL Backwards Compatibility (links available in part 1) as a reference and manually search your code and database configurations.

SQL Server 2005

Starting with SQL Server 2005, Microsoft has added some detection capabilities into the SQL Server Profiler tool.  Two new event classes were added to report “Deprecation Announcement” feature usage and “Deprecation Final Support” feature usage.

Per the text inside Profiler when “Deprecation Announcement” is selected: Occurs when you use a feature that will be removed from future version of SQL Server, but will not be removed from the next major release of SQL Server…

Per the text inside Profiler when “Deprecation Final Support” is selected: Occurs when you use a feature that will be removed from the next major release of SQL Server…

If you setup your profiler trace to record to a table (I named mine Deprecated_Features_Trace_tb), then you can query it as follows:

SELECT * FROM [dbo].[Deprecated_Features_Trace_tb]  
WHERE EventClass IN (125,126)

EventClass 125 corresponds to “Deprecation Announcement” and EventClass 126 corresponds to “Deprecation Final Support”.

Ironically, you may find that you want to use a Column Filter on ApplicationName to exclude the Profiler tool and SQL Agent (and maybe other internal Microsoft features) which are currently using deprecated features themselves.  For example, when recording the profiler results to a table, the table is created using ntext and image datatypes which have been deprecated and are therefore reported events. Alternatively you could filter on DatabaseName and exclude the system databases.  However, you need to be absolutely sure that no one has created non-Microsoft supplied objects in those databases if you chose to filter on database name.

SQL Server 2008

In SQL Server 2008, an explicit performance counter object has been added for deprecated features with specific “instances” of deprecated features. This enables you to use the DMV sys.dm_os_performance_counters to query if the usage of any of these features has occurred within your SQL Server 2008 instance.

SELECT * FROM sys.dm_os_performance_counters 
WHERE [object_name] LIKE '%:Deprecated%' AND cntr_value > 0

Of course, this won’t tell you where the deprecated features are used, but at least you will know whether you need to go hunting in-depth.

Another new feature in SQL Server 2008 which can help with this task is Extended Events. You can setup an Event Session to capture both “deprecation_announcement” events and “deprecation_final_support” events.  If you are new to Extended Events, then you must read Jonathan Kehayias’ old blog and/or new blog – these links point specifically to all of his Extended Event tagged entries.  Jonathan has written a “XEvent a Day” series and much more. If you want to learn about Extended Events – he’s the man!

Final Thought for Detecting Deprecated Features

For all versions, however, no matter what tools you’ve initially used, always, always, always run the Upgrade Advisor.