Upgrading SQL Server 2000 to 2005—Say Goodbye to Access 2003 ADP Files!

Flippantly, it appears to be this:

• Detach all SQL Server 2000 databases to be upgraded.
• Consider turning off SQL Server 2000 services (e.g. NET STOP MSSQLSERVER).
• Turn on SQL Server 2005 services (e.g. NET START MSSQL$SQLEXPRESS).
• Attach the databases that were once detached to SQL Server 2000. See CREATE DATABASE statement below).
• Deal with security issues (e.g. creating/copying SQL logins).Much of this flippant behavior is based on “Upgrading MSDE 2000 to SQL Server 2005 Express” so clearly this view does not take into account a myriad of large-scale enterprise situations. This view does not reassure me that using ‘old’ SQL 2000 data files has insignificant drawbacks compared to generating new data and log files in SQL 2005. When the attach takes place an upgrade/conversion does take place and file size increases significantly. It would be interesting to see how file size increases from building from scratch and a conversion. This is a sample attach statement:USE master; GO

CREATE DATABASE TestDb

ON (FILENAME = 'C:\Test.mdf'), (FILENAME = 'C:\Test.ldf') FOR ATTACH; GO What’s more is that SQL 2005 does not play well with Access *.ADP files. Access Data Projects are not forward-compatible with SQL Server 2005. The current guidance from Microsoft is, “Delete the data connection to the newer SQL Server, and connect only to SQL Server 2000 or earlier versions.”

rasx()