Upgrading SQL Server 6.5

Question

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.

Answer 1

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.

Answer 2

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 you database is truly trivial).

Answer 3

@Goyuix: Thanks for the feedback. Other than logins, what would be in danger of being missed if I ran an [Object > Generate SQL Scripts] for the database in question (there's just one).

One roadblock for me in the 6.5 > 7 > 2005 scenario is that I don't have a license for 7, nor the media. I haven't looked into it, but I'm afraid that's going to be a non-trivial problem.

Answer 4

If you can find a professional or some other super-enterprise version of Visual Studio 6.0 - it came with a copy of MSDE (Basically the predecessor to SQL Express). I believe MSDE 2000 is still available as a free download from Microsoft, but I don't know if you can migrate directly from 6.5 to 2000.

I think in concept, you won't likely face any danger. Years of practice however tell me that you will always miss some object, permission, or other database item that won't manifest itself immediately. If you can script out the entire dump, the better as you will be less likely to miss something - and if you do miss something, it can be easily added to the script and fixed. I would avoid any manual steps (other than hitting the enter key once) like the plague.

Answer 5

You can upgrade 6.5 to SQL Server 2000. You may have an easier time getting a hold of SQL Server or the 2000 version of the MSDE. Microsoft has a page on going from 6.5 to 2000. Once you have the database in 2000 format, SQL Server 2005 will have no trouble upgrading it to the 2005 format.

If you don't have SQL Server 2000, you can download the MSDE 2000 version directly from Microsoft.

Answer 6

Hi, I actually wrote part of a book on migrating/upgrading SQL6.5/2000 :) but that was some time ago, The upgrade advisor tool is useful, also there is a upgrade tool from scalabilityexperts.com which is useful for larger migrations.

Redgate scripting tools tend to let you have better control over how the schema / data is scripted to allow easier management.