How to: Export the Application Tables to a Dedicated Database
In Microsoft Dynamics NAV 2013 R2, you can export the tables that define the application to a dedicated database. To export the application tables from an existing database, Microsoft Dynamics NAV provides a Windows PowerShell cmdlet as part of the Microsoft Dynamics NAV administration shell.
Important
Before you can run the script, you must upgrade to Microsoft Dynamics NAV 2013 R2. For more information, see Upgrading to Microsoft Dynamics NAV 2013 R2.
The following procedure illustrates how you can separate the application tables in an existing database into two databases: an application database and a business data database. You can automate this process and combine it with the use of other cmdlets. For more information, see the samples in the Windows PowerShell scripts in the …\WindowsPowerShellScripts\Multitenancy\ folder on the Microsoft Dynamics NAV product media.
To export the application tables to a dedicated database
Stop all Microsoft Dynamics NAV Server services that access the database that you are modifying.
Open the Microsoft Dynamics NAV administration shell.
Important
You must run the program as administrator. Also, you must ensure that scripting is enabled on the computer.
For more information, see Microsoft Dynamics NAV Windows PowerShell Cmdlets.
For an overview of the cmdlet, type the following command:
get-help Export-NAVApplicationTo export the application tables, type the following command:
Export-NAVApplication –DatabaseServer <server name> -DatabaseInstance <instance name> –DatabaseName <name of original database> –DestinationDatabaseName <name of new application database> -ServiceAccount <the account used by Microsoft Dynamics NAV Server if not NT AUTHORITY\NETWORK SERVICE>For example, to run the cmdlet against the CRONUS International Ltd. demonstration database, type the following command:
Export-NAVApplication –DatabaseServer ‘MyServer’ –DatabaseInstance ‘NavDemo’ –DatabaseName ‘Demo Database NAV (7-1)’ –DestinationDatabaseName ‘NAV App’In the example, the database server name is MyServer , and the SQL Server instance is NavDemo. The name of the new application database can be anything. You can specify a name that reflects your application.
The application database is created on the same SQL Server instance as the original database. In the example, if you connect to the NavDemo instance using SQL Server Management Studio you will see two databases: the original database, Demo Database NAV (7-1), and the new application database, NAV App.
At this stage, the original database still contains the application tables, and you can still access it using the Microsoft Dynamics NAV Development Environment. Next, you must remove the application tables from the original database to make it a tenant database.
Tip
Optionally, you can combine the Export-NAVApplication and Remove-NAVApplication cmdlets. For an example of how you can combine the two cmdlets, see the Example section.
To remove the application tables from the original database, type the following command:
Remove-NAVApplication –DatabaseServer <server name> -DatabaseInstance <instance name> –DatabaseName <name of the original database>For example, to run the cmdlet against the CRONUS International Ltd. demonstration database where you have exported the application tables, type the following command:
Remove-NAVApplication –DatabaseServer ‘MyServer’ –DatabaseInstance ‘NavDemo’ –DatabaseName ‘Demo Database NAV (7-1)’You will be asked to confirm that you want to remove the tables.
Warning
Running the Remove-NAVApplication cmdlet is not reversible. When you have removed the application tables from the database, you cannot import them again. Make sure that you have a full backup available.
At the end of the process, you have two databases. In the example earlier in this topic, the databases are as follows.
| Database name | Database type | Description |
|---|---|---|
Demo Database NAV (7-1) |
Business data database |
Contains the data from the original database. |
NAV App |
Application database |
Contains the tables that define the application. For a list of tables, see Separating Application Data from Business Data. |
You must take additional steps to get the final business data database operational. For an example of how you can write a script that runs the cmdlet for creating an application database, see the …\Windows PowerShell\Multitenancy\ folder on the Microsoft Dynamics NAV product media. For an example of how to write individual commands in Windows PowerShell, see the Example section.
Next, you must restart the Microsoft Dynamics NAV Server service, and you must mount the two databases by using the Mount-NAVApplication and Mount-NAVTenant cmdlets. For more information, see Microsoft Dynamics NAV Windows PowerShell Cmdlets.
Example
The following code example illustrates how you can manually write commands in the Microsoft Dynamics NAV administration shell. The commands create an application database based on an existing Microsoft Dynamics NAV database.
The sample commands are assumed to run in the Microsoft Dynamics NAV administration shell based on the CRONUS International Ltd. demonstration database on a local computer.
PS C:\Windows\System32> Set-NAVServerInstance –ServerInstance ‘DynamicsNAV71’ -stop
PS C:\Windows\System32> Export-NAVApplication –DatabaseServer ‘MyServer’ –DatabaseInstance ‘NAVDEMO’ –DatabaseName ‘Demo Database NAV (7-1)’ –DestinationDatabaseName ‘NAV App’| Remove-NAVApplication –DatabaseName ‘Demo Database NAV (7-1)’ -Force
PS C:\Windows\System32> Set-NAVServerConfiguration –ServerInstance ‘DynamicsNAV71’ –element appSettings –KeyName ‘DatabaseName’ –KeyValue ‘’
PS C:\Windows\System32> Set-NAVServerInstance –ServerInstance ‘DynamicsNAV71’ -Start
PS C:\Windows\System32> Mount-NAVApplication –ServerInstance ‘DynamicsNAV71’ –DatabaseServer ‘MyServer\NAVDEMO’ –DatabaseName ‘NAV App’
PS C:\Windows\System32> Mount-NAVTenant –ServerInstance ‘DynamicsNAV71’ -Id tenant1 –DatabaseServer ‘MyServer\NAVDEMO’ -DatabaseName ‘Demo Database NAV (7-1)’ -OverwriteTenantIdInDatabase
In the example, the commands stop the Microsoft Dynamics NAV Server service, creates the application database, clears the default database name in the server configuration, and then restarts the service. Then, the application database and the tenant database are mounted, and the configuration is saved in the Tenants.config file on the server. As a result, you have an application database and a single-tenant deployment. When you try to open the Microsoft Dynamics NAV Windows client, an error displays because you have not specified a tenant. So in the Select Server window, in the Server Address field, add the tenant ID to the address. In this example, the address is localhost:7046/DynamicsNAV71/tenant1.
Tip
For an example of how you can automate the process of transferring user accounts from the original database to the new application database, see the HowTo-ExportNAVApplicationDatabase.ps1 sample script. This and other sample scripts are in the …\Windows PowerShell\Upgrade</STRONG> folder on the Microsoft Dynamics NAV product media. The ExportNAVApplicationDatabase.ps1 sample script can be run in the context of the NAVUpgradeSamples.psm1 script module file. When you call a script such as this, it will export the application tables to a new application database and copy all accounts and SQL Server user roles to the application database. To only transfer the account that the Microsoft Dynamics NAV Server instance uses, use the –ServiceAccount parameter for the Export-NAVApplication cmdlet. In the examples in this topic, this parameter has not been specified. As a result, the default account, NT AUTHORITY\NETWORK SERVICE, is set up with the required user roles.
See Also
Concepts
Separating Application Data from Business Data
Migrating to Multitenancy
Microsoft Dynamics NAV Windows PowerShell Cmdlets
Creating Tenants from Companies
Merging an Application Database with a Tenant Database