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
