Migration from one SQL Server to a new...

Brannmark

I need to migrate a client over from thier old SQL Server 2005 to a completly new server also with SQL server 2005.

Tried to use SQL server for the migration but got locked out of the database. I used the backup tool in SQL server and did a restore of the backup in the new sql server. When I tried to login into Navision the user didnt work on certain object even though I hade SUPER permission. Also verifed that the users in the new sql server hade the same permissions as in the old one. The odd thing is that some items worked, but others didnt..

Seams like Navision is doing something in the master databases that doesnt get into the backup.

So what is the best steps to migrate a client over to a new sql server?

I thought I would try this way:

1. Created a new database thru the Navision client on the new sql server.
2. Make a backup thru navision of the database from the old sql server.
3. Restore the backup in the new database / sql server.

Anyone done this before?

kriki

Did you do a synchronization of the Navision-logins on the new server?

Brannmark

Yes, didnt fix the problems.

Going to try the way I describe now. Anyone got any better suggetions than my planned method?

kriki

And completely (SQL+Navision) deleting the users and recreating them (+ synchronizing)?

kine

You can just detach/attach the database, but you need to migrate the users. You can use SISS for that. There is special component for migrating users between servers...

thaug

Here is the KB article that give you instructions on moving users. I've used it to migrate users from 2000 to 2005 flawlessly.

http://support.microsoft.com/kb/246133

Waldo

If you're looking for references:
We've used this as well (recently), and worked like a charm.