• 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!


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.

SQL Server Deprecated Features – Part 2

In part 1 of this mini-series on deprecated features, I explained what that meant and where to find more information. This entry will focus on issues I’ve personally encountered in this area which require close attention not just to deprecation, but actual discontinuation.

While rewriting all of our “data collection” scripts in PowerShell and SQL-SMO (because SQL-DMO was discontinued in SQL 2008 R2), I discovered an unexpected “backwards compatibility” issue.  When I ran the database collection script against certain SQL 2000 servers, the script “failed” against some databases. My first suspicion was that the databases were “offline” and I needed to add a check for that condition.  However, I added that check and still got failures. When I explicitly connected to the instances with the problem databases using SQL Server Management Studio (SSMS), I got a semi-surprise – they all had databases in 65 compatibility mode… and those were the databases which caused my script to fail.   So, instead of pulling the database compatibility setting via SQL-SMO, I now have to select it using the Invoke-Sqlcmd SQLPS cmdlet using a T-SQL statement and verify that it is greater than 65 before continuing with the collection of the database’s metadata.

What surprised me was that this happened on a SQL 2000 instance.  I knew that SQL-SMO was not supported on SQL 6.5 instances, but it did not occur to me that it would also be invalid for a database in 65 compatibility mode on an instance whose versioning supported SQL-SMO.

At that time, I decided against collecting further metadata from 65 compatibility mode databases.  There are too few to justify the additional scripting required.  The main thing is that we will be able to report how many 65 databases are still around.

Consequently, the next issue I realized was that the SSMS tool cannot even manage a database in 65 compatibility mode as SSMS uses SQL-SMO almost exclusively behind the scenes. Remember, I’m not in a day-to-day production DBA role to have encountered this scenario.

Then, I found another “backwards compatibility” issue when using the SSMS 2008 R2 to script a drop/create of a table from a database on a SQL 2000 instance.  The database containing the table was in 80 compatibility mode (so not quite the same issue as previously discussed).  What I noticed in the auto-generated T-SQL statement was that the “exist” check for the table prior to the drop, did not select from the SQL 2000 system tables, but from the newer system views in SQL 2005 and up.  Consequently, if I had tried to execute the generated script, it would have failed since sys.objects does not exist in SQL 2000.  I have verified that this has actually been the case since SSMS 2005, so hopefully everyone besides me was already aware of this gotcha.

Bottom line: be very aware of the version of the tools you are using to access down-level instances and down-level databases.  In the words of Ronald Reagan, “Trust, but verify”.

SQL Server Deprecated Features – Part 1


Okay – there is still officially a couple of weeks to go, but here in Southeast Texas, the redbuds have bloomed and azaleas are next!  As we move from winter to spring – many people (in the Northern Hemisphere, anyway) think of “spring cleaning” and time to clean out the garage and clean out their wardrobe – you know – throw away all the stuff you’ve kept for years thinking you’ll use it or fit back into it. But, of course, expiration dates have come and gone, and even if you could fit into that outfit from 10 years ago – you’d get some strange looks on the street.

It is the same with our databases – every now and then we have to check the expiration dates.  With SQL Server “Denali” on the horizon, I thought that a mention of old features of SQL Server which are not / will not be in newer versions was an appropriate topic to tackle.

This concept is referred to as “deprecation”.  According to The New Oxford American Dictionary, the verb “deprecate” means to “express disapproval of”.  Thus, when Microsoft lists a feature as “deprecated” they are expressing disapproval in the continued usage of that feature with the intent to remove the feature entirely in a future release.

With each release of a new version of SQL Server, Microsoft documents “Backwards Compatibility” with prior versions. This documentation is typically divided into four components – deprecated features, discontinued features, breaking changes, and behavior changes.  All four areas are important to review when moving to a new version of SQL Server. However, it isn’t like Microsoft pulls the plug without warning.  Discontinued features were previously deprecated, so you usually have at least one release (i.e. several years!) to address these known issues.

It is especially important to note whether deprecated features are disappearing entirely or if you just need to start using a different syntax.  For instance, in SQL Server 2005 many of the object management system stored procedures (e.g. sp_addlogin) were deprecated and replaced by equivalent CREATE <object> and ALTER <object> T-SQL commands.  You can still use the old feature for another version (or sometimes two), but it will not be enhanced if a new parameter or option is added in a new release.

The Upgrade Advisor for each version will attempt to scan your database instance for code or settings which will be impacted by a new release.  However, that obviously means that code has to reside in the database in unencrypted stored procedures. So, yes, usually you have to find your own way to scan all of your scripts, programs, etc. for the obsolete commands.

If you focus on replacing\discontinuing the deprecated features over the course of each release, you will be ahead of the game when those features are actually discontinued in a future release.

To review the Backwards Compatibility issues documented for each SQL Server version, check the MSDN online books shown here:

One of the biggest issues to be aware of when planning to upgrade\migrate from SQL Server 2000 to SQL Server 2008 is that SQL Server 2008 will not support databases whose compatibility mode is not at least 80. In my experience, there are still a lot of databases sitting on SQL Server 2000 instances running in 60, 65, and 70 compatibility modes!  Come on people – you’ve had 10 years to get those updated to 80 compatibility mode!  Of course, you probably thought those systems would be retired by now, didn’t you? Or you thought that you’d be retired or in another job and it would be someone else’s problem….or maybe you just had the bad luck of inheriting someone else’s obsolete system.  In any case, the first order of business before going any further is to figure out how to get those databases running successfully in compatibility mode 80.  Good luck!

And, don’t forget once you get those 80 databases migrated to SQL Server 2008 in compatibility mode 80, you’d better work on getting them fully into 100 mode!  Microsoft has stated that each version will only support two levels down for an upgrade. Thus, when Microsoft SQL Server “Denali” rolls out, it will only support 90 and 100 database compatibility modes for upgrades.