Month: December 2013

Posted on

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.

Posted on

Could not load type “. error in DotNet object

As often a solution to a strange error is so simple when you know it.  When I copied my add-ins to a  new computer I was not able compile the objects that included DotNet add-ins.

CouldNotLoadType

As this happened when I was installing NAV 2013 R2 for the first time my first thought was that this was something that had changed between versions and I sent a support request to Microsoft.

Mohamad Vajid and Duilio Tacconi took the case and tried to reproduce the problem without success.  Yesterday we took a look at the problem together and the solution came to our attention.

The Add-in was copied from a web site and because of that the file was blocked by the server operating system.

UnblockDLL

Then today as I was creating a new website with files that I downloaded from the web I had the same problem.  That will be the last time.

After you unblock the file, recheck to see if that worked.  If the file is in a protected folder you might need to drag it to your desktop to unblock it and back again.

Thank you Mohamad and Duilio for the assistance.

Posted on

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.

Posted on

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

Posted on

Backup your NAV Database to Microsoft Azure

I am not sure that you will find safer place to place your SQL backups than on a Microsoft Azure Storage.  Azure keeps three copies of all the data within the data center and if you enable Geo-Replication another three copies will be kept in another data center somewhere else in the world.

Running the NAV SQL Server on a virtual machine within Azure also means that the SQL database is also in six places, just to make sure.

I moved my NAV services to Azure and wanted to make sure that the backup would be as good as everything else.  To backup directly from SQL to Azure Storage you will need SQL Server 2012.  There are most likely other third-party solutions to move you backups to Azure.  There even is a program from Microsoft – SQL Server Backup to Windows Azure Tool – that will pick up files a local folder(s) and upload them to the Azure Storage.  I am using this tool to upload backup from my MySql server.

If you have SQL Server 2012, on Azure or on premise you can backup directly to your storage account.  You will need Cumulative update package 2 for SQL Server 2012 to update your installation to the required version to get this feature supported.

How it works is being shared in many places, for example on SQLServerCentral.com.  I followed these instructions and created a storage account in USA.  My SQL server is running in North Europe and I wanted to make sure that the backups where on another continent.

I created the credentials in my master database and then started to test the backup.  After a succesful backup I started to think about automating this process.  As usual a dynamic script was needed, I did not want to have to manually do anything.  Even if I removed or added a database.  This is the script

[code lang=”sql”]declare @yea varchar(10)
declare @wk varchar(10)
declare @dw varchar(10)
declare @hh varchar(10)
declare @url varchar(250)
SET @yea = (SELECT datepart(yy,Getdate()));
SET @wk = (SELECT datepart(wk,GetDate()));
SET @dw = (SELECT datepart(dw,GetDate()));
SET @hh = (SELECT datepart(hh,GetDate()));

DECLARE @TableName VARCHAR(256)
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(max)
DECLARE @getDBName CURSOR
SET @TableName = ‘$ndo$dbproperty’
SET @getDBName = CURSOR FOR SELECT name FROM sys.databases
CREATE TABLE #TmpTable (DBName VARCHAR(256), SchemaName VARCHAR(256), TableName VARCHAR(256))
OPEN @getDBName
FETCH NEXT FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = ‘USE [‘ + @DBName + ‘];
INSERT INTO #TmpTable SELECT ”’+ @DBName + ”’ AS DBName, SCHEMA_NAME(schema_id) AS SchemaName, name AS TableName FROM sys.tables
WHERE name LIKE ”%’ + ltrim(rtrim(@TableName)) + ‘%”’
EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName

DECLARE @DBName_Cursor CURSOR
SET @DBName_Cursor = CURSOR FOR SELECT DBName FROM #TmpTable;
OPEN @DBName_Cursor
FETCH NEXT FROM @DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
IF @dw = 1 AND @hh < 6
BEGIN
SET @url = ‘https://kappisqlbackup.blob.core.windows.net/sqlbackup/’ + REPLACE(@dbname,’ ‘,’_’) + ‘_’ + @yea + ‘_’ + @wk + ‘.bak’
BACKUP DATABASE @dbname TO
URL=@url
WITH CREDENTIAL=’kappiAzureCredential’, STATS = 10, FORMAT, COMPRESSION
END
SET @url = ‘https://kappisqlbackup.blob.core.windows.net/sqlbackup/’ + REPLACE(@dbname,’ ‘,’_’) + ‘_’ + @yea + ‘_’ + @wk + ‘_’ + @dw + ‘_’ + @hh + ‘.trn’
BACKUP LOG @dbname TO
URL=@url
WITH CREDENTIAL=’kappiAzureCredential’, STATS = 10, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION
FETCH NEXT FROM @DBName_Cursor INTO @dbname
END;
CLOSE @DBName_Cursor;
DEALLOCATE @DBName_Cursor;
DROP TABLE #TmpTable
GO[/code]

You can see that my company name, Kappi, is in the script. If you want to use this script you need to update the storage URL and the credential name.

What happens is that all databases are read and within them the script looks for the table ‘$ndo$dbproperty’.  If this table exists then I add this database name to a temporary table.  I then loop through the temporary table and start a backup for each database.

My plan is to do a full backup once a week and transaction backup every six hours.  The line ‘IF @dw= 1 AND @hh < 6′ triggers a full backup on Sundays before six in the morning.  Then I created a job that executes every six hours to start the backup.

[code lang=”sql”]USE [msdb]
GO

/****** Object: Job [Kappi_Backup] Script Date: 7.12.2013 14:16:04 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 7.12.2013 14:16:04 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Kappi_Backup’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’Database Backup’,
@category_name=N’Database Maintenance’,
@owner_login_name=N’ONAZURE\kappi’,
@notify_email_operator_name=N’Gunnar Þór Gestsson’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Start Backup] Script Date: 7.12.2013 14:16:05 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Start Backup’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’declare @yea varchar(10)
declare @wk varchar(10)
declare @dw varchar(10)
declare @hh varchar(10)
declare @url varchar(250)
SET @yea = (SELECT datepart(yy,Getdate()));
SET @wk = (SELECT datepart(wk,GetDate()));
SET @dw = (SELECT datepart(dw,GetDate()));
SET @hh = (SELECT datepart(hh,GetDate()));

DECLARE @TableName VARCHAR(256)
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(max)
DECLARE @getDBName CURSOR
SET @TableName = ”$ndo$dbproperty”
SET @getDBName = CURSOR FOR SELECT name FROM sys.databases
CREATE TABLE #TmpTable (DBName VARCHAR(256), SchemaName VARCHAR(256), TableName VARCHAR(256))
OPEN @getDBName
FETCH NEXT FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = ”USE [” + @DBName + ”];
INSERT INTO #TmpTable SELECT ”””+ @DBName + ””” AS DBName, SCHEMA_NAME(schema_id) AS SchemaName, name AS TableName FROM sys.tables
WHERE name LIKE ””%” + ltrim(rtrim(@TableName)) + ”%”””
EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName

DECLARE @DBName_Cursor CURSOR
SET @DBName_Cursor = CURSOR FOR SELECT DBName FROM #TmpTable;
OPEN @DBName_Cursor
FETCH NEXT FROM @DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
IF @dw = 1 AND @hh < 6
BEGIN
SET @url = ”https://kappisqlbackup.blob.core.windows.net/sqlbackup/” + REPLACE(@dbname,” ”,”_”) + ”_” + @yea + ”_” + @wk + ”.bak”
BACKUP DATABASE @dbname TO
URL=@url
WITH CREDENTIAL=”kappiAzureCredential”, STATS = 10, FORMAT, COMPRESSION
END
SET @url = ”https://kappisqlbackup.blob.core.windows.net/sqlbackup/” + REPLACE(@dbname,” ”,”_”) + ”_” + @yea + ”_” + @wk + ”_” + @dw + ”_” + @hh + ”.trn”
BACKUP LOG @dbname TO
URL=@url
WITH CREDENTIAL=”kappiAzureCredential”, STATS = 10, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION
FETCH NEXT FROM @DBName_Cursor INTO @dbname
END;
CLOSE @DBName_Cursor;
DEALLOCATE @DBName_Cursor;
DROP TABLE #TmpTable
GO
‘,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Every Six Hours’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=6,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20131205,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N’dce606ac-0803-43a2-a98a-c8ffedb1df09′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

[/code]

Posted on

Dynamics.is is running on Microsoft Azure

Since I came home from Seattle MVP Summit I have been testing the Azure hosting environment.  I wanted an environment to host my web sites and my Dynamics NAV.

AzureSubscription

I created three virtual machines with two cloud services.  One computer is a domain controller and the other two have identical setup with Web server, MySql server and MSSql server.

The first thing I needed to do was to plan my network.  If I use the default setup then the virtual machines are not guaranteed to always have the same internal IP addresses.  I am using active directory domain controller (why I will tell you later) that will act as a DNS server for all the computers.

In my Virtual Network I created a separate subnet that will host only the domain controller.  The domain controller will always be given the first available IP address on that subnet and that way I can add a DNS server to my virtual network.

I created a separate cloud service for my domain controller computer and I am using it as a management computer for the others.

For the two web computers I used the Web Platform Installer to install PHP and MySql.  I also installed phpMyAdmin to manage the databases.  I added a master/slave relationship between the MySql servers so that every change on each of them is replicated to the other one.

I installed MSSql server 2012 on all three computers.  I wanted to be able to mirror my databases and I use the domain controller MSSql server as a witness to the mirroring.  The mirroring setup in MSSql is done for each database and I always have a principal database and a mirror database.  When the principal database in inaccessible the roles are shifted and the mirror becomes the principal.  I am hoping that Dynamics NAV will support this failover functionality soon as this is built into the MSSql native client.

I added an extra hard drive for file data and another extra hard drive for database data to the web computers.  When there is a domain controller you will have the option to use DFS Replication that can automatically synchronize folders between computers.  I use this functionality to replicate my web folders between the web computers.

I used phpMyAdmin to export the databases on my old host and copied that file and the web folders to my new server.  I created the websites and imported my databases.  At first I had PHP errors and needed to go into Handler Mappings and delete all references to PHP and then I used PHP Manager to create a new mapping.

Finally I turned to Dynamics NAV.  Since I am working with Advania to set up a Dynamics NAV hosting environment I wanted to make sure the all the installation should work through PowerShell.  NAVTechDays 2013 had two sessions where Microsoft employees showed that by using PowerShell they where able to install NAV on Azure and on premise.

I tried out the Example-1VM script that is provided with the NAV DVD and then started to break things down into smaller parts that would work in my private cloud.  Currently I have several instances of NAV up and running and am planning to share the PowerShell scripts in another post later.

The most suprising thing is that all three machines are small machines (1 core, 1.75GB memory) and still the performance is acceptable.