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) Install-Module BCSPowershellModule -Force $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" Backup-SqlDatabase -ServerInstance $databaseServerInstance -Database $templateDatabase -BackupFile $TempBackupFile -Credential $databaseCredentials | Out-Null Write-Host "Relocating Data file Template_Data" $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("Template_Data", "F:\DATA\$tenant.mdf") Write-Host "Relocating Data file Template_Log" $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" 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" 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" if (Test-Path -Path $TempBackupFile -PathType Leaf) { Remove-Item -Path $TempBackupFile } } -ArgumentList $databaseServerInstance, $databaseCredentials, $tenant, $templateDatabase, $tempFolder } end { } } Export-ModuleMember -Function New-BCSTenantDatabase |