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:
- 2000: http://msdn.microsoft.com/en-us/library/aa176543(SQL.80).aspx
- 2005: http://msdn.microsoft.com/en-us/library/ms143232(SQL.90).aspx
- 2008: http://msdn.microsoft.com/en-us/library/cc280407(v=SQL.100).aspx
- 2008R2: http://msdn.microsoft.com/en-us/library/cc280407.aspx
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.
Filed under: SQL Server, SQLServerPedia Syndication | Tagged: Backward Compatibility, Denali, Deprecated Features, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, Upgrade |