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
2005? Is there any direct path? Most of the documentation I've found deals with upgrading
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:
SQL Server 2000's
6.5's Master. This allows
SQL Server 2000's OLEDB provider to connect to
SQL Server 2000's standalone
"Import and Export Data"to create a DTS package, using
OLEDBto connect to 6.5. This successfully copied all
6.5's tables to a new
2005database (also using
6.5's Enterprise Manager to script out all of the database's indexes and triggers to a .sql file.
.sqlfile against the
2005database. Several dozen sprocs had issues making them incompatible with
quoted identifier issues.
6.5's logins in
2005and 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.
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.
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.
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).
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.