Upgrading Sql Server 2000 to 2008

At my place of employment we have been thinking about upgrading from SQL Server 2000 to the latest (2008). We know that the DTS packages will have to be dealt with since the newer server software no longer supports them. Has anyone done this transition before and how easy is it? Are there any snags you ran into that we should be aware of?

Solution 1:

Make sure you run upgrade advisor. There were a lot of changes between 2000 and 2005 and stuff that worked on 2000 doesn't work on 2005. Also make sure you set up a test server and run full regression tests on your apps. 2005/2008's T-SQL parser is stricter and stuff that ran in 2000 will fail in 2005 or 2008

Solution 2:

DTS packages were sort of weird after SQL 2000, but you can still fun them. You just have to install the add ons. There is a great article here.

Solution 3:

This is a pretty good article to point you in the right direction.

http://msdn.microsoft.com/en-us/library/ms143716.aspx

Also try and catch one of Brian Knights Webinars, the pragmaticworks site is being redesigned but keep an eye out.

Also this wiki is useful for links

Solution 4:

SQL Server 2005 and SQL Server 2008 support 2000 DTS packages as legacy without having to migrate/convert them. dtsrun.exe is installed by default in 2005, & can be installed as a separated component in 2008