SQL Server 2005 includes architectural overhauls that mean complete backward compatibility with earlier SQL Server releases isn’t possible. Some features in previous SQL Server versions have been discontinued. For example, instead of the undocumented system stored procedures, you must use new catalog views and dynamic management objects. Instead of isql.exe and rebuildm.exe, you need to use sqlcmd and Setup.exe, respectively. Instead of non-ANSI outer-join operators, you must use ANSI outer-join operators. Some virtual tables are gone (e.g., syslocks; you must now use dynamic management views). Jet 4.0 subscribers are no longer supported, so you need to use SQL Server Express Edition instead. And English Query and Metadata Services are gone.
Some features have been deprecated. For example, instead of MAPI-based SQL Mail, Microsoft wants you to use SMTP-based Database Mail; likewise, Microsoft is encouraging you to replace the use of the SETUSER T-SQL statement with the EXECUTE AS statement.
Some features still exist, but exhibit different behavior than you might expect. For example, you’ll see changes in the way system metadata security works. The new built-in permission layer above the metadata access may break your scripts and applications.
One particular compatibility problem you might encounter is that unlike MSDE, SQL Server 2005 Express Edition doesn’t include SQL Server Agent. Therefore, if you have Replication configured, you must change the environment to use the Agent at the publisher rather than at the subscriber. To prepare for your upgrade, be sure to read SQL Server 2005 BOL for complete details on backward compatibility. To identify potential compatibility problems and find workarounds and fixes, you can use the SQL Server 2005 Upgrade Advisor Analysis Wizard. For details about how the tool works, see the sidebar “Leverage the Upgrade Advisor Tool” at InstantDoc ID 47783.
