Hey y'all, I'm looking to migrate several databases on SQL 7 and SQL 2000 to a new SQL 2005 box. Has anyone done such a migration? What would you recommend as a good methodology for it? Also, should I expect stored procedures, triggers, indexes, other complementary stuff to break? I'd love to hear about your migration experiences.
The only sql code that might break migrating from 2000 to 2005 would be some references to system objects.
Also, you will need to migrate any DTS packages to SSIS, as DTS is no longer supported. I have heard that the wizard for upgrading DTS to SSIS is a bit klunky.
If it's not practically useful, then it's practically useless.
You can install the "DTS Designer for SQL 2000" as an add on for SQL 2005, and it is fully backward compatible with DTS Packages, you just cannot create new packages, but you can edit, correct, execute existing packages with the tool, from what I've encountered so far, it works. I have some pretty complex DTS packages with ActiveX scripts and calls to O/S that work with no problems.
I've restored 2000 DBs to 2005 with no problem, just get the new sp_help_revlogin sproc for SQL 2005 (if you extract logins). I ran a SQL 2000 generated file against 2005 with no problem to create users and existing passwords from a 2000 box.
system tables/views, some have change it sysobjects sys.objects, etc.
if you use xp_cmdshell, you need to enable it via "SQL Server Surface Configuration" program (don't understand why they put it here, but it's here).
Although old style joins are compatible, I would update all SQL Code to use Ansi style join syntax, instead of in the where clause. A lot of nice, what I consider "Oracle-like", upgrades with index rebuilding, stats update, etc.