SQL 6.5 Migration Failure

Posted on by

I’m trying to migrate an NT4 SQL 6.5 server to Windows 2000 and SQL 2000 using the SQL Server Upgrade wizard. We’ve got lots of data on the old server and some tables structures that I’d really rather not have to recreate by hand. Below is the horror story that was the attempted upgrade.

At 6:10 am, I know that the direct method (sql6.5->sql2000 via wizard) wasn’t going to work. (with 20/20 hindsight, this is where I should have formated the target box and started over).

So, install a temp 3rd server to migrate the data to, then move backed up databases over to new server. Sure, that’ll work. It does, but the logins are busted on the new server. Use sp_change_users_login, works like a charm.

Roll over to the new server (shut down source via radmin, rename new server to the correct name). All seems to be well.

Try to add a Maintenance plan, get a cryptic error about incorrect table format. Aw hell. Look at msdb and master. There are lots of tables name ‘MIG_blahblah’, ‘TMP_*’ and ‘RS65xxx’, that can’t be good. Looks like the original attempt at migration failed and left shit broken. So, I try to reinstall sql server and restore the dbs, doesn’t work. The logins get hosed and are unfixable because I don’t have a source for the original permissions the logins had (the only ‘correct’ server is turned off and across campus).

Well, I’m screwed, time to roll back to the NT4/SQL6.5 box. Get to Peabody, and my key fits in the lab door, but it won’t open. It’s been changed for IVCF. Stan was in his office.

Well, it was a learning experience.

I now know how NOT to do it, and a best bet for how TO do it.

Lessons learned:

  1. Don’t install sp3 until after you’ve migrated the data. SQL2k SP3 broke the data migration wizard. There’s a Knowledgebase article about it. Cross your fingers that no slammer packets get to the server during this time.
  2. Don’t involve a 3rd server in the process. It seems like a good idea at 7 am, but will just lead to errors in the end.
  3. If the data migration errors out in the middle, FORMAT THE TARGET SERVER. Most of the tables in msdb and master are royally hosed at this point. They act normal, they ALMOST seem normal, but they are a nasty mix of the 6.5, 7.0 and 8.0 formats. Doesn’t really pop up until you go to either: Install a service pack or add a scheduled job. Either one bombs miserably.
  4. Once you’ve shown a DSN that the server is capable of TCP/IP instead of named pipes, it forgets all the named pipes properties. Took me awhile to track THAT down. (ie webserver access to data on squiggy was broke)
  5. There are probably better ways to do this, but I think Ive got one that will succeed next time. New years day maybe?