Move single-tenant NAV 2016 to Azure SQL and multitenancy

CustomFunctions.psm1

function Update-AzureSqlPricingTier {
    
    [CmdletBinding()]
    param (
    
        # Credential
        [parameter(Mandatory=$true,Position=1)]
        [pscredential]$Credential,
        
        # ServerName
        [parameter(Mandatory=$true,Position=2)]
        [string]$ServerName,

        #ResourceGroupName
        [parameter(Mandatory=$true,Position=3)]
        [string]$ResourceGroupName,

        # DatabaseName
        [parameter(Mandatory=$true,Position=4)]
        [string]$DatabaseName,
        
        #PricingTier
        [Parameter(Mandatory=$true,Position=5)]
        [Alias('RequestedServiceObjectiveName')]
        [string]$PricingTier,
        
        #Edition
        [Parameter(Mandatory=$true,Position=6)]
        [ValidateSet('Basic','Standard','Premium','Free','DataWarehouse','None')]
        [string]$Edition
    )

    begin {
        Write-Verbose "Importing module AzureRM.Sql"
        Import-module AzureRM.Sql
        Write-Verbose "Logging on to Azure Resource Manager account"
        $AzureRmLogin = Login-AzureRmAccount -Credential $Credential
        Write-Verbose "Getting and selecting Azure subscription"
        $AzureRmSubscription = Get-AzureRmSubscription -WarningAction SilentlyContinue | Select-AzureRmSubscription
    }
    
    process {
        Write-Host -ForegroundColor Yellow "Changing pricing tier for database $DatabaseName to $PricingTier"
        $result = Set-AzureRmSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $ResourceGroupName -RequestedServiceObjectiveName $PricingTier -Edition $Edition
        Write-Host -ForegroundColor Yellow "Pricing tier updated"
    }

}

MoveNAVToAzureSQL.ps1

#region Variables you need to change

# On-premise SQL server details
$LocalSqlServer = 'THE_NAME_OF_YOUR_SQL_SERVER'
$LocalSqlInstance = 'SQL_INSTANCE' # Use MSSQLSERVER if you don't have any other named instance.
#$LocalSqlCredentials = '' # Not used. Script uses your current windows credentials

# Azure SQL details
$AzureSqlServer = 'YOUR_AZURE_SQL_SERVER.database.windows.net'
$AzureSqlServerShort = 'YOUR_AZURE_SQL_SERVER'
$AzureSqlUserName = 'ADMIN_USER_NAME_ON_AZURE_SQL_SERVER'
$AzureSqlPassword = 'ADMIN_PASSWORD'

# Database names
$SourceDatabaseName = 'THE_NAME_OF_YOUR_SINGLE-INSTANCE_DATABASE'
$ApplicationDatabaseName = 'NAME_OF_TEMPORARY_APP_DATABASE_ON_ON-PREMISE_SQL'
$ApplicationDatabaseNameOnAzureSql = 'NAME_OF_APP_DATABASE_ON_AZURE_SQL'

# Azure Storage Account (find or create this on https://portal.azure.com, look for Storage accounts (classic)
$StorageAccountName="AZURE_STORAGE_ACCOUNT_NAME"
$StorageAccountKey = {STORAGE_ACCOUNT_KEY}
# Name your container. It's just like a folder name in the Azure blob storage. Ít will be created automatically if it does not exist.
$ContainerName = "backupcontainer"

#Variables for remote NAV-services server
$NAVVMServer = 'NAV_SERVER_ADDRESS' # example: yourserver.cloudapp.net
$NAVVMPSPort = '5986' # Powershell port number. Default is 5986.
$NAVVMUserName = 'WINDOWS_ACCOUNT_WITH_ADMIN_PRIVILEDGES'
$NAVVMPassword = 'YOUR_PASSWORD'

# Name of the created NAV instance that you want to reconfigure
$NAVServerInstance = 'NAV_SERVER_INSTANCE' # Default is DynamicsNAV90

# This NAV database user is created in all mounted tenants
$NAVDatabaseUserName = 'navadmin' # The user you want to create in every database
$NAVDatabaseUserPassword = 'CHANGE_M3_SOON!'
$NAVUserFullName = 'My administrator account'

# File paths
$PathToPSScript = 'C:\MyPowerShellScripts'
$NAVBackupFilePath = 'c:\temp'
$NAVEncryptionKeyPath = 'c:\temp'

# Azure portal credentials (used when creating new Azure SQL databases etc)
$AzureRmUser = 'YOUR_AZURE_PORTAL_CREDENTIALS' # yourname@company.com
$AzureRmPassword = 'AND_YOUR_PASSWORD'
$AzureSqlResourceGroupName = 'THE_RESOURCE_GROUP_YOUR_AZURE_SQL_SERVER_BELONGS_TO'

#Azure SQL settings
# The databases are first created with the $TempSqlEdition and $TempSqlPricingTier to maximize data import speed. Adjust to your needs.
$TempSqlEdition = "Standard"
$TempSqlPricingTier = "S3"
# Edition and pricing tier are change to $SqlEdition and $SqlPricingTier after data import.
$SqlEdition = "Standard"
$SqlPricingTier = "S0"

#endregion

#region Automatically generated variables
$NAVEncryptionKey = -join(0..15|%{[char][int]((65..90) + (97..122) + (48..57) + (35,36,42,43,44,45,46,47,58,59,61,63,64,91,92,93,95,123,125,126) | Get-Random)})
$AzureSQLCredential = (New-Object PSCredential -ArgumentList $AzureSqlUserName,(ConvertTo-SecureString -AsPlainText -Force $AzureSqlPassword))
#$AzureSQLCredential = Get-Credential # Uncomment this line if you prefer to type your credentials
$AzureRmCredential = (New-Object PSCredential -ArgumentList $AzureRmUser,(ConvertTo-SecureString -AsPlainText -Force $AzureRmPassword))
#$AzureRmCredential = Get-Credential # Uncomment this line if you prefer to type your credentials
$NAVVMCredential = (New-Object PSCredential -ArgumentList $NAVVMUserName,(ConvertTo-SecureString -AsPlainText -Force $NAVVMPassword))
#$NAVVMCredential = Get-Credential # Uncomment this line if you prefer to type your credentials

$LocalSqlServerAndInstance = $LocalSqlServer+'\'+$LocalSqlInstance
#endregion

Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Script started"

#region Azure logon
# Log on with your Azure account (Requires Azure Powershell cmdlets (https://azure.microsoft.com/da-dk/documentation/articles/powershell-install-configure/))
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Log on to Azure account"
$AzureAccount = Add-AzureAccount -Credential $AzureRmCredential

#endregion

#region Import modules
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Importing modules"
Import-Module "C:\Program Files\Microsoft Dynamics NAV\90\Service\NavAdminTool.ps1"	 -WarningAction SilentlyContinue
# Import the SQL Server Module. (Requires SQL Powershell module installed)
Import-Module “sqlps” -DisableNameChecking -WarningAction SilentlyContinue
Import-Module $PathToPSScript\CustomFunctions.psm1
Import-module AzureRM.Sql -WarningAction SilentlyContinue

#endregion

#region Export NAV company data (all companies)

Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Exporting NAV company data (all companies)"
$NavExport = Export-NAVData -DatabaseServer $LocalSqlServerAndInstance -DatabaseName $SourceDatabaseName -AllCompanies -FilePath $NAVBackupFilePath'\'$NAVServerInstance'.navdata' -Force

#endregion

#region Export NAV company data (single company)

#Export-navdata -DatabaseServer $LocalSqlServerAndInstance -DatabaseName $SourceDatabaseName -CompanyName 'COMPANY_NAME' -FilePath $NAVBackupFilePath'\company_data.navdata'

#endregion

#region Export the NAV application data into new database
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Exporting NAV application into new database"$ApplicationDatabaseName
$ExportNavApp = Export-NAVApplication -DatabaseName $SourceDatabaseName -DestinationDatabaseName $ApplicationDatabaseName -DatabaseServer $LocalSqlServer -DatabaseInstance $LocalSqlInstance -Force

#endregion

#region Get companies from backup file (requires run as administrator)
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Get company names from $NAVBackupFilePath\$NAVServerInstance.navdata"
$NAVCompanyList = Get-NAVCompany -FilePath $NAVBackupFilePath'\'$NAVServerInstance'.navdata'
#endregion

#region Delete user 'NT AUTHORITY\NETWORK SERVICE' from APP database
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Deleting user [NT AUTHORITY\NETWORK SERVICE] from database"
Invoke-Sqlcmd -ServerInstance $LocalSqlServerAndInstance -Database $ApplicationDatabaseName -Query "DROP USER [NT AUTHORITY\NETWORK SERVICE]"
#endregion

#region Create BACPAC file
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Creating bacpac file"

# Load DAC DLL (requires config file to support .NET 4.0)

$LocalSqlSession = New-PSSession -ComputerName $LocalSqlServer
Invoke-Command -Session $LocalSqlSession -ScriptBlock {
    param($LocalSqlServerAndInstance,$ApplicationDatabaseName)
    #Test-Path "C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin"
    Add-Type -path "C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.dll"
    # make DacServices object, needs a connection string
    $d = New-Object Microsoft.SqlServer.Dac.DacServices "server=$LocalSqlServerAndInstance" 
    # Export schema and data from database
    $ExportBacpac = $d.exportbacpac("c:\pshtemp\$ApplicationDatabaseName.bacpac", "$ApplicationDatabaseName") 
} -ArgumentList $LocalSqlServerAndInstance,$ApplicationDatabaseName

#endregion

#region Move files from SQL to local folder
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Copy bacpac file to local folder"
$Source = "\\" + $LocalSqlServer + "\c$\pshtemp"
$SourceFileName = $ApplicationDatabaseName + ".bacpac"

New-PSDrive -Name source -PSProvider FileSystem -Root $Source | Out-Null
Copy-Item -Path source:\$SourceFileName -Destination $NAVBackupFilePath
Remove-PSDrive source

#endregion

#region Copy file to your Azure Blob Storage
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Copy file to Azure blob storage"
# Requires Azure Powershell cmdlets (https://azure.microsoft.com/da-dk/documentation/articles/powershell-install-configure/)

# Filepath on your local computer.
$FileToUpload = $NAVBackupFilePath + "\" + $SourceFileName

# Get Subscription
$AzureSubscription = Get-AzureSubscription

# Set a default Azure subscription.
Select-AzureSubscription -SubscriptionName $AzureSubscription.SubscriptionName

# Set storage account.
$AzureStorageContext = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey

# Create a new container.
New-AzureStorageContainer -Name $ContainerName -Permission Off -Context $AzureStorageContext -ErrorAction SilentlyContinue | Out-Null

# Upload bacpac file
$Void = Set-AzureStorageBlobContent -Container $ContainerName -File $FileToUpload -Context $AzureStorageContext -Force

# List all blobs in a container.
#Get-AzureStorageBlob -Container $ContainerName -Context $AzureStorageContext

#endregion

#region Restore BACPAC to Azure SQL
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Restore bacpac on Azure SQL"
$SqlCtx = New-AzureSqlDatabaseServerContext -ServerName $AzureSqlServerShort -Credential $AzureSQLCredential
$StorageCtx = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey
$Container = Get-AzureStorageContainer -Name $ContainerName -Context $StorageCtx
$ImportRequest = Start-AzureSqlDatabaseImport -SqlConnectionContext $SqlCtx -StorageContainer $Container -DatabaseName $ApplicationDatabaseNameOnAzureSql -BlobName $SourceFileName

#Get import status
#Get-AzureSqlDatabaseImportExportStatus -RequestId $ImportRequest -Username $AzureSQLCredential.UserName -ServerName $AzureSqlServerShort -Password $AzureSqlPassword

#endregion

#region  Configure NAV-service for multi tenancy on remote VM (NAV-service must be pre-installed)
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Configure NAV service for multi tenancy"
$sessionOption = New-PSSessionOption -SkipCNCheck
$NAVVMServerSession = New-PSSession -ComputerName $NAVVMServer -Port $NAVVMPSPort -Credential $NAVVMCredential -UseSSL -SessionOption $sessionOption

$scriptBlock = {
               param($NAVServerInstance,$ApplicationDatabaseNameOnAzureSql,$AzureSQLCredential)
               Import-Module "C:\Program Files\Microsoft Dynamics NAV\90\Service\NavAdminTool.ps1" | Out-Null
               Set-NAVServerInstance $NAVServerInstance -Stop
               Set-NAVServerConfiguration -ServerInstance $NAVServerInstance -Keyname Multitenant -KeyValue "True" -WarningAction SilentlyContinue
               Set-NAVServerConfiguration -ServerInstance $NAVServerInstance -Keyname ClientServicesCredentialType -KeyValue "NavUserPassword" -WarningAction SilentlyContinue
               Set-NAVServerConfiguration -ServerInstance $NAVServerInstance -Keyname DatabaseName -KeyValue $ApplicationDatabaseNameOnAzureSql -WarningAction SilentlyContinue
               Set-NAVServerConfiguration -ServerInstance $NAVServerInstance -Keyname EnableSqlConnectionEncryption -KeyValue "True" -WarningAction SilentlyContinue
               Set-NAVServerConfiguration -ServerInstance $NAVServerInstance -Keyname TrustSQLServerCertificate -KeyValue "False" -WarningAction SilentlyContinue
               Set-NAVServerConfiguration -DatabaseCredentials $AzureSQLCredential -ServerInstance $NAVServerInstance -Force -WarningAction SilentlyContinue
               Set-NAVServerConfiguration $NAVServerInstance -KeyName DatabaseServer -KeyValue $AzureSqlServer -Force -WarningAction SilentlyContinue
               Set-NAVServerInstance $NAVServerInstance -Restart

               }

$result = Invoke-Command -Session $NAVVMServerSession -ScriptBlock $scriptBlock -ArgumentList $NAVServerInstance,$ApplicationDatabaseNameOnAzureSql,$AzureSQLCredential

#endregion

#region import NAV encryption key
Start-Sleep -s 120 # Had problems importing the key, even when sleeping 60 secs.
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Importing NAV encryption key"
$sbImportEncryptionKey = {
                         param($NAVServerInstance,$AzureSqlServer,$AzureSQLCredential,$ApplicationDatabaseNameOnAzureSql,$NAVEncryptionKeyPath,$NAVEncryptionKey)
                         $KeyFullPath = $NAVEncryptionKeyPath+"\"+$ApplicationDatabaseNameOnAzureSql+".key"
                         $KeyFullPath = $KeyFullPath -replace '\s','_'
                         New-NAVEncryptionkey -KeyPath $KeyFullPath -Password (ConvertTo-SecureString -AsPlainText -Force $NAVEncryptionKey)
                         Import-NAVEncryptionKey -ServerInstance $NAVServerInstance `
                        -ApplicationDatabaseServer $AzureSqlServer `
                        -ApplicationDatabaseCredentials $AzureSQLCredential `
                        -ApplicationDatabaseName $ApplicationDatabaseNameOnAzureSql `
                        -KeyPath $KeyFullPath `
                        -Password (ConvertTo-SecureString -AsPlainText -Force $NAVEncryptionKey) `
                        -Force
                        }

Invoke-Command -Session $NAVVMServerSession -ScriptBlock $sbImportEncryptionKey -ArgumentList $NAVServerInstance,$AzureSqlServer,$AzureSQLCredential,$ApplicationDatabaseNameOnAzureSql,$NAVEncryptionKeyPath,$NAVEncryptionKey

#endregion

#region create databases and mount tenants
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Starting loop to create tenant databases, import data and mount tenants"
#Login to your Azure account (This might be blocked by your internet security settings - I had to add 3 websites to the Trusted sites zone in my Internet Explorer)
Login-AzureRmAccount -Credential $AzureRmCredential | Out-Null
Get-AzureRmSubscription -WarningAction SilentlyContinue | Select-AzureRmSubscription -WarningAction SilentlyContinue | Out-Null

#Loop though all selected companies, create databases, import data and mount tenants
$NAVCompanyList | Out-GridView -PassThru -Title 'Select the companies you want to create and mount' |
    foreach $_.CompanyName {
    $CompanyName = $_.CompanyName
    $DatabaseName = $_.CompanyName -replace '/',''
    Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Creating database $DatabaseName"
    $AzureRmDatabase = New-AzureRmSqlDatabase -DatabaseName $DatabaseName -ServerName $AzureSqlServerShort -ResourceGroupName $AzureSqlResourceGroupName -RequestedServiceObjectiveName $TempSqlPricingTier -Edition $TempSqlEdition
    Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Database created"

    $MountedTenants = Get-NAVTenant -ApplicationDatabaseServer $AzureSqlServer -ApplicationDatabaseCredentials $AzureSQLCredential -ApplicationDatabaseName $ApplicationDatabaseNameOnAzureSql

    If ($MountedTenants -eq $null) { 
        $TenantId = 'default'
        Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") No existing tenants, setting TenantId to default" 
    }
    Else 
    {
        $TenantId = $DatabaseName
    }

    $TenantId = $TenantId -replace '\s',''

    # Import data into tenant
    Start-Sleep -Seconds 300
    Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Started importing data into $DatabaseName"
    Import-NAVData -DatabaseServer $AzureSqlServer -DatabaseName $DatabaseName -DatabaseCredentials $AzureSQLCredential -ApplicationDatabaseServer $AzureSqlServer -ApplicationDatabaseName $ApplicationDatabaseNameOnAzureSql -CompanyName $CompanyName -ApplicationDatabaseCredentials $AzureSQLCredential -FilePath $NAVBackupFilePath'\'$NAVServerInstance'.navdata'

    Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Data import finished"

#region Mount tenant, sync tenant and create NAV user

    $sbCreateNAVUser = {param($TenantId,$DatabaseName,$NAVServerInstance,$NAVDatabaseUserName,$NAVDatabaseUserPassword,$NAVUserFullName)
                      $DatabaseName = $DatabaseName -replace '/',''
                      Import-Module "C:\Program Files\Microsoft Dynamics NAV\90\Service\NavAdminTool.ps1"

                      # Mounting and syncing tenant
                      Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Starting NAV instance service"
                      Set-NAVServerInstance $NAVServerInstance -Start
                      Start-Sleep -s 60

                      #Dismount-NAVTenant -ServerInstance $NAVServerInstance -Tenant $TenantId -Force
                      Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Mounting tenant"
                      Mount-NAVTenant -ApplicationDatabaseServer $AzureSqlServer -ApplicationDatabaseCredentials $AzureSQLCredential -ApplicationDatabaseName $ApplicationDatabaseNameOnAzureSql -DatabaseCredentials $AzureSQLCredential -Id $TenantId -DatabaseName $DatabaseName -EncryptionProvider LocalKeyFile -OverwriteTenantIdInDatabase -Force -WarningAction SilentlyContinue
                      Start-Sleep -s 10
                      Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Syncing tenant"
                      Sync-NAVTenant -Tenant $TenantId -ServerInstance $NAVServerInstance -Mode ForceSync -Force

                      # Create NAV user
                      Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Creating NAV user"$NAVDatabaseUserName
                      New-NAVServerUser -Tenant $TenantId -ServerInstance $NAVServerInstance -UserName $NAVDatabaseUserName -Password (ConvertTo-SecureString -AsPlainText -Force $NAVDatabaseUserPassword) -FullName $NAVUserFullName -LicenseType Full
                      Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Creating user permission set"
                      New-NAVServerUserPermissionSet -Tenant $TenantId -ServerInstance $NAVServerInstance -Scope System -PermissionSetId SUPER -UserName $NAVDatabaseUserName -WarningAction SilentlyContinue
                      Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") User creation finished"
                      }
    Invoke-Command -Session $NAVVMServerSession -ScriptBlock $sbCreateNAVUser -ArgumentList $TenantId,$_.CompanyName,$NAVServerInstance,$NAVDatabaseUserName,$NAVDatabaseUserPassword,$NAVUserFullName

#endregion

#region Update database pricing tier
    Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") Changing pricing tier for database"$DatabaseName
    Update-AzureSqlPricingTier -Credential $AzureRmCredential -ServerName $AzureSqlServerShort -ResourceGroupName $AzureSqlResourceGroupName -DatabaseName $DatabaseName -PricingTier $SqlPricingTier -Edition $SqlEdition
#endregion
}

#Script output
Write-Host -ForegroundColor Yellow 'NAV Encryption key:' $NAVEncryptionKey
Write-Host -ForegroundColor Cyan "("$(Get-Date -format 'f')") The end"

#endregion