All set for new SQL 2000 server. Next Migration...help please!

[rasindia]

I wave installed and configured well a new SQL 2000 Server. I am planning to migrate an SQL 6.5 server database to this new server. Both servers can see each other. Please help me for the following....

1]Is there any special steps to be done before trying the upgrade wizard on the new server?
2]What will happen to the old SQL 6.5 server and it's database once the migration is done to the new server? Can I use the old server again without any modifications in case if the new server with migrated/upgraded database had a problem?
3]What should I do at the end of a successful migration/upgrade? Shut down the old 6.5 server?
4]All the data is on a third server where the SQL 6.5 points.After the migration/upgrade, will the new database look to the same location for data? Do I need to do any modifications?
5]What are the steps to confirm a successfull migration?
6]Do I need to change anything on the SQL 6.5 server before trying the migration/upgrade?

This is my first SQL migration project.
Please Help!

 

[rasindia]

Please Help!

 

[calidrake]

1. First thing you should always do is make a backup of your information and, if possible, restore your database without overwriting the original so that you are sure you have a good backup.

2. I have never upgraded from 6.5 to 2000, but the upgrade wizard has an export server, and import server. This setup leads me to believe that it is just moving the information, and the original store will be left intact. But…I have never done it, so I am not sure on this point. Look through the SQL Server 2000 Books-online.

3. After it is upgraded you should make sure that your applications can actually connect, and interact with the new server. Have several people do this before you move your whole customer base over. Just be sure to remember, this is data from when you upgraded…ANY changes made to the 6.5 database afterwards will not update the 2000 server. However when EVERYONE is happy and working, then you can shutdown the 6.5 server. However don't totally trash it just in case a problem arises that you did not see before.

4. The data on the 2000 server will not point to the old data. It will have totally new data files, and transaction log files. Once again, I have never done this upgrade. You should have to set up the data directory when you install SQL Server 2000. If it is just a file server that the database keeps it’s files on, then you can set it up to save the SQL Server 2000 data files to that server also.

5. A successful migration is a SQL Server 2000 server that is fully functioning. If your applications don’t work with the database then it is not successful. Make sure the data is intact, and double check any stored procedures, and views that you need have been transferred. Security objects will have to be double checked also.

6. You shouldn’t have to change anything. But if you don't want your users changing info then you have to set it read-only. But check with Books-Online.

I hope this helped out.

 

[rasindia]

Hi,
thanks a lot for your comments...


The migration worked without any errors. It was as simple as getting both servers online and running the migration wizard.I had to use a common login account for the SQL server service to make the migration work.

Anyway I had to discard the new server due to some other problems. Almost half of the users on our database migrated as Domain users and faced logon issues. According to our database and its software we need all users to stay as SQL users.

Is there any way that I can change a domain user to SQL user on SQL 2000? If there is, I can try the migration again and switch everything to the new server.

Please help....

 

[calidrake]

What do you mean "our database and its software say we need all users to stay as SQL users"? If the application only supports SQL server security then that is what you have to use. As for making NT users into SQL users, you cannot change them in the transfer because they are two totally seperate things.

 

[rasindia]

What I am asking is that if there is a way to change the domain users to SQL users? After migration, I was ended with the problem of half of the users showed up as domain users. It can be due to the problems in the original database.

What I need to know is the procedure to change the users to sql database.These users show the domain or sql login properties in 'dim' color and I can't even change password.Is there any registry key which I can change?

 

[calidrake]

No...you cannot change NT users into SQL users.

 

[calidrake]

The best you can do is to delete the NT users, and make sql users with the same names.