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
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:
- Back up the database in question and Master on
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.
- 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
.sqlfile against the
2005database. Several dozen sprocs had issues making them incompatible with
quoted identifier issues.
- Corrected all of those issues and re-executed the
- Recreated the
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.
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).