NAV Service Startup Type in PowerShell scripting

I have spent some time creating PowerShell scripts to solve all my installation and upgrade tasks.  I am now able to install Dynamics NAV on both local and remote computers.  I can install databases, services, clickonce distribution and web clients.  And my final task last week was to be able to install knowledge base package in a few minutes that upgrades services, client, webclient and clickonce.  Likely to create a blog about that later.

As a part of this process I have created several PowerShell scripts and functions.  I will find a time to share this with you over the next weeks.

For now I would like to share one function.

When you install a NAV Service Instance from the DVD the service startup is set to “Automatic (Delayed Start)”.

NAVServiceStartupType

There is a reason for this.  I have seen servers unable to finish the startup process if the service startup type is set to “Automatic” only.  There is a period of time needed before the service can start after the computer startup.

The problem I am facing with my scripts is that the command New-NAVServerInstance will leave the service in “Automatic” startup mode.  I wanted to change this so I created a function.

[code lang=”powershell”]# Set Service Startup Mode to Automatic (delayed)
function Set-ServiceStartupModeRemotely
{
[CmdletBinding()]
param (
[parameter(Mandatory=$true)]
[string] $ServiceInstance,

[parameter(Mandatory=$true)]
[System.Management.Automation.Runspaces.PSSession] $Session
)
PROCESS {

Invoke-Command -Session $Session -ScriptBlock {
param([string] $ServiceInstance, [string] $verbosePreference)
$VerbosePreference = $verbosePreference
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
$Service = ‘MicrosoftDynamicsNavServer$’ + $ServiceInstance
$Computer = ‘LOCALHOST’
$command = ‘sc.exe \\$Computer config "$Service" start= delayed-auto’
$Output = Invoke-Expression -Command $Command -ErrorAction Stop
if($LASTEXITCODE -ne 0){
Write-Host "$Computer : Failed to set $Service to delayed start.
More details: $Output" -foregroundcolor red
} else {
Write-Host "$Computer : Successfully changed $Service service
to delayed start" -foregroundcolor green
}

} -ArgumentList
$ServiceInstance,

$VerbosePreference

return $ManifestList
}
}
Export-ModuleMember -Function Set-ServiceStartupModeRemotely[/code]

I added this function to my New-Instance script and call the function after the service has started.

WARNING: Waiting for service ‘Microsoft Dynamics NAV Server [NAV71_KAPPI] (MicrosoftDynamicsNavServer$NAV71_KAPPI)’ to start…

LOCALHOST : Successfully changed MicrosoftDynamicsNavServer$NAV71_KAPPI service to delayed start

Create Instance, Web Client and ClickOnce with PowerShell

In my earlier posts I showed how to create the databases and how to install Dynamics NAV on the server machines.

The next step is to create an instance for every application and mount the default tenant.  The default tenant is the only one that will be able to change the application data.

To finish this demonstration I will show how I mount an empty tenant database and create a new company with clickonce installation for windows client and an active web client.

For each installation machine I create a separate folder. Within that folder I create a settings file, Set-MachineSettings.ps1

[code lang=”powershell”]$NAV_RemoteMachineAddress = ‘kl4msas37’
$ClientServicesCredentialType = ‘Windows’
[/code]

On my out-facing machine the client credential type is ‘NavUserPassword’. My second settings file is for the instance, Set-Instancesettings.ps1

[code lang=”powershell”]$dbNamePrefix = ‘NAV71_L01_’
$ServiceInstance = $dbNamePrefix + ‘APP’
$DatabaseName = $ServiceInstance
$SQL_DatabaseInstance = ”
$SQL_RemoteMachineAddress = ‘SQLSERVER’
$ManagementServicesPort = 7100
$NAV_WindowsServiceDomain = ‘KAPPI’
$NAV_WindowsServiceAccount = ‘NAVSERVICE’
$NAV_WindowsServiceAccountPassword = ‘NavPass@Word’
$ClientServicesPort = $ManagementServicesPort + 1
$SOAPServicesPort = $ClientServicesPort + 1
$ODataServicesPort = $SOAPServicesPort + 1
$ClickOnceWebSitePort = 80
$DefTenant = $dbNamePrefix + ‘DEF’
$DemoName = ‘www.kappi.is’
$OtherTenants = ”[/code]

With these setting I can create the instance remotly and change the settings to multitenant.

[code lang=”powershell”]Set-StrictMode -Version 2.0

Import-Module ‘N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministrationSamples\NAVRemoteAdministrationSamples.psm1’ -DisableNameChecking
Import-Module ‘N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministration\Misc\Import-NAVAdministrationModuleRemotely.ps1’ -DisableNameChecking

# Import settings
$PSScriptRootV2 = Split-Path $MyInvocation.MyCommand.Definition -Parent
. (Join-Path $PSScriptRootV2 ‘..\Set-DeploySettings.ps1’)
. (Join-Path $PSScriptRootV2 ‘Set-MachineSettings.ps1’)
. (Join-Path $PSScriptRootV2 ‘..\Set-InstanceSettings.ps1’)

#New-NavAdminSession
[int]$currentMemoryLimitPerPSSessionInMB = Get-MaxMemoryPerShellRemotely -RemoteMachineAddress $NAV_RemoteMachineAddress
$requiredMemoryLimitPerSessionInMB = 1024
if(($currentMemoryLimitPerPSSessionInMB -ne 0) -and ($currentMemoryLimitPerPSSessionInMB -lt $requiredMemoryLimitPerSessionInMB))
{
Set-MaxMemoryPerShellRemotely -Value $requiredMemoryLimitPerSessionInMB -RemoteMachineAddress $NAV_RemoteMachineAddress
}

Write-Verbose "Creating remote PS session on $NAV_RemoteMachineAddress…"
$Session = New-PSSession -ComputerName $NAV_RemoteMachineAddress
Write-Verbose "Done creating remote PS session on $NAV_RemoteMachineAddress."

try {
# Import the module into the remote PS session
Invoke-Command -Session $Session -ScriptBlock {
param([string] $ModulePath, [string] $verbosePreference)
$VerbosePreference = $verbosePreference
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
Import-Module $ModulePath
Add-PSSnapin "Microsoft.Dynamics.Nav.Management" -ErrorAction SilentlyContinue
} -ArgumentList (Join-Path $NAV_AdminRemoteDirectory ‘NAVAdministration.psm1’), $VerbosePreference
Write-Verbose "================================================================================"
Write-Verbose ("Deploy-NewInstance starting at " + (Get-Date).ToLongTimeString() + "…")
Write-Verbose "================================================================================"

New-NAVServerInstanceRemotely -ServiceInstance $ServiceInstance -DatabaseName $DatabaseName -DatabaseInstance $SQL_DatabaseInstance -DatabaseServer $SQL_RemoteMachineAddress -ManagementServicesPort $ManagementServicesPort -ClientServicesPort $ClientServicesPort -SOAPServicesPort $SOAPServicesPort -ODataServicesPort $ODataServicesPort -RemoteMachineShortName $NAV_WindowsServiceDomain -ServiceAccount $NAV_WindowsServiceAccount -ServiceAccountPassword $NAV_WindowsServiceAccountPassword -Session $Session
Set-NAVServerConfigurationRemotely -KeyName "ClientServicesCredentialType" -KeyValue $ClientServicesCredentialType -ServerInstance $ServiceInstance -Session $Session
Set-NAVServerConfigurationRemotely -KeyName "MultiTenant" -KeyValue "true" -ServerInstance $ServiceInstance -Session $Session
Set-NAVServerConfigurationRemotely -KeyName "DatabaseName" -KeyValue "" -ServerInstance $ServiceInstance -Session $Session
Set-NAVServerConfigurationRemotely -KeyName ‘NASServicesStartupCodeunit’ -KeyValue ‘450’ -ServerInstance $ServiceInstance -Session $Session
Set-NAVServerConfigurationRemotely -KeyName ‘NASServicesStartupMethod’ -KeyValue ” -ServerInstance $ServiceInstance -Session $Session
Set-NAVServerConfigurationRemotely -KeyName ‘NASServicesStartupArgument’ -KeyValue ‘JOBQUEUE’ -ServerInstance $ServiceInstance -Session $Session

$fullAccountName = $NAV_WindowsServiceDomain + "\" + $NAV_WindowsServiceAccount

# Install certificates
[System.Security.SecureString]$clientServicesPfxPasswordAsSecureString = ConvertTo-SecureString $ClientServicesPfxPassword -AsPlainText -Force
if ($ClientServicesPfxFile -ne $null)
{
Install-ClientServicesCertificate
-ServiceInstance $ServiceInstance

-ServiceAccount $fullAccountName
-ClientServicesPfxFile $ClientServicesPfxFile

-ClientServicesPfxPassword $clientServicesPfxPasswordAsSecureString
-Session $Session
}

Start-ServiceRemotely -ServiceName ("MicrosoftDynamicsNavServer$" + $ServiceInstance) -Session $Session

Write-Verbose "================================================================================"
Write-Verbose ("Deploy-NewInstance finished at " + (Get-Date).ToLongTimeString() + ".")
Write-Verbose "================================================================================"

Write-Output "The NAV Server virtual machine is: $NAV_RemoteMachineAddress"
Write-Output "The NAV Server Instance is: $ServiceInstance"
Write-Output "The NAV Server account credentials: $NAV_WindowsServiceDomain\$NAV_WindowsServiceAccount/$NAV_WindowsServiceAccountPassword"

}
catch [System.Exception] {
Remove-NAVAdminSession -Session $Session
throw $_.Exception
}

finally
{
Remove-NAVAdminSession -Session $Session
}

[/code]

I also activate the JOBQUEUE NAS function by default.
I use the CRONUS database as the default tenant and that is the only one with write access to the application database. My next step is to mount the application and the default tenant and add required users to the default tenant.

[code lang="powershell"]Set-StrictMode -Version 2.0

Import-Module 'N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministrationSamples\NAVRemoteAdministrationSamples.psm1' -DisableNameChecking
Import-Module 'N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministration\Misc\Import-NAVAdministrationModuleRemotely.ps1' -DisableNameChecking

# Import settings
$PSScriptRootV2 = Split-Path $MyInvocation.MyCommand.Definition -Parent
. (Join-Path $PSScriptRootV2 '..\Set-DeploySettings.ps1')
. (Join-Path $PSScriptRootV2 'Set-MachineSettings.ps1')
. (Join-Path $PSScriptRootV2 '..\Set-InstanceSettings.ps1')

#New-NavAdminSession
[int]$currentMemoryLimitPerPSSessionInMB = Get-MaxMemoryPerShellRemotely -RemoteMachineAddress $NAV_RemoteMachineAddress
$requiredMemoryLimitPerSessionInMB = 1024
if(($currentMemoryLimitPerPSSessionInMB -ne 0) -and ($currentMemoryLimitPerPSSessionInMB -lt $requiredMemoryLimitPerSessionInMB))
{
Set-MaxMemoryPerShellRemotely -Value $requiredMemoryLimitPerSessionInMB -RemoteMachineAddress $NAV_RemoteMachineAddress
}

Write-Verbose "Creating remote PS session on $NAV_RemoteMachineAddress..."
$Session = New-PSSession -ComputerName $NAV_RemoteMachineAddress
Write-Verbose "Done creating remote PS session on $NAV_RemoteMachineAddress."

try {
# Import the module into the remote PS session
Invoke-Command -Session $Session -ScriptBlock {
param([string] $ModulePath, [string] $verbosePreference)
$VerbosePreference = $verbosePreference
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
Import-Module $ModulePath
Add-PSSnapin "Microsoft.Dynamics.Nav.Management" -ErrorAction SilentlyContinue
} -ArgumentList (Join-Path $NAV_AdminRemoteDirectory 'NAVAdministration.psm1'), $VerbosePreference

Write-Verbose "Mount Application to $ServiceInstance..."
Invoke-Command -Session $session -ScriptBlock

{
param([string]$ServiceInstance, [string]$DatabaseServer, [string]$DatabaseInstance, [string]$DatabaseName)
Mount-NAVApplication -ServerInstance $ServiceInstance -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -DatabaseName $DatabaseName

}
-ArgumentList $ServiceInstance, $SQL_RemoteMachineAddress, $SQL_DatabaseInstance, $DatabaseName

Write-Verbose "Mount Default Tenant to $ServiceInstance..."
Invoke-Command -Session $session -ScriptBlock

{
param([string]$ServiceInstance, [string]$DatabaseServer, [string]$DatabaseInstance, [string]$DatabaseName, [string]$AlternateId)
Mount-NAVTenant -ServerInstance $ServiceInstance -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -DatabaseName $DatabaseName -Id Default -OverwriteTenantIdInDatabase -AllowAppDatabaseWrite -AlternateId $AlternateId

}
-ArgumentList $ServiceInstance, $SQL_RemoteMachineAddress, $SQL_DatabaseInstance, $DefTenant, $DemoName

[System.Security.SecureString]$NAVPasswordAsSecureString = ConvertTo-SecureString $NAVAdminPassword -AsPlainText -Force
New-NAVServerUserRemotely -UserName $NAVAdminUserName -Password $NAVPasswordAsSecureString -ServerInstance $ServiceInstance -Tenant Default -Session $Session
New-NAVServerUserPermissionSetRemotely -UserName $NAVAdminUserName -PermissionSetId "SUPER" -ServerInstance $ServiceInstance -Tenant Default -Session $Session

$fullUserName = $NAV_WindowsServiceDomain + "\" + $NAV_WindowsServiceAccount
New-NAVServerUserRemotely -WindowsAccount $fullUserName -ServerInstance $ServiceInstance -Tenant Default -Session $Session
New-NAVServerUserPermissionSetRemotely -UserName $fullUserName -PermissionSetId "SUPER" -ServerInstance $ServiceInstance -Tenant Default -Session $Session

$fullUserName = $NAV_WindowsServiceDomain + "\kappi"
New-NAVServerUserRemotely -WindowsAccount $fullUserName -ServerInstance $ServiceInstance -Tenant Default -Session $Session
New-NAVServerUserPermissionSetRemotely -UserName $fullUserName -PermissionSetId "SUPER" -ServerInstance $ServiceInstance -Tenant Default -Session $Session

if ($OtherTenants -ne '') {
foreach ($tenant in $OtherTenants)
{
$tenantdbName = $dbNamePrefix + $tenant
Write-Verbose "Mount Tenant $tenant to $ServiceInstance..."
Invoke-Command -Session $session -ScriptBlock

{
param([string]$ServiceInstance, [string]$DatabaseServer, [string]$DatabaseInstance, [string]$TenantId, [string]$DatabaseName)
Mount-NAVTenant -ServerInstance $ServiceInstance -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -DatabaseName $DatabaseName -Id $TenantId -OverwriteTenantIdInDatabase

}
-ArgumentList $ServiceInstance, $SQL_RemoteMachineAddress, $SQL_DatabaseInstance, $tenant, $tenantdbName

}
}

Write-Verbose "Done Mounting Tenants to Server Instance $ServiceInstance on $NAV_RemoteMachineAddress."

}
catch [System.Exception] {
Remove-NAVAdminSession -Session $Session
throw $_.Exception
}

finally
{
Remove-NAVAdminSession -Session $Session
}

[/code]

If something when wrong I can use this script to remote the instance and try again.

[code lang="powershell"]Set-StrictMode -Version 2.0

Import-Module 'N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministrationSamples\NAVRemoteAdministrationSamples.psm1' -DisableNameChecking
Import-Module 'N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministration\Misc\Import-NAVAdministrationModuleRemotely.ps1' -DisableNameChecking

# Import settings
$PSScriptRootV2 = Split-Path $MyInvocation.MyCommand.Definition -Parent
. (Join-Path $PSScriptRootV2 '..\Set-DeploySettings.ps1')
. (Join-Path $PSScriptRootV2 'Set-MachineSettings.ps1')
. (Join-Path $PSScriptRootV2 '..\Set-InstanceSettings.ps1')

#New-NavAdminSession
[int]$currentMemoryLimitPerPSSessionInMB = Get-MaxMemoryPerShellRemotely -RemoteMachineAddress $NAV_RemoteMachineAddress
$requiredMemoryLimitPerSessionInMB = 1024
if(($currentMemoryLimitPerPSSessionInMB -ne 0) -and ($currentMemoryLimitPerPSSessionInMB -lt $requiredMemoryLimitPerSessionInMB))
{
Set-MaxMemoryPerShellRemotely -Value $requiredMemoryLimitPerSessionInMB -RemoteMachineAddress $NAV_RemoteMachineAddress
}

Write-Verbose "Creating remote PS session on $NAV_RemoteMachineAddress..."
$Session = New-PSSession -ComputerName $NAV_RemoteMachineAddress
Write-Verbose "Done creating remote PS session on $NAV_RemoteMachineAddress."

try {
# Import the module into the remote PS session
Invoke-Command -Session $Session -ScriptBlock {
param([string] $ModulePath, [string] $verbosePreference)
$VerbosePreference = $verbosePreference
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
Import-Module $ModulePath
Add-PSSnapin "Microsoft.Dynamics.Nav.Management" -ErrorAction SilentlyContinue
} -ArgumentList (Join-Path $NAV_AdminRemoteDirectory 'NAVAdministration.psm1'), $VerbosePreference

Write-Verbose "Removing Server Instance $ServiceInstance..."
Invoke-Command -Session $session -ScriptBlock

{
param([string]$ServiceInstance)
Remove-NAVServerInstance -ServerInstance $ServiceInstance -Force

}
-ArgumentList $ServiceInstance

Write-Verbose "Done Removing Server Instance $ServiceInstance on $NAV_RemoteMachineAddress."

}
catch [System.Exception] {
Remove-NAVAdminSession -Session $Session
throw $_.Exception
}

finally
{
Remove-NAVAdminSession -Session $Session
}

[/code]

All the framework is installed and running. Now it is time to prepare the system for my first client. I create a folder for each client and in that folder I put the Set-TenantSettings.ps1 file.

[code lang="powershell"]# Select Instance
. (Join-Path $PSScriptRootV2 '..\Set-InstanceSettings.ps1')

# Install on machines
$InstallOnMachines = ('PUBLICSERVER','LOCALSERVER','DEVSERVER')

# Next available Tenant
$TenantId = '01'
# Domain name for ClickOnce and Web Client
$AlternateIds = @("microsoft.kappi.is")
$EndpointIdentifier = 'microsoft.kappi.is'

# New Company Name
$NewCompanyName = 'Microsoft'

# Activate NAS
$EnableNAS = '$true'

# Web Client Path
$WebServerInstance = 'NAV71_MS'

# ClickOnce Client Application Name - No need to change
$applicationName = "Microsoft Dynamics NAV 2013 R2 for $NewCompanyName"

# First Super User
$ClientUserName = 'Gunnar'
$ClientUserPassword = 'gunnar@dynamics.is'
[/code]

Now I am all set to mount my first tenant and create the first company. This script will also create the users needed for the tenant.

[code lang="powershell"]Set-StrictMode -Version 2.0

Import-Module 'N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministrationSamples\NAVRemoteAdministrationSamples.psm1' -DisableNameChecking
Import-Module 'N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministration\Misc\Import-NAVAdministrationModuleRemotely.ps1' -DisableNameChecking

# Import settings
$PSScriptRootV2 = Split-Path $MyInvocation.MyCommand.Definition -Parent
. (Join-Path $PSScriptRootV2 '..\Set-DeploySettings.ps1')
. (Join-Path $PSScriptRootV2 'Set-TenantSettings.ps1')

$LoopCount = 0

# Loop Machines
foreach ($InstallOnMachine in $InstallOnMachines)
{
$MachineSettingsPath = '..\' + $InstallOnMachine + '\Set-MachineSettings.ps1'
. (Join-Path $PSScriptRootV2 $MachineSettingsPath)
$LoopCount = $LoopCount + 1

#New-NavAdminSession
[int]$currentMemoryLimitPerPSSessionInMB = Get-MaxMemoryPerShellRemotely -RemoteMachineAddress $NAV_RemoteMachineAddress
$requiredMemoryLimitPerSessionInMB = 1024
if(($currentMemoryLimitPerPSSessionInMB -ne 0) -and ($currentMemoryLimitPerPSSessionInMB -lt $requiredMemoryLimitPerSessionInMB))
{
Set-MaxMemoryPerShellRemotely -Value $requiredMemoryLimitPerSessionInMB -RemoteMachineAddress $NAV_RemoteMachineAddress
}

Write-Verbose "Creating remote PS session on $NAV_RemoteMachineAddress..."
$Session = New-PSSession -ComputerName $NAV_RemoteMachineAddress
Write-Verbose "Done creating remote PS session on $NAV_RemoteMachineAddress."

try {
# Import the module into the remote PS session
Invoke-Command -Session $Session -ScriptBlock {
param([string] $ModulePath, [string] $verbosePreference)
$VerbosePreference = $verbosePreference
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
Import-Module $ModulePath
Add-PSSnapin "Microsoft.Dynamics.Nav.Management" -ErrorAction SilentlyContinue
} -ArgumentList (Join-Path $NAV_AdminRemoteDirectory 'NAVAdministration.psm1'), $VerbosePreference

$DatabaseName = $dbNamePrefix + $TenantId
Invoke-Command -Session $Session -ScriptBlock

{
param([string]$TenandId, [string]$ServiceInstance, [string]$DatabaseName, [string]$DatabaseInstance, [string]$DatabaseServer, [string]$AlternateId, [string]$DefaultCompanyName, [string]$EnableNAS, [int]$LoopCount)
if ($EnableNAS -eq ‘$true’)
{
Mount-NAVTenant -Id $TenandId -ServerInstance $ServiceInstance -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -AlternateId $AlternateId -DefaultCompany $DefaultCompanyName -NasServicesEnabled -OverwriteTenantIdInDatabase
}
else
{
Mount-NAVTenant -Id $TenandId -ServerInstance $ServiceInstance -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -AlternateId $AlternateId -DefaultCompany $DefaultCompanyName -OverwriteTenantIdInDatabase
}
if ($LoopCount -eq 1)
{
New-NAVCompany -ServerInstance $ServiceInstance -Tenant $TenandId -CompanyName $DefaultCompanyName
}
}
-ArgumentList $TenantId, $ServiceInstance, $DatabaseName, $SQL_DatabaseInstance, $SQL_RemoteMachineAddress, $AlternateIds, $NewCompanyName, $EnableNAS, $LoopCount

if ($LoopCount -eq 1)
{
[System.Security.SecureString]$NAVPasswordAsSecureString = ConvertTo-SecureString $NAVAdminPassword -AsPlainText -Force
New-NAVServerUserRemotely -UserName $NAVAdminUserName -Password $NAVPasswordAsSecureString -ServerInstance $ServiceInstance -Tenant $TenantId -Session $Session -ChangePasswordAtNextLogOn $false
New-NAVServerUserPermissionSetRemotely -UserName $NAVAdminUserName -PermissionSetId "SUPER" -ServerInstance $ServiceInstance -Tenant $TenantId -Session $Session

$fullUserName = $NAV_WindowsServiceDomain + "\" + $NAV_WindowsServiceAccount
New-NAVServerUserRemotely -WindowsAccount $fullUserName -ServerInstance $ServiceInstance -Tenant $TenantId -Session $Session
New-NAVServerUserPermissionSetRemotely -UserName $fullUserName -PermissionSetId "SUPER" -ServerInstance $ServiceInstance -Tenant $TenantId -Session $Session

[System.Security.SecureString]$ClientUserPasswordAsSecureString = ConvertTo-SecureString $ClientUserPassword -AsPlainText -Force
New-NAVServerUserRemotely -UserName $ClientUserName -Password $ClientUserPasswordAsSecureString -ServerInstance $ServiceInstance -Tenant $TenantId -Session $Session -ChangePasswordAtNextLogOn $true
New-NAVServerUserPermissionSetRemotely -UserName $NAVAdminUserName -PermissionSetId "SUPER" -ServerInstance $ServiceInstance -Tenant $TenantId -Session $Session
}
}
catch [System.Exception] {
Remove-NAVAdminSession -Session $Session
throw $_.Exception
}

finally
{
Remove-NAVAdminSession -Session $Session
}

}[/code]

All set and now to create the client distribution with clickonce.

[code lang="powershell"]Set-StrictMode -Version 2.0

Import-Module 'N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministrationSamples\NAVRemoteAdministrationSamples.psm1' -DisableNameChecking
Import-Module 'N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministration\Misc\Import-NAVAdministrationModuleRemotely.ps1' -DisableNameChecking
Import-Module 'N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministration\ClickOnce\Test-MageExePrerequisite.ps1' -DisableNameChecking
Import-Module 'N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministration\ClickOnce\Get-MageExeLocation.ps1' -DisableNameChecking
Import-Module 'N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministration\ClickOnce\Set-ManifestSignatureRemotely.ps1' -DisableNameChecking
Import-Module 'N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministration\ClickOnce\New-ClickOnceWebSiteRemotely.ps1' -DisableNameChecking

# Import settings
$PSScriptRootV2 = Split-Path $MyInvocation.MyCommand.Definition -Parent
. (Join-Path $PSScriptRootV2 '..\Set-DeploySettings.ps1')
. (Join-Path $PSScriptRootV2 '..\PUBLICSERVER\Set-MachineSettings.ps1')
. (Join-Path $PSScriptRootV2 '..\Set-InstanceSettings.ps1')
. (Join-Path $PSScriptRootV2 'Set-TenantSettings')

#New-NavAdminSession
[int]$currentMemoryLimitPerPSSessionInMB = Get-MaxMemoryPerShellRemotely -RemoteMachineAddress $NAV_RemoteMachineAddress
$requiredMemoryLimitPerSessionInMB = 1024
if(($currentMemoryLimitPerPSSessionInMB -ne 0) -and ($currentMemoryLimitPerPSSessionInMB -lt $requiredMemoryLimitPerSessionInMB))
{
Set-MaxMemoryPerShellRemotely -Value $requiredMemoryLimitPerSessionInMB -RemoteMachineAddress $NAV_RemoteMachineAddress
}

Write-Verbose "Creating remote PS session on $NAV_RemoteMachineAddress..."
$Session = New-PSSession -ComputerName $NAV_RemoteMachineAddress
Write-Verbose "Done creating remote PS session on $NAV_RemoteMachineAddress."

try {
# Import the module into the remote PS session
Invoke-Command -Session $Session -ScriptBlock {
param([string] $ModulePath, [string] $verbosePreference)
$VerbosePreference = $verbosePreference
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
Import-Module $ModulePath
Add-PSSnapin "Microsoft.Dynamics.Nav.Management" -ErrorAction SilentlyContinue
} -ArgumentList (Join-Path $NAV_AdminRemoteDirectory 'NAVAdministration.psm1'), $VerbosePreference

# Create a web site that holds a ClickOnce deployment of the Windows client
Write-Verbose "Creating a web site that holds a ClickOnce deployment of the Windows client on $NAV_RemoteMachineAddress..."
[System.Security.SecureString]$clickOnceCodeSigningPfxPasswordAsSecureString = $null
if ($ClickOnceCodeSigningPfxPassword)
{
$clickOnceCodeSigningPfxPasswordAsSecureString = ConvertTo-SecureString $ClickOnceCodeSigningPfxPassword -AsPlainText -Force
}
$Port = $ClickOnceWebSitePort
$clickOnceDeploymentId = "ClickOnce_${ServiceInstance}_at_${EndpointIdentifier}"
$clickOnceDirectory = Join-Path 'C:\inetpub' $clickOnceDeploymentId
$webSiteUrl = ("http://" + $EndpointIdentifier + ":" + $Port)

Write-Verbose "Creating new ClickOnce web site for server instance $ServiceInstance at address $webSiteUrl..."

# Create and populate the directory on the remote machine
[xml]$clientUserSettings = New-NAVClientUserSettings

-Session $Session
-Server $EndpointIdentifier

-ServerInstance $ServiceInstance
-ClientServicesPort $ClientServicesPort

-DnsIdentity $dnsIdentity
Edit-NAVClientUserSettings -ClientUserSettings $ClientUserSettings  -KeyName ‘ClientServicesCredentialType’ -NewValue $ClientServicesCredentialType
Edit-NAVClientUserSettings -ClientUserSettings $ClientUserSettings  -KeyName ‘ServicesCertificateValidationEnabled’ -NewValue false
Edit-NAVClientUserSettings -ClientUserSettings $ClientUserSettings  -KeyName ‘TenantId’ -NewValue $TenantId

Invoke-Command -Session $session -ScriptBlock {
param([string]$ClickOnceDirectory,[xml]$ClientUserSettings)
New-ClickOnceDirectory
-ClickOnceDirectory $ClickOnceDirectory

-ClientUserSettings $ClientUserSettings
} -ArgumentList $clickOnceDirectory,$ClientUserSettings

# Copy the mage.exe file to the remote machine
if (!(Test-MageExePrerequisite))
{
Write-Error ‘Mage.exe not found or wrong version. Install the Windows SDK from http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=8279, and set up the global variable MageExeFile ($Global:MageFileExe) to point to the executable, probably C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\NETFX 4.0 Tools\mage.exe.’
return $null
}
$localMageExeFile = Get-MageExeLocation
$remoteMageExeFile = Join-Path $NAV_RemoteFolder (Split-Path -Leaf $localMageExeFile)
Copy-FileToRemoteMachine -SourceFile $localMageExeFile -DestinationFile $remoteMageExeFile -Session $session

# Adjust the application manifest (Microsoft.Dynamics.Nav.Client.exe.manifest)
$clickOnceApplicationFilesDirectory = Join-Path $clickOnceDirectory ‘Deployment\ApplicationFiles’
$applicationManifestFile = Join-Path $clickOnceApplicationFilesDirectory ‘Microsoft.Dynamics.Nav.Client.exe.manifest’
$applicationIdentityName = "$clickOnceDeploymentId application identity" # any unique value will do
$applicationIdentityVersion = "1.0.0.0" # any version will do; it is not tied to the product version
Invoke-Command -Session $session -ScriptBlock {
param([string]$ApplicationManifestFile,[string]$ApplicationIdentityName,[string]$ApplicationIdentityVersion,[string]$ApplicationFilesDirectory,[string]$RemoteMageExeFile)

# Note: be careful to run "mage -update" before you change settings, because "mage -update" sometimes resets some of the other settings
Set-ApplicationManifestFileList
-ApplicationManifestFile $ApplicationManifestFile

-ApplicationFilesDirectory $ApplicationFilesDirectory
-MageExeLocation $RemoteMageExeFile
Set-ApplicationManifestApplicationIdentity

-ApplicationManifestFile $ApplicationManifestFile
-ApplicationIdentityName $ApplicationIdentityName

-ApplicationIdentityVersion $ApplicationIdentityVersion
} -ArgumentList $applicationManifestFile,$applicationIdentityName,$applicationIdentityVersion,$ClickOnceApplicationFilesDirectory,$remoteMageExeFile

# Sign the application manifest
if ($ClickOnceCodeSigningPfxFile)
{
Set-ManifestSignatureRemotely
-ManifestFile $applicationManifestFile

-CodeSigningPfxFile $ClickOnceCodeSigningPfxFile
-CodeSigningPfxPassword $clickOnceCodeSigningPfxPasswordAsSecureString

-MageExeLocation $localMageExeFile
-Session $Session
}

# Adjust the deployment manifest (Microsoft.Dynamics.Nav.Client.application)
$deploymentManifestFile = Join-Path $clickOnceDirectory 'Deployment\Microsoft.Dynamics.Nav.Client.application'
$deploymentIdentityName = "$clickOnceDeploymentId deployment identity" # any unique value will do
$deploymentIdentityVersion = "1.0.0.0" # any version will do; it is not tied to the product version
$deploymentManifestUrl = ($webSiteUrl + "/Deployment/Microsoft.Dynamics.Nav.Client.application")
$applicationManifestUrl = ($webSiteUrl + "/Deployment/ApplicationFiles/Microsoft.Dynamics.Nav.Client.exe.manifest")
Invoke-Command -Session $session -ScriptBlock {
param([string]$DeploymentManifestFile,[string]$ApplicationManifestFile,[string]$DeploymentIdentityName,[string]$DeploymentIdentityVersion,[string]$ApplicationPublisher,[string]$ApplicationName,[string]$DeploymentManifestUrl,[string]$ApplicationManifestUrl,[string]$RemoteMageExeFile)
# Note: be careful to run "mage -update" before you change settings, because "mage -update" sometimes resets some of the other settings
Set-DeploymentManifestApplicationReference

-DeploymentManifestFile $DeploymentManifestFile
-ApplicationManifestFile $ApplicationManifestFile

-ApplicationManifestUrl $ApplicationManifestUrl
-MageExeLocation $RemoteMageExeFile
Set-DeploymentManifestSettings

-DeploymentManifestFile $DeploymentManifestFile
-DeploymentIdentityName $DeploymentIdentityName

-DeploymentIdentityVersion $DeploymentIdentityVersion
-ApplicationPublisher $ApplicationPublisher

-ApplicationName $ApplicationName
-DeploymentManifestUrl $DeploymentManifestUrl
} -ArgumentList $deploymentManifestFile,$applicationManifestFile,$deploymentIdentityName,$deploymentIdentityVersion,$ApplicationPublisher,$ApplicationName,$deploymentManifestUrl,$applicationManifestUrl,$remoteMageExeFile

# Sign the deployment manifest
if ($ClickOnceCodeSigningPfxFile)
{
Set-ManifestSignatureRemotely

-ManifestFile $deploymentManifestFile
-CodeSigningPfxFile $ClickOnceCodeSigningPfxFile

-CodeSigningPfxPassword $clickOnceCodeSigningPfxPasswordAsSecureString
-MageExeLocation $localMageExeFile

-Session $Session
}

# Create the actual web site
Invoke-Command -Session $session -ScriptBlock {
param([string]$WebSiteName,[int]$Port,[string]$PhysicalPath, [string]$EndpointIdentifier, [string]$AdminRemoteDirectory)
Write-Verbose "Creating new web site $WebSiteName on port $Port, pointing to $PhysicalPath…"

# Create the web site
$website = New-Website -Name $WebSiteName -Port $Port -PhysicalPath $PhysicalPath -HostHeader $EndpointIdentifier -Force

# Put a web.config file in the root folder, which will tell IIS which .html file to open
$sourceFile = Join-Path $AdminRemoteDirectory ‘ClickOnce\Resources\root_web.config’
$targetFile = Join-Path $PhysicalPath ‘web.config’
Copy-Item $sourceFile -destination $targetFile

# Put a web.config file in the Deployment folder, which will tell IIS to allow downloading of .config files etc.
$sourceFile = Join-Path $AdminRemoteDirectory ‘ClickOnce\Resources\deployment_web.config’
$targetFile = Join-Path $PhysicalPath ‘Deployment\web.config’
Copy-Item $sourceFile -destination $targetFile

# Open firewall on the given port
New-FirewallPortAllowRule -RuleName $WebSiteName -Port $Port

Write-Verbose "Done creating new web site $WebSiteName on port $Port, pointing to $PhysicalPath."

} -ArgumentList $clickOnceDeploymentId,$Port,$clickOnceDirectory, $EndpointIdentifier, $NAV_AdminRemoteDirectory

Write-Verbose "Done creating new ClickOnce web site for server instance $ServiceInstance at address $webSiteUrl."
Write-Output "The Windows Client can be downloaded via ClickOnce at: $webSiteUrl"

}
catch [System.Exception] {
Remove-NAVAdminSession -Session $Session
throw $_.Exception
}

finally
{
Remove-NAVAdminSession -Session $Session
}

[/code]

And finally the web client.

[code lang=”powershell”]Set-StrictMode -Version 2.0

Import-Module ‘N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministrationSamples\NAVRemoteAdministrationSamples.psm1’ -DisableNameChecking
Import-Module ‘N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministration\Misc\Import-NAVAdministrationModuleRemotely.ps1’ -DisableNameChecking

# Import settings
$PSScriptRootV2 = Split-Path $MyInvocation.MyCommand.Definition -Parent
. (Join-Path $PSScriptRootV2 ‘..\Set-DeploySettings.ps1’)
. (Join-Path $PSScriptRootV2 ‘..\PUBLICSERVER\Set-MachineSettings.ps1’)
. (Join-Path $PSScriptRootV2 ‘..\Set-InstanceSettings.ps1’)
. (Join-Path $PSScriptRootV2 ‘Set-TenantSettings’)

#New-NavAdminSession
[int]$currentMemoryLimitPerPSSessionInMB = Get-MaxMemoryPerShellRemotely -RemoteMachineAddress $NAV_RemoteMachineAddress
$requiredMemoryLimitPerSessionInMB = 1024
if(($currentMemoryLimitPerPSSessionInMB -ne 0) -and ($currentMemoryLimitPerPSSessionInMB -lt $requiredMemoryLimitPerSessionInMB))
{
Set-MaxMemoryPerShellRemotely -Value $requiredMemoryLimitPerSessionInMB -RemoteMachineAddress $NAV_RemoteMachineAddress
}

Write-Verbose "Creating remote PS session on $NAV_RemoteMachineAddress…"
$Session = New-PSSession -ComputerName $NAV_RemoteMachineAddress
Write-Verbose "Done creating remote PS session on $NAV_RemoteMachineAddress."

try {
# Import the module into the remote PS session
Invoke-Command -Session $Session -ScriptBlock {
param([string] $ModulePath, [string] $verbosePreference)
$VerbosePreference = $verbosePreference
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
Import-Module $ModulePath
Add-PSSnapin "Microsoft.Dynamics.Nav.Management" -ErrorAction SilentlyContinue
} -ArgumentList (Join-Path $NAV_AdminRemoteDirectory ‘NAVAdministration.psm1’), $VerbosePreference

Invoke-Command -Session $Session -ScriptBlock
{
param([string]$ClientServicesCredentialType, [int]$ClientServicesPort, [string]$Company, [string]$DnsIdentity, [string]$Server, [string]$ServerInstance, [string]$WebServerInstance, [string]$language, [string]$regionFormat, [string]$TenantId)

Write-Host "Creating Web Site " $WebServerInstance " ..."
New-NAVWebServerInstance -ClientServicesCredentialType $ClientServicesCredentialType -ClientServicesPort $ClientServicesPort -Company $Company -DnsIdentity $DnsIdentity -Server $Server -ServerInstance $ServerInstance -WebServerInstance $WebServerInstance -Language $language -RegionFormat $regionFormat

}
-ArgumentList $ClientServicesCredentialType, $ClientServicesPort, $NewCompanyName, $DnsIdentity, $ServerDNSName, $ServiceInstance, $WebServerInstance, $WebServerLanguage, $WebServerRegionFormat, $TenantId
}
catch [System.Exception] {
Remove-NAVAdminSession -Session $Session
throw $_.Exception
}

finally
{
Remove-NAVAdminSession -Session $Session
}

[/code]

I am using the URL rewrite method in IIS and that has to be enabled. See this post from Microsoft on how to do that.

The next task is to create the required scripts that will install the client and the server updates and update the clickonce distribution.  This will be posted later.

Restore Database with PowerShell and create Tenants

In my latest blog in this PowerShell series I showed how to install Dynamics NAV on a remote or local computer with PowerShell.

The next step is to create the databases. To prepare PowerShell I use these functions to prepare for NAV and SQL administration

[code lang=”powershell”]Import-Module ‘C:\Program Files\Microsoft Dynamics NAV\71\Service\NavAdminTool.ps1’
Import-Module SQLPS
[/code]

I use the Demo Database that is included with the Dynamics NAV DVD to create the default tenant database. This PowerShell script restores the database backup to a new name in a given path on a given SQL server.

[code lang=”powershell”]$NewDatabaseName = ‘NAV71_L01_DEF’
$NewDatabasePath = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA’
$BackupFile = ‘C:\SQLDemoDatabase\CommonAppData\Microsoft\Microsoft Dynamics NAV\71\Database\Demo Database NAV (7-1).bak’
$sqlserver = ‘sqlserver’

$NewDatabaseFullPath = Join-Path $NewDatabasePath $NewDatabaseName
Write-Host Restoring $BackupFile to $NewDatabaseFullPath

#Load the DLLs if not using SQLPS
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SmoExtended’) | out-null

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver
$Restore = New-Object Microsoft.SqlServer.Management.Smo.Restore

#settings for the restore
$Restore.Action = "Database"
$Restore.NoRecovery = $false;
$Restore.ReplaceDatabase = $false;
$RestorePercentCompleteNotification = 5;
$Restore.Devices.AddDevice($BackupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

#get the db name
$RestoreDetails = $Restore.ReadBackupHeader($server)

#print database name
"Database Name from Backup File : " + $RestoreDetails.Rows[0]["DatabaseName"]

#give a new database name
$OldDatabaseName = $RestoreDetails.Rows[0]["DatabaseName"]
$Restore.Database = $NewDatabaseName

Write-Host Changing Database Name $OldDatabaseName to $NewDatabaseName

$RestoreFile = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile
$RestoreLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile

#set file names; use the default database directory
$OldDataFileName = $OldDatabaseName + ‘_Data’
$NewDataFileName = Join-Path $NewDatabasePath $NewDatabaseName
$RestoreFile.LogicalFileName = $OldDataFileName
$RestoreFile.PhysicalFileName = $NewDataFileName + ‘.mdf’
Write-Host Changing Data File $OldDataFileName to $NewDataFileName
$OldLogFileName = $OldDatabaseName + ‘_Log’
$NewLogFileName = Join-Path $NewDatabasePath $NewDatabaseName
$RestoreLog.LogicalFileName = $OldLogFileName
$RestoreLog.PhysicalFileName = $NewLogFileName + ‘.ldf’
Write-Host Changing Log File $OldLogFileName to $NewLogFileName
$Restore.RelocateFiles.Add($RestoreFile)
$Restore.RelocateFiles.Add($RestoreLog)

$Restore.SqlRestore($Server)
write-host "Restore of " $NewDatabaseName "Complete"

# add role membership
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$sqlserver;Integrated Security=SSPI;Initial Catalog=$NewDatabaseName");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N’db_owner’, @membername = N’NT AUTHORITY\NETWORK SERVICE’"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()[/code]

To be able to use this the SQL Management tools must be installed. After the database has been restored the script makes the NETWORK SERVICE account an owner. This can of course be customized to another user(s).

The following steps are needed to set up multi tenancy. First I need a temporary Dynamics NAV service Instance.

[code lang=”powershell”]$ClientPort = 7210
$ServiceInstanceName = ‘NAV71_L01_APP’
$SoapPort = $ClientPort + 1
$ODataPort = $SoapPort + 1
$MgtPort = $ODataPort + 1
$DatabaseName = ‘NAV71_L01_DEF’
$DatabaseServer = ‘sqlserver’
$DatabaseInstance = ”

New-NAVServerInstance -ServerInstance $ServiceInstanceName -ClientServicesCredentialType Windows -ClientServicesPort $ClientPort -DatabaseInstance $DatabaseInstance -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -ManagementServicesPort $MgtPort -SOAPServicesPort $SoapPort -ODataServicesPort $ODataPort
Set-NAVServerInstance -ServerInstance $ServiceInstanceName -Start[/code]

Next step is to export the application to a separate database and change the service configuration to multi tenant.

[code lang=”powershell”]$ServiceInstance = ‘NAV71_L01_APP’
$DatabaseName = ‘NAV71_L01_DEF’
$DatabaseServer = ‘sqlserver’
$DatabaseInstance = ”

# Stop the NAV Service
Set-NAVServerInstance $ServiceInstance -Stop

# Export the NAV Application to a new Database and then remove
Export-NAVApplication -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -DestinationDatabaseName $ServiceInstance
Remove-NAVApplication -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -Force

# Change Service to MultiTenant
Set-NAVServerConfiguration -ServerInstance $ServiceInstance -KeyName MultiTenant -KeyValue "true"
Set-NAVServerConfiguration -ServerInstance $ServiceInstance -KeyName DatabaseName -KeyValue ""
Set-NAVServerInstance $ServiceInstance -Start

# Mount Application
Mount-NAVApplication -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -DatabaseName $ServiceInstance -ServerInstance $ServiceInstance
Mount-NAVTenant -ServerInstance $ServiceInstance -Id Default -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -DatabaseName $DatabaseName -OverwriteTenantIdInDatabase -AllowAppDatabaseWrite

Get-NAVTenant -ServerInstance $ServiceInstance | Format-Table
[/code]

I like to create empty tenant databases that I can later pick up and use for customers. Here I create ten databases and attach them to the service to initialize the common tables.

[code lang=”powershell”]$ServiceInstance = ‘NAV71_L01_APP’
$DatabaseServer = ‘sqlserver’
$DatabaseInstance = ”
$TenantPrefix = ‘NAV71_L01_’

#Load the DLLs if not using SQLPS
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SmoExtended’) | out-null

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $DatabaseServer

$NewIds = (’00’,’01’,’02’,’03’,’04’,’05’,’06’,’07’,’08’,’09’)

foreach ($NewId in $NewIds)
{
$DatabaseName = $TenantPrefix + $NewId
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database($DatabaseServer, $DatabaseName)
$db.RecoveryModel = ‘full’
$db.Create()

# add role membership
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$DatabaseServer;Integrated Security=SSPI;Initial Catalog=$DatabaseName");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N’db_owner’, @membername = N’NT AUTHORITY\NETWORK SERVICE’"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()

Mount-NAVTenant -ServerInstance $ServiceInstance
-Id $NewId

-DatabaseServer $DatabaseServer
-DatabaseInstance $DatabaseInstance

-DatabaseName $DatabaseName `
-OverwriteTenantIdInDatabase
}[/code]

Since I did all this on my developement machine I wanted to move all the databases to the production SQL server. So I created a script to backup all the databases.

[code lang=”powershell”]$BackupLocation = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\’

$appDatabases = Get-NAVServerInstance | Where-Object {$_.DisplayName -match ‘NAV71_’} | Get-NAVApplication
foreach ($appDatabase in $appDatabases)
{
$dbName = $appDatabase.’Database name’
$dbServer = $appDatabase.’Database server’
$backupFile = $BackupLocation + $dbName + ‘.bak’
write-host "Backing up database $dbName on $dbServer"
Backup-SqlDatabase -ServerInstance $dbServer -Database $dbName -BackupAction Database -BackupFile $backupFile
}
$tenantDatabases = Get-NAVServerInstance | Where-Object {$_.DisplayName -match ‘NAV71_’} | Get-NAVTenant
foreach ($tenantDatabase in $tenantDatabases)
{
$dbName = $tenantDatabase.DatabaseName
$dbServer = $tenantDatabase.DatabaseServer
$backupFile = $BackupLocation + $dbName + ‘.bak’
write-host "Backing up database $dbName on $dbServer"
Backup-SqlDatabase -ServerInstance $dbServer -Database $dbName -BackupAction Database -BackupFile $backupFile
}
[/code]

And the last step is to stop and remove the temporary Dynamics NAV service instance.

[code lang=”powershell”]$ServiceInstanceName = ‘NAV71_L01_APP’

Set-NAVServerInstance -ServerInstance $ServiceInstanceName -Stop
Remove-NAVServerInstance -ServerInstance $ServiceInstanceName -Force
[/code]

On the production SQL server I restored the databases with this script. I also give the service user owner rights to the databases.

[code lang=”powershell”]#Load the DLLs if not using SQLPS
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for smo.backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$Backups = dir ‘T:\Recover\Nav71\*.bak’
$NewDatabasePath = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA’
$sqlserver = ‘sqlserver’
$ServiceAccount = ‘DOMAIN\SERVICE USER’

foreach ($BackupFile in $Backups) {
‘Restoring ‘ + $BackupFile.Directory + ‘\’ + $BackupFile.Name

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver
$backupDevice = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $BackupFile, "File"
$Restore = New-Object Microsoft.SqlServer.Management.Smo.Restore

#Set properties for Restore
$Restore.NoRecovery = $false;
$Restore.ReplaceDatabase = $true;
$Restore.Devices.Add($backupDevice)
$RestoreDetails = $Restore.ReadBackupHeader($server)
$NewDatabaseName = Get-ChildItem $BackupFile | % {$_.BaseName}
$Restore.Database = $NewDatabaseName

# Specify the need to relocate the data and log files (mdf and ldf)
$resFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$resLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")

# The logical file names should be the logical filename stored in the backup media
# The physical file should reflect the updated path
$resFile.LogicalFileName = $RestoreDetails.Rows[0]["DatabaseName"]
$resLog.LogicalFileName = $RestoreDetails.Rows[0]["DatabaseName"] + "_Log"
$resFile.PhysicalFileName = $NewDatabasePath + $RestoreDetails.Rows[0]["DatabaseName"] + "_Data.mdf"
$resLog.PhysicalFileName = $NewDatabasePath + $RestoreDetails.Rows[0]["DatabaseName"] + "_Log.ldf"
$Restore.RelocateFiles.Add($resFile)
$Restore.RelocateFiles.Add($resLog)

$Restore.SqlRestore($server)
# add role membership
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$sqlserver;Integrated Security=SSPI;Initial Catalog=$NewDatabaseName");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N’db_owner’, @membername = N’$ServiceAccount’"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()
}
[/code]

Next we take a look at creating the server instance on a remote machine, mounting the tenant and creating the ClickOnce website along with the Web Client.

Remotly install NAV with PowerShell

This will be my first post in a series of many that will cover a Dynamics NAV installation in Azure or on Premise.  Everything is set up as on premise so if you are planning to use this on Azure you will need to run the scripts from an Azure VM.

Every machine that I am installing on needs to allow PowerShell Remote Session.  Make sure that the Windows Management Framework 3.0 is installed.  Then make sure that you enable Remote Management.

OpenRemoteManagement

That should take care of the basics and now we will be working on our management machine where all the installs will be running from.

In the directory that I store my scripts, I create a subfolder for each machine that requires an install.  In that folder I have few files.

First I have a file called Set-MachineSettings.ps1 that contains the setup parameters for this machine.  Only two lines.

[code lang=”powershell”]$NAV_RemoteMachineAddress = ‘kl4msweb05’
$ClientServicesCredentialType = ‘NavUserPassword'[/code]

I also have an XML file that holds the installation configuration for Dynamics NAV

[code lang=”xml”]

[/code]

In the parent folder I store the general deployment settings file called Set-DeploySettings.ps1 that includes all setup settings that for the deployment.

[code lang=”powershell”]$NAV_DvdLocation = ‘N:\NAV2013R2\NAVDVD’
$NAV_RemoteFolder = ‘C:\REMOTE\’
$NAV_AdminRemoteDirectory = Join-Path $NAV_RemoteFolder "NAVAdministration"
$NAV_NavDvdRemoteDirectory = Join-Path $NAV_RemoteFolder "NavDvd"
$NAV_CertRemoteDirectory = Join-Path $NAV_RemoteFolder "Cert"
$NAV_ServerAddins = ‘N:\NAV2013R2\Add-Ins\Server’
$NAV_ClientAddins = ‘N:\NAV2013R2\Add-Ins\Client’

$NAVAdminUserName = ‘NAVAdmin’
$NAVAdminPassword = ‘NAV.Admin.2013R2’

# Specifies language and regional settings for NAV Web Server
$WebServerLanguage = ‘is-IS’
$WebServerRegionFormat = ‘is-IS’

# Security Certificates for NAV Client Services
#
# Specifies the security certificate PFX file and password to use with Microsoft Dynamics NAV client services.
# For more information about certificates, see http://go.microsoft.com/fwlink/?LinkID=285869.
# A sample pfx file called MyAzureVM.pfx is available in the WindowsPowerShellScripts\Cloud\Examples\HowTo directory. The password is pfxpassword.
$ClientServicesPfxFile = ‘N:\NAV2013R2\Uppsetning\kappi-is.pfx’
$ClientServicesPfxPassword = ‘CertitificatePassword’
$DnsIdentity = ‘kappi.is’
$ServerDNSName = ‘www.kappi.is’
$applicationPublisher = "Kappi"

# Security Certificate to enable HTTPS for NAV Web Client
#
# Specifies the security certificate PFX file and password for configuring https protocol for web server authentication with the Microsoft Dynamics NAV Web Client.
# If you provide an empty value for the $NAV_HttpsWebClientPfxFile parameter, then the script will generate and install a self signed certificate remotely.#
# HTTPS helps secure the connection between clients and NAV web server
# For more information about certificates, see http://go.microsoft.com/fwlink/?LinkID=285869.
$HttpsWebClientPfxFile = ‘N:\NAV2013R2\Uppsetning\kappi-is.pfx’
$HttpsWebClientPfxPassword = ‘CertitificatePassword’

# Specifies the ClickOnce signing settings for the NAV Windows Client.
# If you sign the deployment, then the ClickOnce install page presented to end users will say that the publisher has been verified.
# If you don’t sign, then end users will get warning that the publisher cannot be verified.
# To sign ClickOnce, set $NAV_ClickOnceCodeSigningPfxFile to the path and name of the certificate pfx file, and then set $NAV_ClickOnceCodeSigningPfxPassword to the pfx password.
# A sample pfx file called ClickOnceSignature.pfx is available in the WindowsPowerShellScripts\Cloud\Examples\HowTo directory. The password is clickoncesignaturepassword.
# To not sign ClickOnce, set the values to $null.
$ClickOnceCodeSigningPfxFile = Join-Path $NAV_DvdLocation ‘WindowsPowerShellScripts\Cloud\HowTo\ClickOnceSignature.pfx’
$ClickOnceCodeSigningPfxPassword = ‘clickoncesignaturepassword’

# Tool dependencies

# The following tools must be installed on the provisioning computer:
# Azure.psd1 – Part of Windows Azure PowerShell, which can be downloaded from http://www.windowsazure.com/en-us/manage/downloads/ (select "install" under Windows)
# mage.exe – Part of the Microsoft Windows SDK for Windows 7 and .NET Framework 4, which can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=8279. This is included with Windows 8 and Windows Server 2012.
# winhttpcertcfg.exe – The Microsoft Windows HTTP Services (WinHTTP) Certificate Configuration Tool. Available at http://www.microsoft.com/en-us/download/details.aspx?id=19801
# makecert.exe – The Certificate Creation tool to generate x.509 certificates for testing purpose. This tool is available as part of the Windows SDK, which you can download from http://go.microsoft.com/fwlink/p/?linkid=84091.
# The following variables specify the path and file name of the tools.
$Global:MageExeFile = Join-Path ${env:ProgramFiles(x86)} ‘Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\mage.exe’
$Global:WinHttpCertCfgExeFile = Join-Path ${env:ProgramFiles(x86)} ‘Windows Resource Kits\Tools\winhttpcertcfg.exe’

# Windows PowerShell includes a set of variables that enable you to customize its behavior.
# Get help on all PowerShell preference parameters by issuing: Get-Help about_Preference_Variables
$verbosePreference = ‘Continue’
# To not display the verbose message and continue executing, use ‘SilentlyContinue’ (default PowerShell behavior)
$errorActionPreference = ‘Continue’
# To stop execution on first error, use ‘Stop’ (default value is ‘Continue’)
# To display the error message and ask you whether you want to continue, use ‘Inquire’
[/code]

Finally I have the script Install-NAV.ps1 in each computer subfolder that I execute with PowerShell ISE as Administrator.

[code lang=”powershell”]Set-StrictMode -Version 2.0

Import-Module ‘N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministrationSamples\NAVRemoteAdministrationSamples.psm1’ -DisableNameChecking
Import-Module ‘N:\NAV2013R2\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministration\Misc\Import-NAVAdministrationModuleRemotely.ps1’ -DisableNameChecking

# Import settings
$PSScriptRootV2 = Split-Path $MyInvocation.MyCommand.Definition -Parent
. (Join-Path $PSScriptRootV2 ‘..\Set-DeploySettings.ps1’)
. (Join-Path $PSScriptRootV2 ‘Set-MachineSettings.ps1’)

#New-NavAdminSession
[int]$currentMemoryLimitPerPSSessionInMB = Get-MaxMemoryPerShellRemotely -RemoteMachineAddress $NAV_RemoteMachineAddress
$requiredMemoryLimitPerSessionInMB = 1024
if(($currentMemoryLimitPerPSSessionInMB -ne 0) -and ($currentMemoryLimitPerPSSessionInMB -lt $requiredMemoryLimitPerSessionInMB))
{
Set-MaxMemoryPerShellRemotely -Value $requiredMemoryLimitPerSessionInMB -RemoteMachineAddress $NAV_RemoteMachineAddress
}

Write-Verbose "Creating remote PS session on $NAV_RemoteMachineAddress…"
$Session = New-PSSession -ComputerName $NAV_RemoteMachineAddress
Write-Verbose "Done creating remote PS session on $NAV_RemoteMachineAddress."

try
{
# Import the NAVAdministration module into the remote PS session
Write-Verbose (‘Copying the NAVAdministration module to ‘ + $Session.ComputerName + ‘ and importing it into the remote PS session…’)
$navAdministrationDirectory = Join-Path $NAV_dvdLocation ‘WindowsPowerShellScripts\Cloud\NAVAdministration’

Import-NAVAdministrationModuleRemotely
-LocalDirectory $navAdministrationDirectory

-RemoteDirectory $NAV_AdminRemoteDirectory
-Session $Session
Write-Verbose ('Done copying the NAVAdministration module to ' + $Session.ComputerName + ' and importing it into the remote PS session.')

# Copy the NAV DVD to the remote machine through the remote PowerShell session (slower but doesn't have a dependency on Azure Storage)
Write-Verbose ("Copying the NAV DVD to the remote machine at " + (Get-Date).ToLongTimeString() + "...")
#Copy-DirectoryToRemoteMachine -SourceDirectory $NAV_DvdLocation -RemoteDirectory $NAV_NavDvdRemoteDirectory -psSession $Session
Write-Verbose ("Done copying the NAV DVD to the remote machine at " + (Get-Date).ToLongTimeString() + ".")

# Install the SSL certificate that is used for HTTPS on the NAV Web Client
[System.Security.Cryptography.X509Certificates.X509Certificate2]$Certificate = $null

Write-Verbose "Importing certificate for https authentication on remote machine..."

[System.Security.SecureString]$HttpsWebClientPfxPasswordAsSecureString = ConvertTo-SecureString $HttpsWebClientPfxPassword -AsPlainText -Force

$Certificate = Import-PfxFileRemotely
-PfxFile $HttpsWebClientPfxFile
-PfxPassword $HttpsWebClientPfxPasswordAsSecureString

-Session $Session

# Get installed certificate thumbprint
$WebServerSSLCertificateThumbprint = Get-CertificateThumbprint -Certificate $Certificate

# Prepare NAV Installer Configuration file for NAV Web Site SSL Binding specific to current deployment
$TemplateSetupConfigFile = (Join-Path $PSScriptRootV2 ‘NAVBoxConfigFile.xml’)
$SetupConfigFile = Prepare-NAVInstallerConfigFileForWebSiteSSLBinding -TemplateSetupConfigFile $TemplateSetupConfigFile -WebServerSSLCertificateThumbprint $WebServerSSLCertificateThumbprint

# Run the NAV Installation with a 1 Box setup
Install-NAV -RemoteNavDvdLocation $NAV_NavDvdRemoteDirectory -SetupConfigFile $SetupConfigFile -PSSession $Session

#Upload Files to Remote Machine

# Copy the Add-ins to the remote machine through the remote PowerShell session (slower but doesn’t have a dependency on Azure Storage)
Write-Verbose ("Copying the Add-ins to the remote machine at " + (Get-Date).ToLongTimeString() + "…")
Copy-DirectoryToRemoteMachine -SourceDirectory $NAV_ServerAddins -RemoteDirectory ‘C:\Program Files\Microsoft Dynamics NAV\71\Service\Add-ins’ -psSession $Session
Copy-DirectoryToRemoteMachine -SourceDirectory $NAV_ClientAddins -RemoteDirectory ‘C:\Program Files (x86)\Microsoft Dynamics NAV\71\RoleTailored Client\Add-ins’ -psSession $Session
Write-Verbose ("Done copying the Add-ins to the remote machine at " + (Get-Date).ToLongTimeString() + ".")

# If your application requires that you install server-side add-ins or copy additional files to the virtual machine,
# then you can customize the scripts to accomplish that. This sample code copies a single file to the VM.
# Update the code with your file(s) and uncomment the line.
# Copy-FileToRemoteMachine -SourceFile ‘C:\MyAddin.dll’ -DestinationFile ‘C:\Program Files\Microsoft Dynamics NAV\71\Service\Add-ins\MyAddin\MyAddin.dll’ -Session $psSession

}
finally {
Remove-NAVAdminSession -Session $Session
}
[/code]

The machine is now ready for the next step, to install NAV Server Instances, Tenants, ClickOnce and Web Sites. Before I will show that the next post will be about creating the databases with scripts.

Delete out-of-license table data

In an email exchange between a few NAV developers, we where discussing how to delete or modify data from read-only tables or tables that are not included in the customer license.

In the Classic Client the consultant was able to change to a partner license to make data changes.  Since NAV 2013 tables are opened through the server instance and the only way to have tables opened with the partner license is to upload the partner license to the database and restart the server instance.  That is not a best practice method but a possible way if you have a dedicated developement server instance where no user is connected.

If that is the case you can use PowerShell to upload the license so it will only work on this service instance.

[code lang=”powershell”]Import-Module ‘C:\Program Files\Microsoft Dynamics NAV\71\Service\NavAdminTool.ps1’
Import-NAVServerLicense -ServerInstance DynamicsNAV71 -LicenseFile MyLicenseFile.flf[/code]

If a user server instance is restarted the customer will be running the partner license and that is a dangerous thing. Make sure that you have the correct customer license at hand before doing this and make sure that you upload that license back with the same PowerShell commands as soon as your data changes have been done.

Another way to solve this is with programming.  You either create a Page, Report or a Codeunit to modify the data.  If the field is read only a Report or a Codeunit is needed to modify the data with code.  Other fields can be edited with a Page.  To enable modification with the customer license the object needs to have special permissions.

ObjectPermission

Then one of the group mentioned another problem.  If a customer is started with the CRONUS data then you will have data in tables that are not licensed.  In that case you can’t change this with the customer license.  Here the first method of change to a partner license is available but I suggest another method.

DeleteOutOfLicenseData

Here is a Report that will loop through Table Information.  Running on the customer license it will find all tables that are out of license containing data and give you a SQL script to execute that will delete all data from these tables.

[code lang=”sql”]USE [CRONUS International Ltd]
GO
DELETE FROM dbo.[CRONUS International Ltd$Payroll Cue]
GO
DELETE FROM dbo.[CRONUS International Ltd$Banking Card Process Setup]
GO
DELETE FROM dbo.[CRONUS International Ltd$Banking Card Type]
GO
DELETE FROM dbo.[CRONUS International Ltd$Banking Collection Agent]
GO
[/code]

I hope this will answer these questions and give you a simpler way to start a new company with existing data, or just check if you have any data that is not included in the customer license.

The Report – Create SQL Delete Script – is attached.  This version is for NAV 2013 R2 but the text version should also work in NAV 2013.

DeleteScriptNAV2013R2

Setting up our NAV 2013 R2 Developement with PowerShell

I just finished my install on databases and service to our developement server.  When creating several databases and several instances I wanted to use PowerShell.

I based the setup on the demo database backup and used PowerShell ISE as an Administrator to do the setup.

First step is to set up parameters.

#Change Id to match the solution type
$Id = ‘GL’
#Change ClientPort to available port
$ClientPort = 9213

Then I run the script.

[code]#Change Id to match the solution type
$Id = ‘GL’
#Change ClientPort to available port
$ClientPort = 9213
#Database Server Name
$servername = ‘localhost’
#Location of Demo Database Backup File
$backupFile = ‘T:\Demo Database NAV (7-1).bak’
#New locations for Database Files
$DataOrigName = ‘Demo Database NAV (7-1)_Data’
$DataDestName = ‘M:\SQLdata\mdf\NAV71_’ + $Id + ‘.mdf’
$LogOrigName = ‘Demo Database NAV (7-1)_Log’
$LogDestName = ‘L:\SQLdata\ldf\NAV71_’ + $Id + ‘.ldf’
#New Database Name
$DatabaseName = ‘NAV71_’ + $Id
#New Service Instance Name
$ServiceInstanceName = ‘NAV71_’ + $Id
#New Company Name
$NewCompanyName = ‘CRONUS ‘ + $Id + ‘ 2013 R2’
#Other Ports to use
$SoapPort = $ClientPort + 1
$ODataPort = $SoapPort + 1
$MgtPort = $ODataPort + 1
#Database and Table with the Developement License
$CopyLicenseFromDB = ‘NAV71_AdvaniaStarter’
$tblName = ‘$ndo$dbproperty’

#Load the DLLs if not using SQLPS
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SmoExtended’) | out-null

#load server, backup, and restore objects
$server = new-object("Microsoft.SqlServer.Management.Smo.Server") $servername
$dbRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")

#settings for the restore
$dbRestore.Action = "Database"
$dbRestore.NoRecovery = $false;
$dbRestore.ReplaceDatabase = $false;
$dbRestorePercentCompleteNotification = 5;
$dbRestore.Devices.AddDevice($backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

#get the db name
$dbRestoreDetails = $dbRestore.ReadBackupHeader($server)

#print database name
"Database Name from Backup File : " + $dbRestoreDetails.Rows[0]["DatabaseName"]

#give a new database name
$dbRestore.Database = $DatabaseName

#specify new data and log files (mdf and ldf)
$dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")

#set file names; use the default database directory
$dbRestoreFile.LogicalFileName = $DataOrigName
$dbRestoreFile.PhysicalFileName = $DataDestName
$dbRestoreLog.LogicalFileName = $LogOrigName
$dbRestoreLog.PhysicalFileName = $LogDestName
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)

#execute the restore!
$dbRestore.SqlRestore($server)
write-host "Restore of " $DatabaseName "Complete"

$database = $server.Databases[$DatabaseName]
# grant access to database
$name = ‘NT AUTHORITY\NETWORK SERVICE’
$user = new-object (‘Microsoft.SqlServer.Management.Smo.User’) $database, $name
$user.Login = ‘NT AUTHORITY\NETWORK SERVICE’
$user.DefaultSchema = ‘dbo’
$user.Create()

# add role membership
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$servername;Integrated Security=SSPI;Initial Catalog=$DatabaseName");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N’db_owner’, @membername = N’NT AUTHORITY\NETWORK SERVICE’"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()

# Copy License
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$servername;Integrated Security=SSPI;Initial Catalog=master");
$cn.Open()
$q = "UPDATE [$DatabaseName].[dbo].[$tblName] SET [license] = (SELECT [license] FROM [$CopyLicenseFromDB].[dbo].[$tblName])"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()

New-NAVServerInstance -ServerInstance $ServiceInstanceName -ClientServicesCredentialType Windows -ClientServicesPort $ClientPort -DatabaseName $DatabaseName -DatabaseServer Localhost -ManagementServicesPort $MgtPort -SOAPServicesPort $SoapPort -ODataServicesPort $ODataPort
Set-NAVServerInstance -ServerInstance $ServiceInstanceName -Start
Rename-NAVCompany -ServerInstance $ServiceInstanceName -CompanyName ‘CRONUS Ísland hf.’ -NewCompanyName $NewCompanyName
Get-NAVCompany -ServerInstance $ServiceInstanceName

# give developers SUPER access to the database
$grpNames = (‘sec_Dynamics_NAV_Dev1’, ‘sec_Dynamics_NAV_Dev2′)
foreach ($grp in $grpNames)
{
Get-ADGroupMember -Identity $grp -Server "172.16.1.2" | foreach {
New-NAVServerUser -ServerInstance $ServiceInstanceName -Sid $($_.SID) -FullName $($_.name) -LicenseType Full -State Enabled
New-NAVServerUserPermissionSet -ServerInstance $ServiceInstanceName -Sid $($_.SID) -PermissionSetId "SUPER"
}

# add role membership
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$servername;Integrated Security=SSPI;Initial Catalog=$DatabaseName");
$cn.Open()
$q = "EXEC sp_addrolemember @rolename = N’db_owner’, @membername = N’" + "SKYRR\" + $grp + "’"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$cmd.ExecuteNonQuery() | out-null
$cn.Close()
}

[/code]

First step is to initialize all variables.

  • Set variable for the location of the Demo Database Backup File
  • Set variables for new location of Database Files
  • Set variable for new Database Name
  • Set variable for new Service Instance Name
  • Set variable for new Company Name

Next step is to restore the database.

  • Setup SMO environment
  • Setup the restore
  • Get current Database Name
  • Set new name for the Database
  • Set new locations for the Database Files
  • Execute Restore

The user running the NAV Server Instance will need to have access to the new database.

  • Create new user for  ‘NT AUTHORITY\NETWORK SERVICE’
  • Adds role ‘do_owner’ to user ‘NT AUTHORITY\NETWORK SERVICE’

Before starting the NAV Server Instance I will copy the developement license to the new database

  • Setup SQL connection to the master database
  • Prepare SQL statement to copy the license from another database
  • Execute SQL statement

New NAV Server Instance is created and started.  The existing company is renamed to the new company.

Use Active Directory to find all users in the developement group and add them as NAV users and give them SUPER permission.  The groups are also given ‘db_owner’ permission for the database to be able to run Developement Environment.

If the Active Directory PowerShell commands are not installed just activate that feature or look here for further details.

AddADPowerShell