Get the best from both world: use the rich features of the native Navision software and rely on the performance and reliability of Azure services.
To do that, I’m assuming you already have provisioned a Virtual Machine setup in Azure, where you installed the Navision software. For this blog we’ll also assume all components from Nav 2017 are installed (also works for Nav 2016).
The demo instance that has been created by the installer is called DynamicsNAV100, and can be connected to through:
- localhost:7046/DynamicsNAV100 when using the RoleTailored Client
- http://localhost:8080/DynamicsNAV100/WebClient/ in a browser to access the Web Client
Make sure this work before attempting to set this up, or reach out on Twitter @MattiasDeSmet if you’re stuck.
The summary of steps involved:
- Migrate a Navision database to SQL Azure
- Connect your Navision instance to the database
There are probably plenty of options to do this, I’ll illustrate two. I’ll expand on both methods in a separate blogpost, but condense the essence for now. Both methods assume you already have a Navision database in SQLServer, e.g. the Demo Database (10-0) running on the local SQLExpress.
First of all: License!
Before attempting either method, make sure to store the license in the database first, or you’ll be treated to this lovely error message:
The following SQL error was unexpected: Reference to database and/or server name in 'master.dbo.$ndo$srvproperty' is not supported in this version of SQL Server.
After hours of waiting migrating the database, you’ll only make this mistake once.
Start Nav 2017 Development Environment and open the database you will export.
Go to menu “Tools”, “License information” and press “Upload…”
Select the license file provided (or sent to you by your partner).
Go to the menu “File”, “Database”, “Alter”
Close the database, it’s ready to be migrated.
When you have an existing SQLServer database, you can create a BACPAC file in SQL Management Studio by
- right-clicking on the database
- choose tasks
- choose “Export data-tier application…”
- you can choose to store locally, or already move it in azure, which improves the Import performance later on.
Connect to your SQL Azure database server using SQL Management Studio (v17 or higher) with the server admin user.
- Right-click “Databases”
- Choose “Import data-tier application…”
- Select the BACPAC file created above
- Specify the name of the database and the Size
Data Migration Assistant
A more direct approach is to use the Microsoft Data Migration Assistant. A similar process could be done using an archived Codeplex tool named SQLAzure Migration Wizard.
First create a new empty SQL Azure database, e.g. through the portal.
Make sure to create the user you’ll use for the migration process in the new database and assign db_owner access to him, by executing
CREATE USER navision FOR LOGIN navision GO EXEC sp_addrolemember N'db_owner', N'navision' GO
Start the tool and click on + in the left menu to start a new migration project. Specify the configuration like below.
In the first step, select the source database.
In the second step, select the newly create target SQL Azure data base, and click Next.
This brings you to the third step, where the tool will Asses the source database schema, which can take some time. Next the wizard lets you select what to migrate, leave all selected and click “Generate Script”, moving to step 4 (again, after a long while).
In the fourth step, click “Deploy script”, and the target database schema will be created.
Click “Migrate data” to start the discovery of tables to migrate in step 5.
Keep all tables selected and press “Start migration”, step 6 and final step.
This is the easier part.
In Dynamics NAV 2017 Administration select the instance you want to configure (DynamicsNAV100 in our case), expand the “Database” section and click the Edit button to make changes.
The screenshot doesn’t show you much useful information, the fields in the administration console are limited in length for some reason.
Leave the Database Instance field empty, enter the Database Name (Demo Database NAV (10-0) in my case) and enter the Database Server as the Azure server, e.g. axis0.database.windows.net
Press “Save”, which warns you to restart the instance.
At this point we still have to configure the Database credentials, by pressing on the right-hand side on “Database Credentials”
Here, we first click “Edit”, than select “SQL Server Authentication” from the dropdown, and provide the login for the SQL Azure database, eg. navision + password. note: take care when entering the password, in some RDP configurations, some characters are not ingested by the snap-in (e.g. numbers) the first time.
Press “Save” again, and you’ll be presented with this information, which is fine.
The encryption key is by default stored in a file on the server. Typically this is in C:\ProgramData\Microsoft\Microsoft Dynamics NAV\100\Server\Keys . If Navision detects a file with “the name of your database”.key (e.g. “Demo Database NAV (10-0).key”), it will assume this is the one used in your database.
When you want to point your NAV instance to a new database, make sure to remove the .key file. This will force Navision to regenerate the key when saving the database credentials you have input in the configuration. I’ll expand on this in a future blog.
Also the next message is a good sign, ie. Navision could make a connection to the database and just wants to give some more friendly (and secure) advice, which you can follow.
Restart your NAV instance, and you should be up and running with a SQL Azure backed NAV instance.
Make sure to take advantage of functions like “Geo-redundant replication” in Azure, to keep an realtime updated copy of your database available in another Azure region.
You’ll also have the possibility to quickly copy a database to another environment (e.g. for training, development or User Acceptance Testing). I’ll go into more detail on this in a later post.