How to move Dynamics NAV from one SQL to another SQL.

thomast

Hi Everyone,

How do you move a Dynamics NAV installation from one SQL server to another on a completely different server and domain?

A reference to a description of this process would be appreciated.

Thanks.

Ramonchu

Hello Thomast.
I use to do that to move a customer installation to a development enviroment and test things. I don't know if this is the best way, but It works:
What I do:

- With Dynamics Nav administration stop the instance
- Go to SQL server and backup the database (over database, clic on right mouse button and you will see an option to backup). In options I changed the backup to SIMPLE mode to avoid logs and a big big backup file.
- In other SQL server, restore the database.
- Clear all the permissions and user configuration (you will have to recreate after):
For this execute these SQL commands:

USE [your restored database name];
TRUNCATE TABLE "User Personalization";
TRUNCATE TABLE "User Property";
TRUNCATE TABLE "Access Control";
TRUNCATE TABLE "User";
GO

I don't know if this way is good for you, but I use it to move a database to my virtualbox developent enviroment and with it I avoid all problems regarding to users, permissions, domains...

I hope this helps you.

Ramonchu

Hello Thomast.
I use to do that to move a customer installation to a development enviroment and test things. I don't know if this is the best way, but It works:
What I do:

- With Dynamics Nav administration stop the instance
- Go to SQL server and backup the database (over database, clic on right mouse button and you will see an option to backup). In options I changed the backup to SIMPLE mode to avoid logs and a big big backup file.
- In other SQL server, restore the database.
- Clear all the permissions and user configuration (you will have to recreate after):
For this execute these SQL commands:

USE [your restored database name];
TRUNCATE TABLE "User Personalization";
TRUNCATE TABLE "User Property";
TRUNCATE TABLE "Access Control";
TRUNCATE TABLE "User";
GO

I don't know if this way is good for you, but I use it to move a database to my virtualbox developent enviroment and with it I avoid all problems regarding to users, permissions, domains...

I hope this helps you.

EvR

But that would clear all the users.
There is no official migration script but if you know the new usernames and SID's you could write a simple migration codeunit or xmlport that migrates the current users. If that's useful depends of course on the number of users.

Poltergeist

ActiveDirectory has the possibility to store the SID from the other domain into the new domain. I believe it's called SID history.

postsaurav

Hi,

I think if both the servers are in same domain you can simply do it -

1. Disconnect all session to the database. (Client and Navision Service Tier).
2. Take Backup of Database. (in case of issues).
3. Detach the database from server.
4. Move all three files from old server to new server (mdf, ndf & ldf)
5. If possible place files in same folder structure as of old server.
6. If not possible place file as required.
7. Login to new SQL Server, select attach and select the mdf file. If the path is same then system automatically picks the ndf and ldf file.
8. if path is different select ndf and ldf manually in the attach window.
9. Database will be moved successfully in new server.
10. Create service account (account used to Navision Services) with appropriate permissions.
11. Allow SQL Port in firewall (to access the sql from service tier).

Let me know if any doubts.

thomast

thank you so much guys!!! All very helpfull - looks like I've managed it now.

turnip595

Hi thomast, Can you share how you managed it? Did you use ADMT for your migration?