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”.