Microsoft's mainstream support of SQL Server 7.0 will cease on Dec. 31, 2005. This includes all the options and programs customers currently receive, such as no-charge and paid incident support, hourly charge support, warranty claim support and hotfix support. After mainstream support ends, extended support will be offered for business and development software.
Extended support will end on Dec. 31, 2007. This includes all paid support options, as well as no-charge security-related hotfix support. Hotfix support that is not security related requires a separate extended hotfix support contract to be purchased within 90 days after mainstream support ends. Microsoft will not accept requests for warranty support, design changes or new features during the extended support phase.
Since bugs are more likely to be fixed on current versions of SQL Server (as opposed to the deprecated versions) it is good business practice to migrate all applications to current supported versions of SQL Server, if not the most recent version.
Migrating to the next version is frequently hampered by poor design choices that have made the application dependent on older versions of SQL Server. For instance, the application could be reading version-specific registry keys or using undocumented features, reserved keywords or system tables that have changed from one version to another.
The industry trend is to migrate slowly to new versions of SQL Server, typically after the first service pack. The theory is that the product will be vetted by the community and the vendor will have fixed glaring bugs by then.
Many DBAs also feel there needs to be a critical mass of community support (books, newsgroups, Web sites, peers, etc). Once this level of support is established, most DBAs feel a degree of comfort moving to new versions. Management also feels more comfortable knowing there is an available talent pool to draw on to support the product.
The following are best practices to test functionality when running multiple versions of SQL Server.
Multiple versions on the same machine
When running multiple versions of SQL Server it is often necessary to test functionality across versions. For instance, you must test how an application written in SQL Server 7.0 will work on 2000, or how an application running on 2000 will perform on 2005. It is possible to run all three versions on the same machine. SQL Server 7.0 must be installed first and then instances 2000 or 2005 can be installed.
SQL Server 7.0 shipped with Program Group Item, which allows you to run SQL Server 6.5 on the same machine and toggle back and forth between versions. However, both versions can not run simultaneously nor can they share databases. You can use this switch on 7.0 to run 6.5 when named instances of 2000 and 2005 exist.
Database-compatibility levels
An alternative is to set the database-compatibility level by using the stored procedure sp_dbcmptlevel. This way you can test how an application written for 7.0 will run on 2000 by setting the application database on your 2000 server to 7.0 by using the following command (where pubs is your application database):
sp_dbcmptlevel 'pubs',70
Similarly, you can issue this command on a 2005 database, but keep in mind your application will have to use the SQL Native Client provider to access your 2005 server, so your connection string will have to be different as well.
Backup and restore
You may test functionality by backing up a database and restoring it to a SQL Server running a more recent version. Performance characteristics vary between versions and are typically better as you move toward a more recent version. In some cases you will have to rewrite stored procedures to improve performance or take advantage of new version features. The problem with the backup and restore method is that it only works moving up the version chain; you can't take a 2000 database backup and restore it on a 7.0 server. Some incompatibilities exist between databases of the same version at different service pack levels.
Documentation
It is advisable to keep Books Online installed for all versions of SQL Server you support installed on your desktop. When working on a problem you may need to refer to the version-specific documentation. Having it on hand, especially during high-pressure situations is a blessing.
Generally speaking, you should be using the most current versions of SQL Server to avoid functionality issues. Programmers should also be forward thinking and avoid the use of reserved keywords, system tables or any feature that may not be present in subsequent versions of the product.
While you can run multiple instances of SQL Server on the same box and thereby run SQL 6.5 to SQL 2005 on the same machine, a better approach is to use the sp_dbcmptlevel procedure to set your database to be compatible with the version of SQL Server you wish to test. You can restore database from a lower version to a higher version to test compatibility. However, you cannot restore a backup from a higher version of SQL Server to a lower version. It is also wise to keep all versions of the documentation installed on your desktop for quick reference.
About the author: Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.
More information from SearchSQLServer.com
Article: Are single-platform databases in your future?
Learning Guide: SQL Server 2005
Topic: Get up to speed on integration and interoperability best practices
Shelf life of SQL Server 7.0 support
