SQL Server 2005 backward compatibility issues to consider

In the first part of this two-part series, contributor Serdar Yegulalp looks at deprecated functions in SQL Server 2005 and how to avoid ending up with broken SQL server applications.

It's always good to be on top of the latest technology, and many DBAs with SQL Server 2000 or 7.0 setups are eyeing a migration to SQL Server 2005. The same DBAs typically have to rework SQL server applications to operate correctly with SQL Server 2005, such as custom databases, in-house front-end programs and others.

Here's a rundown of the most important SQL Server 2005 upgrade compatibility issues for consideration -- the things most likely to immediately break an application if it's switched. (For the sake of this article, an "application" is anything that makes use of SQL Server -- whether it's a product that contains a SQL Server database or just a front end that passes commands.)

One of the most important backward-compatibility issues involves the elimination of system tables from SQL Server 2005. If you have existing SQL Server applications that reference system tables directly, you will have to update them.

The good news is that SQL Server 2005 does provide backward compatibility for such functions through compatibility views. Adam Machanic, who is also a contributor to SearchSQLServer.com, wrote a tip that details how compatibility views work. It's worth reading if you are planning to port SQL Server applications that use system table references regularly. (Side note: The sample databases, Northwind and Pub...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Microsoft SQL Server 2005 End of life comes for SQL Server 2005 SP2, 2008 SQL Server Reporting Services Fast Guide SQL Server Service Broker Tutorial and Reference Guide Tips for tuning SQL Server 2005 to improve reporting performance SQL Server consolidation: Why it's an optimization technique Parent-child dimensions in SQL Server 2005 with Analysis Services MDX Enforcing data integrity in a SQL Server database SSIS error message due to installation problem on SQL Server 2005 Should you upgrade to SQL Server 2005 or SQL Server 2008? Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005 Microsoft SQL Server 2005 Research SQL Server Migration Strategies and Planning eZines and eBooks for SQL Server professionals New SQL Server 2008 R2 CTP set for November PASS Summit 2009 Preview Are data warehouses made for the cloud? Q&A: Moving forward with SQL Server in the cloud SQL Server Mailbag: Migrating down to Standard Edition Microsoft releases SQL Server 2008 R2 CTP A first look at Microsoft SQL Server 2008 R2 Using Microsoft Hyper-V for SQL Server consolidation Migrating to SQL Server 2008 and leveraging new features SQL Server Interoperability Utilize SSAS for data predictions and classification using Excel How to create a SQL Server linked server to DB2 Export SQL Server data to an Excel file using SSIS and Visual Studio Performance tuning for SQL Server 2005 and Exchange running on SBS Custom VB.Net scripting in SQL Server Integration Services Can SQL Server 2000 work on Windows 2003 platform? Query to search text in old DTS packages in SQL Server? Handle slowly changing dimensions with SSIS 2005 wizard Run DTS packages within SQL Server Integration Services SQL Server Integration Services how-to

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary application server  (SearchSQLServer.com) Yukon  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

s, are no longer included with SQL Server 2005, so don't be surprised if you don't see them either.)

Many system functions and stored procedures supported in earlier versions of SQL Server will not work. Microsoft's Books Online for SQL Server 2005 has an index of all backward-compatibility issues, including database engine changes and deprecated commands. Commands that were marked as deprecated prior to SQL Server 2000 should not have been used in SQL Server 2000 anyway.

This is a big concern for front-end applications. Microsoft no longer supports many of the older data-access technologies allowed in earlier versions of SQL Server. DB-Library (not updated since SQL Server 6.5), Embedded SQL (E-SQL), Data Access Objects (DAO) and Remote Data Objects (RDO) are all being abandoned. If you have applications that use any of these, you'll need to use the ADO.NET libraries to make them work natively with SQL Server 2005.

One key tool you can have at your disposal is the Upgrade Advisor, a free program available on both the SQL Server 2005 CD-ROM or from Microsoft's site. The Advisor scans your existing SQL Server 7.0 or 2000 setup and produces a report of possible issues that might arise if you upgrade to SQL Server 2005. It does not analyze standalone applications, just the installation of SQL Server itself. It's helpful for eliminating some of the most pressing issues first. Microsoft also has a moderated SQL Server Setup and Upgrade forum where many common issues (including application compatibility and deprecated functions) are addressed.

Another useful add-on is SQL Server 2005 Backward Compatibility Components package, available through the Feature Pack for SQL Server 2005. This pack includes SQL Server 2005-updated versions of items used in SQL Server 2000: the Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO) and SQL Virtual Device Interface (SQLVDI). Grab this if you have applications that rely on any of these components.

In part two, contributor Serdar Yegulalp will explain how to update stored procedures while preserving backward compatibility in SQL Server 2005.

About the author:Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!