Create a gist now

Instantly share code, notes, and snippets.

anonymous /CustomFunctions.psm1
Created Apr 26, 2016

What would you like to do?
Move single-tenant NAV 2016 to Azure SQL and multitenancy
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"
}
}
#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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment