access 7.6 to SQL 8.05

Just finished going from access 7.6 to SQL 2005 8.05. 9400 members with 37K+ posts. It worked.

I had no prior SQL experience at all, as in I didn't know how to log in to the server. I spent this past week reading this Forum and doing a bit of research. I opened a new Verio SQL account using just the IP address as opposed to repointing the domain to the new server in case the Forum move didn't work. I have another ASP/database portion of the Site I'm not ready to move yet so I'm keeping both servers for now.

Most of the migration/upgrade info I needed was posted here so I was able to develop a plan of upgrading  the 7.6 access database to the Access 8 version on the new SQL server then FTPing it back to my hard drive. I FTP'd the v8 Forum to the new server and followed the instructions for creating the tables on the new SQL database (it takes about 10 seconds). At this point you should be able to access the new forum.

There are probably cheaper solutions but I decided to buy SQL Manager 2005 for SQL Server along with the Data Import component. I also DL'd the free MS Studio Express. I'm glad I had both.

Verio sets you up with a SQL 2000 account and gives you the option of upgrading to 2005. They have a Control Panel that lets you do this youself as well as revert back to 2000. I spent a day just screwing around connecting the Forum back and forth between 2000 and 2005. Sometimes one would work for a while, then return nothing but errors. SQL Manager was a bit flaky as well, sometimes displaying correct DB files and other times not. Studio Express was always reliable displaying the proper DB structure but it doesn't have the Data Import capability. I have no idea how you would transfer the data from the Access DB to the SQL DB without something like SQL Manager Data Import. I understand you can do it with an SQL Query but I wouldn't have any idea where to begin.

SQL Manager DI opens the Access table on your hard drive and the corresponding table on the SQL server and you just link up the fields with the same name in a dialog box. You click through a few more screens and it imports all your Access data into the SQL database. I practiced with a few sample records at a time to get a feel for it and learn how it works. I also found that I needed to go into the WWF control panel and update or re-sync for everything to show up. I believe the order of table import I used was Category, Forum, Topic then Thread - fewest records to most. I did the Author table with 9400 records last and it worked.

SQL Manager with DI isn't cheap and I'm not impressed with its erratic behavior but it worked. DTS may be an option for SQL 2000, but since I planned on using SQL 2005 it seemed like it may not work.

After spending about a day screwing around with the software and the DB files, the actual time needed to upload, convert and dl the Access DB - FTP the Forum to the new server and then migrate about 60,000 records from Access to SQL was probably about 6 hours.

Each computer, server and software package seems to develop its own anomaly at the worst possible time so I didn't think a step-by-step would be much help. However, since the process I used worked, I thought I'd just toss it into this arena from the perspective of someone with no prior SQL experience at all. Maybe someone reading has a less expensive and more stable and reliable solution then SQL Manager with DI.

WWF is a great Forum program and our 9400+ members seem to agree.

Hope your migration goes well.