Migrate SQL 2000 –> SQL 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Migrate SQL 2000 –> SQL 2005

We have our application running via MS SQL 2000 currently. We have several databases that I would like to move onto a new server. It seems logical to install SQL 2005 but I am concerned about the migration. The previous install is on a very old machine (Dual PIII450 with 512 Meg Ram) and it supports a very busy photo sharing website with several hundred thousand members. The databases in question are 2 that are about 10 gig and 4 or 5 that are a few hundred meg. They are accessed by an application running Cold Fusion 5 via ODBC. In the past when we migrated to a new database we would sp_attach_db and detach (Vice versa). I read that this can be done just as simply from SQL 2000 to SQL 2005. My issues are the following. 1. Must I the server names match and if they do not match what issues will I have besides needing to change all my pointers. 2. My current SQL Server uses only SQL Authentication. Don’t ask why please. We must implement Windows Auth in the new server if for no other reason that to be able to run backups directly to a UNC path or mapped drive. Can I do this before the migration or should I wait until after. 3. Must I create all the same users on both databases and if I do not how do I sync the users so it does not create a problem. I am a bit desperate since the company is struggling and I have very little SQL abilities. I just squeeze by because the software works so well and I am luck enough to get good advice from folks like you (Yea I am pouring it on here I know). Any advice would be great appreciated. OH yea #4 & 5 & 6 4. Is there any danger in running the SQL 2005 advisor against my current SQL 2000 server. As I said it is not in the best shape and we are quite concerned it is going to die. 5. Is there a required version of SQL 2000 I must be up to. I believe it is SP3a currently. 6. We recently changed the database to simple mode because of a disk space problem on the old server. The new server has plenty of free space. I feel like I should use full logging just in case we even need it. What is the thought here on that? Thanks so much for any advice. Doug
Above all your question, I suggest you to run thru Upgrade Advisor in this case, that gives more information about what you’re worrying. Also suggest to run thru the upgrade process on a test environment to get you more acquaintance with the process. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing.