Windows PowerShell and Dynamics NAV for your coffee break – Deploy Dynamics NAV via Windows PowerShell


In this coffee break we will show a way to deploy Microsoft Dynamics NAV automatically. We assume that Microsoft Dynamics NAV is already installed on the machine, but we want to add additional installations / instances of Microsoft Dynamics NAV.

Coffee Break 2: Automate a Microsoft Dynamics NAV deployment

User Story:
The customer has a system where they spin up a new Microsoft Dynamics NAV system every time they
  – Start a new branch
  – Start a new series of testing
  – Start a new Fiscal Year
  – etc
In practical terms, a “new Microsoft Dynamics NAV system” could mean new Microsoft Dynamics NAV Server instance and database, a new company, or a new tenant. Typically you would end by starting a new Microsoft Dynamics NAV Server service or spinning up a new tenant. For simplicity in this example we just re-use an existing Microsoft Dynamics NAV Server service to start up on our new database.

Pre requisites:
We use a SQL Server PowerShell cmdlet: import-module sqlps. SQLPS module is provided with SQL Server installation, but you don’t have to install SQL Server to obtain the module.

If you don’t have SQL Server installed on the box you’re running PS on, you can install Microsoft SQL Server 2012Feature Pack:

http://www.microsoft.com/en-us/download/details.aspx?id=29065

#Preparation
$MyNAVServerName = “DynamicsNAV80”
$MySQLServerName = “.”
$MyNewCustomerName = “NewCustomer”
$MyNewDatabaseName = “NewCustomerDatabase”

Set-ExecutionPolicy unrestricted
import-module “C:\Program Files\Microsoft Dynamics NAV\80\Service\NavAdminTool.ps1”
Push-Location #jump back to standard prompt with pop-location
import-module sqlps #ignore any warnings you may get

#Restore SQL db (NAV demo db)
#Relocate database files http://stackoverflow.com/questions/26400752/cannot-bind-relocatefile-when-using-restore-sqldatabase-cmdlet
$mdf = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(“Demo Database NAV (8-0)_Data”, “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DB_Data_$MyNewCustomerName.mdf”)
$ldf = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(“Demo Database NAV (8-0)_Log”, “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DB_Log_$MyNewCustomerName.ldf”)

restore-SqlDatabase -ServerInstance $MySQLServerName `
                    -Database $MyNewDatabaseName `
                    -BackupFile “C:\NAVDVD\SQLDemoDatabase\CommonAppData\Microsoft\Microsoft Dynamics NAV\80\Database\Demo Database NAV (8-0).bak” `
                    -ReplaceDatabase `
                    -RelocateFile @($mdf,$ldf)
                   

#Set network service as dbo
$CreateServiceAccountUser = “CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE]”
Invoke-Sqlcmd -ServerInstance $MySQLServerName -Database $MyNewDatabaseName -Query $CreateServiceAccountUser
$AddServiceAccountAsDbo = “exec sp_addrolemember ‘db_owner’, ‘NT AUTHORITY\NETWORK SERVICE'”
Invoke-Sqlcmd -ServerInstance $MySQLServerName -Database $MyNewDatabaseName -Query $AddServiceAccountAsDbo

pop-location # Finished with SQL commands so popping back to normal PS prompt

#For NAV 2013(R2), to convert the database to latest executable version.
#For NAV 2015 we don’t need this (in fact we don’t have the Invoke-DatabaseConversion cmdlet).
import-module “C:\PSscripts\Upgrade\Cmdlets\NAVUpgradeCmdlets.psm1”
Invoke-NAVDatabaseConversion -DatabaseServer localhost -DatabaseName “Demo Database NAV (7-1)” -FinSqlExeFile “C:\Program Files\Microsoft Dynamics NAV\71\Service\finsql.exe”

#At this point the next steps depend on whether we want a new NAV Service, connect to an existing one, etc. Here we just reuse existing NAV Service
#Configure then restart the service and get status
Set-NAVServerConfiguration $MyNAVServerName -KeyName DatabaseName -KeyValue $MyNewDatabaseName
Set-NAVServerInstance $MyNAVServerName -restart
Get-NAVServerInstance $MyNAVServerName

 

 

Jasminka Thunes, Escalation Engineer Dynamics NAV EMEA

Lars Lohndorf-Larsen, Escalation Engineer Dynamics NAV EMEA

Comments (5)

  1. Marco Engel says:

    Nice coffee break and again very useful.

    Perhaps it is a good idea to import the sqlmodule like this:

    Import-Module "sqlps" -DisableNameChecking

    It will not show any warnings, more info: msdn.microsoft.com/…/hh231286.aspx

  2. nutcracker says:

    very helpful.

    thank you very much.

  3. Kasper Dan Jensen says:

    I use a similar script to spin up a new instance with new SQL db and adding users in 1 minute thx to powershell 😉

    Do you have any tips on the first initial installation of the NST on-prem using powershell?

  4. Hi Kasper, thanks for reading here! For initial installation, just run the provisioning (cloud) scripts, but take the cloud out of them if you see what I mean? Anything they do will also work on-prem. We will try to do some examples on this in future blog posts.

  5. Michael Rosenørn says:

    I might not drink coffee, but I do enjoy these coffee breaks – good job.

    Question – why do you use the restore-SqlDatabase command instead of the New-NAVDatabase command?