sql-server - tools - sql server migration assistant tutorial

Upgrading SQL Server 6.5 (2)

Yes, I know. The existence of a running copy of SQL Server 6.5 in 2008 is absurd.

That stipulated, what is the best way to migrate from 6.5 to 2005? Is there any direct path? Most of the documentation I've found deals with upgrading 6.5 to 7.

Should I forget about the native SQL Server upgrade utilities, script out all of the objects and data, and try to recreate from scratch?

I was going to attempt the upgrade this weekend, but server issues pushed it back till next. So, any ideas would be welcomed during the course of the week.

Update. This is how I ended up doing it:

  • Back up the database in question and Master on 6.5.
  • Execute SQL Server 2000's instcat.sql against 6.5's Master. This allows SQL Server 2000's OLEDB provider to connect to 6.5.
  • Use SQL Server 2000's standalone "Import and Export Data" to create a DTS package, using OLEDB to connect to 6.5. This successfully copied all 6.5's tables to a new 2005 database (also using OLEDB).
  • Use 6.5's Enterprise Manager to script out all of the database's indexes and triggers to a .sql file.
  • Execute that .sql file against the new copy of the database, in 2005's Management Studio.
  • Use 6.5's Enterprise Manager to script out all of the stored procedures.
  • Execute that .sql file against the 2005 database. Several dozen sprocs had issues making them incompatible with 2005. Mainly non-ANSI joins and quoted identifier issues.
  • Corrected all of those issues and re-executed the .sql file.
  • Recreated the 6.5's logins in 2005 and gave them appropriate permissions.

There was a bit of rinse/repeat when correcting the stored procedures (there were hundreds of them to correct), but the upgrade went great otherwise.

Being able to use Management Studio instead of Query Analyzer and Enterprise Manager 6.5 is such an amazing difference. A few report queries that took 20-30 seconds on the 6.5 database are now running in 1-2 seconds, without any modification, new indexes, or anything. I didn't expect that kind of immediate improvement.

Hey, I'm still stuck in that camp too. The third party application we have to support is FINALLY going to 2K5, so we're almost out of the wood. But I feel your pain 8^D

That said, from everything I heard from our DBA, the key is to convert the database to 8.0 format first, and then go to 2005. I believe they used the built in migration/upgrade tools for this. There are some big steps between 6.5 and 8.0 that are better solved there than going from 6.5 to 2005 directly.

Your BIGGEST pain, if you didn't know already, is that DTS is gone in favor of SSIS. There is a shell type module that will run your existing DTS packages, but you're going to want to manually recreate them all in SSIS. Ease of this will depend on the complexity of the packages themselves, but I've done a few at work so far and they've been pretty smooth.

I am by no means authoritative, but I believe the only supported path is from 6.5 to 7. Certainly that would be the most sane route, then I believe you can migrate from 7 directly to 2005 pretty painlessly.

As for scripting out all the objects - I would advise against it as you will inevitably miss something (unless your database is truly trivial).