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.