Module/Administration/New-BCSTenantDatabase.ps1
|
<#
.SYNOPSIS Create new tenant database .DESCRIPTION Creates a new tenant database on SQL server using a template database .PARAMETER businessCentralServer Business Central server .PARAMETER sqlServerInstance Name of the SQL Server and Instance .PARAMETER tenant Name of the new tenant, will be the new database name .PARAMETER tempalteDatabase Name of the database on the server to use as a template when createing the new tenant database .PARAMETER tempFolder TempFolder where temporary database backup is saved, default c:\temp\ .PARAMETER azureCredentials Your azure credentials to sign in to the Azure Database Server .PARAMETER databaseCredentials Your SQL login to the SQL Server, Credential information in the form of a System.Management.Automation.PSCredential object .NOTES Author: Mathias Stjernfelt Website: http://www.brightcom.se .EXAMPLE New-BCSTenantDatabase -serverInstance "SEBCDB02" -tenant "myNewTenant" -templateDatabase "TemplateDB" -credential (Get-Credential) #> function New-BCSTenantDatabase { Param ( [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] $databaseServer, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $databaseServerInstance = "BCNUP", [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] $databaseName, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $templateDatabase = "Template (19-0)", [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $tempFolder = "c:\temp\", [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] [System.Management.Automation.PSCredential]$databaseCredentials ) begin {} process { $userProfile = Get-BCSModuleProfile if (-not $databaseCredentials) { $databaseCredentials = Get-BCSCredential -userName $userProfile.userName -securePassword (ConvertTo-SecureString -String $userProfile.databasePassword) } $securePassword = ConvertTo-SecureString -String $userProfile.AzurePassword $credential = New-Object System.Management.Automation.PSCredential -argumentList $userProfile.AzureUserName, $securePassword Write-Host "Connecting to server $databaseServer as user $($userProfile.AzureUserName)" $vmSession = New-DeploymentRemoteSession -HostName $databaseServer -Credential $credential $sessionArgument = @{ "Session" = $vmSession } $tenant = $databaseName; Invoke-Command @sessionArgument -ScriptBlock { Param($databaseServerInstance, [System.Management.Automation.PSCredential]$databaseCredentials, $tenant, $templateDatabase, $tempFolder) $ErrorActionPreference = "Stop" if (-not(Get-Module -ListAvailable -Name SQLServer)) { Install-Module SQLServer -Force } if ([string]::IsNullOrEmpty($databaseServerInstance)) { $databaseServerInstance = $databaseServer } $tempFolder = Join-Path $tempFolder $(New-Guid) New-Item -Path $tempFolder -ItemType Directory | Out-Null $TempBackupFile = "$tempFolder\template.bak" Write-Host "Backup database $templateDatabase on $databaseServerInstance to folder $TempBackupFile" -ForegroundColor Yellow Backup-SqlDatabase -ServerInstance $databaseServerInstance -Database $templateDatabase -BackupFile $TempBackupFile -Credential $databaseCredentials | Out-Null Write-Host "Relocating Data file Template_Data" -ForegroundColor Yellow $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("Template_Data", "F:\DATA\$tenant.mdf") Write-Host "Relocating Data file Template_Log" -ForegroundColor Yellow $RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("Template_Log", "G:\LOG\$tenant.mdf") Restore-SqlDatabase -ServerInstance $databaseServerInstance -Database $tenant -BackupFile $TempBackupFile -RelocateFile @($RelocateData, $RelocateLog) -Credential $databaseCredentials $dbLogicalFileName = $tenant.replace(' ','') Write-Host "Changing database Logical Name Template_Data to $($dbLogicalFileName)_Data" -ForegroundColor Yellow Invoke-Sqlcmd -ServerInstance $databaseServerInstance -Database $tenant -Query "ALTER DATABASE [$tenant] MODIFY FILE ( NAME = Template_Data, NEWNAME = $($dbLogicalFileName)_Data );" -Credential $databaseCredentials Write-Host "Changing database Logical Name Template_Log to $($dbLogicalFileName)_Log" -ForegroundColor Yellow Invoke-Sqlcmd -ServerInstance $databaseServerInstance -Database $tenant -Query "ALTER DATABASE [$tenant] MODIFY FILE ( NAME = Template_Log, NEWNAME = $($dbLogicalFileName)_Log );" -Credential $databaseCredentials Write-Host "Database $tenant has been created succesfully on SQL Server $databaseServerInstance" -ForegroundColor Yellow if (Test-Path -Path $TempBackupFile -PathType Leaf) { Remove-Item -Path $TempBackupFile } } -ArgumentList $databaseServerInstance, $databaseCredentials, $tenant, $templateDatabase, $tempFolder } end { } } Export-ModuleMember -Function New-BCSTenantDatabase |