functions/import-d365bacpac.ps1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 |
<# .SYNOPSIS Import a bacpac file .DESCRIPTION Import a bacpac file to either a Tier1 or Tier2 environment .PARAMETER ImportModeTier1 Switch to instruct the cmdlet that it will import into a Tier1 environment The cmdlet will expect to work against a SQL Server instance .PARAMETER ImportModeTier2 Switch to instruct the cmdlet that it will import into a Tier2 environment The cmdlet will expect to work against an Azure DB instance .PARAMETER DatabaseServer The name of the database server If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN). If Azure use the full address to the database server, e.g. server.database.windows.net .PARAMETER DatabaseName The name of the database .PARAMETER SqlUser The login name for the SQL Server instance .PARAMETER SqlPwd The password for the SQL Server user .PARAMETER BacpacFile Path to the bacpac file you want to import into the database server .PARAMETER NewDatabaseName Name of the new database that will be created while importing the bacpac file This will create a new database on the database server and import the content of the bacpac into .PARAMETER AxDeployExtUserPwd Password that is obtained from LCS .PARAMETER AxDbAdminPwd Password that is obtained from LCS .PARAMETER AxRuntimeUserPwd Password that is obtained from LCS .PARAMETER AxMrRuntimeUserPwd Password that is obtained from LCS .PARAMETER AxRetailRuntimeUserPwd Password that is obtained from LCS .PARAMETER AxRetailDataSyncUserPwd Password that is obtained from LCS .PARAMETER AxDbReadonlyUserPwd Password that is obtained from LCS .PARAMETER CustomSqlFile Path to the sql script file that you want the cmdlet to execute against your data after it has been imported .PARAMETER ModelFile Path to the model file that you want the SqlPackage.exe to use instead the one being part of the bacpac file This is used to override SQL Server options, like collation and etc .PARAMETER DiagnosticFile Path to where you want the import to output a diagnostics file to assist you in troubleshooting the import .PARAMETER ImportOnly Switch to instruct the cmdlet to only import the bacpac into the new database The cmdlet will create a new database and import the content of the bacpac file into this Nothing else will be executed .PARAMETER MaxParallelism Sets SqlPackage.exe's degree of parallelism for concurrent operations running against a database The default value is 8 .PARAMETER LogPath The path where the log file(s) will be saved When running without the ShowOriginalProgress parameter, the log files will be the standard output and the error output from the underlying tool executed .PARAMETER ShowOriginalProgress Instruct the cmdlet to show the standard output in the console Default is $false which will silence the standard output .PARAMETER OutputCommandOnly Instruct the cmdlet to only output the command that you would have to execute by hand Will include full path to the executable and the needed parameters based on your selection .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Invoke-D365InstallSqlPackage You should always install the latest version of the SqlPackage.exe, which is used by New-D365Bacpac. This will fetch the latest .Net Core Version of SqlPackage.exe and install it at "C:\temp\d365fo.tools\SqlPackage". .EXAMPLE PS C:\> Import-D365Bacpac -ImportModeTier1 -BacpacFile "C:\temp\uat.bacpac" -NewDatabaseName "ImportedDatabase" PS C:\> Switch-D365ActiveDatabase -NewDatabaseName "ImportedDatabase" This will instruct the cmdlet that the import will be working against a SQL Server instance. It will import the "C:\temp\uat.bacpac" file into a new database named "ImportedDatabase". The next thing to do is to switch the active database out with the new one you just imported. "ImportedDatabase" will be switched in as the active database, while the old one will be named "AXDB_original". .EXAMPLE PS C:\> Import-D365Bacpac -ImportModeTier2 -SqlUser "sqladmin" -SqlPwd "XyzXyz" -BacpacFile "C:\temp\uat.bacpac" -AxDeployExtUserPwd "XxXx" -AxDbAdminPwd "XxXx" -AxRuntimeUserPwd "XxXx" -AxMrRuntimeUserPwd "XxXx" -AxRetailRuntimeUserPwd "XxXx" -AxRetailDataSyncUserPwd "XxXx" -AxDbReadonlyUserPwd "XxXx" -NewDatabaseName "ImportedDatabase" PS C:\> Switch-D365ActiveDatabase -NewDatabaseName "ImportedDatabase" -SqlUser "sqladmin" -SqlPwd "XyzXyz" This will instruct the cmdlet that the import will be working against an Azure DB instance. It requires all relevant passwords from LCS for all the builtin user accounts used in a Tier 2 environment. It will import the "C:\temp\uat.bacpac" file into a new database named "ImportedDatabase". The next thing to do is to switch the active database out with the new one you just imported. "ImportedDatabase" will be switched in as the active database, while the old one will be named "AXDB_original". .EXAMPLE PS C:\> Import-D365Bacpac -ImportModeTier1 -BacpacFile "C:\temp\uat.bacpac" -NewDatabaseName "ImportedDatabase" -DiagnosticFile "C:\temp\ImportLog.txt" This will instruct the cmdlet that the import will be working against a SQL Server instance. It will import the "C:\temp\uat.bacpac" file into a new database named "ImportedDatabase". It will output a diagnostic file to "C:\temp\ImportLog.txt". .EXAMPLE PS C:\> Import-D365Bacpac -ImportModeTier1 -BacpacFile "C:\temp\uat.bacpac" -NewDatabaseName "ImportedDatabase" -DiagnosticFile "C:\temp\ImportLog.txt" -MaxParallelism 32 This will instruct the cmdlet that the import will be working against a SQL Server instance. It will import the "C:\temp\uat.bacpac" file into a new database named "ImportedDatabase". It will output a diagnostic file to "C:\temp\ImportLog.txt". It will use 32 connections against the database server while importing the bacpac file. .EXAMPLE PS C:\> Import-D365Bacpac -ImportModeTier1 -BacpacFile "C:\temp\uat.bacpac" -NewDatabaseName "ImportedDatabase" -ImportOnly This will instruct the cmdlet that the import will be working against a SQL Server instance. It will import the "C:\temp\uat.bacpac" file into a new database named "ImportedDatabase". No cleanup or prepping jobs will be executed, because this is for importing only. This would be something that you can use when extract a bacpac file from a Tier1 and want to import it into a Tier1. You would still need to execute the Switch-D365ActiveDatabase cmdlet, to get the newly imported database to be the AXDB database. .NOTES Tags: Database, Bacpac, Tier1, Tier2, Golden Config, Config, Configuration Author: Rasmus Andersen (@ITRasmus) Author: Mötz Jensen (@Splaxi) #> function Import-D365Bacpac { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseProcessBlockForPipelineCommand", "")] [CmdletBinding(DefaultParameterSetName = 'ImportTier1')] param ( [Parameter(Mandatory = $true, ParameterSetName = 'ImportTier1', Position = 0)] [switch] $ImportModeTier1, [Parameter(Mandatory = $true, ParameterSetName = 'ImportTier2', Position = 0)] [Parameter(Mandatory = $true, ParameterSetName = 'ImportOnlyTier2', Position = 0)] [switch] $ImportModeTier2, [Parameter(Position = 1 )] [string] $DatabaseServer = $Script:DatabaseServer, [Parameter(Position = 2 )] [string] $DatabaseName = $Script:DatabaseName, [Parameter(Mandatory = $false, Position = 3 )] [Parameter(Mandatory = $true, ParameterSetName = 'ImportTier2', ValueFromPipelineByPropertyName = $true, Position = 3)] [Parameter(Mandatory = $false, ParameterSetName = 'ImportTier1', Position = 3)] [Parameter(Mandatory = $true, ParameterSetName = 'ImportOnlyTier2', ValueFromPipelineByPropertyName = $true, Position = 3)] [string] $SqlUser = $Script:DatabaseUserName, [Parameter(Mandatory = $false, Position = 4 )] [Parameter(Mandatory = $true, ParameterSetName = 'ImportTier2', ValueFromPipelineByPropertyName = $true, Position = 4)] [Parameter(Mandatory = $false, ParameterSetName = 'ImportTier1', Position = 4)] [Parameter(Mandatory = $true, ParameterSetName = 'ImportOnlyTier2', ValueFromPipelineByPropertyName = $true, Position = 4)] [string] $SqlPwd = $Script:DatabaseUserPassword, [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, Position = 5 )] [Alias('File')] [string] $BacpacFile, [Parameter(Mandatory = $true, Position = 6 )] [string] $NewDatabaseName, [Parameter(Mandatory = $true, ParameterSetName = 'ImportTier2', ValueFromPipelineByPropertyName = $true, Position = 7)] [Parameter(Mandatory = $false, ParameterSetName = 'ImportOnlyTier2', Position = 7)] [string] $AxDeployExtUserPwd, [Parameter(Mandatory = $true, ParameterSetName = 'ImportTier2', ValueFromPipelineByPropertyName = $true, Position = 8)] [Parameter(Mandatory = $false, ParameterSetName = 'ImportOnlyTier2', Position = 8)] [string] $AxDbAdminPwd, [Parameter(Mandatory = $true, ParameterSetName = 'ImportTier2', ValueFromPipelineByPropertyName = $true, Position = 9)] [Parameter(Mandatory = $false, ParameterSetName = 'ImportOnlyTier2', Position = 9)] [string] $AxRuntimeUserPwd, [Parameter(Mandatory = $true, ParameterSetName = 'ImportTier2', ValueFromPipelineByPropertyName = $true, Position = 10)] [Parameter(Mandatory = $false, ParameterSetName = 'ImportOnlyTier2', Position = 10)] [string] $AxMrRuntimeUserPwd, [Parameter(Mandatory = $true, ParameterSetName = 'ImportTier2', ValueFromPipelineByPropertyName = $true, Position = 11)] [Parameter(Mandatory = $false, ParameterSetName = 'ImportOnlyTier2', Position = 11)] [string] $AxRetailRuntimeUserPwd, [Parameter(Mandatory = $true, ParameterSetName = 'ImportTier2', ValueFromPipelineByPropertyName = $true, Position = 12)] [Parameter(Mandatory = $false, ParameterSetName = 'ImportOnlyTier2', Position = 12)] [string] $AxRetailDataSyncUserPwd, [Parameter(Mandatory = $true, ParameterSetName = 'ImportTier2', ValueFromPipelineByPropertyName = $true, Position = 13)] [Parameter(Mandatory = $false, ParameterSetName = 'ImportOnlyTier2', Position = 13)] [string] $AxDbReadonlyUserPwd, [string] $CustomSqlFile, [string] $ModelFile, [string] $DiagnosticFile, [Parameter(Mandatory = $false, ParameterSetName = 'ImportTier1')] [Parameter(Mandatory = $true, ParameterSetName = 'ImportOnlyTier2')] [switch] $ImportOnly, [string] $MaxParallelism = 8, [Alias('LogDir')] [string] $LogPath = $(Join-Path -Path $Script:DefaultTempPath -ChildPath "Logs\ImportBacpac"), [switch] $ShowOriginalProgress, [switch] $OutputCommandOnly, [switch] $EnableException ) if (-not (Test-PathExists -Path $BacpacFile -Type Leaf)) { return } if ($PSBoundParameters.ContainsKey("CustomSqlFile")) { if (-not (Test-PathExists -Path $CustomSqlFile -Type Leaf)) { return } else { $ExecuteCustomSQL = $true } } Invoke-TimeSignal -Start $UseTrustedConnection = Test-TrustedConnection $PSBoundParameters $BaseParams = @{ DatabaseServer = $DatabaseServer DatabaseName = $DatabaseName SqlUser = $SqlUser SqlPwd = $SqlPwd } $ImportParams = @{ Action = "import" FilePath = $BacpacFile } if (-not [system.string]::IsNullOrEmpty($DiagnosticFile)) { if (-not (Test-PathExists -Path (Split-Path $DiagnosticFile -Parent) -Type Container -Create)) { return } $ImportParams.DiagnosticFile = $DiagnosticFile } if (-not [system.string]::IsNullOrEmpty($ModelFile)) { if (-not (Test-PathExists -Path $ModelFile -Type Leaf)) { return } $ImportParams.ModelFile = $ModelFile } Write-PSFMessage -Level Verbose "Testing if we are working against a Tier2 / Azure DB" if ($ImportModeTier2) { Write-PSFMessage -Level Verbose "Start collecting the current Azure DB instance settings" $Objectives = Get-AzureServiceObjective @BaseParams if ($null -eq $Objectives) { return } [System.Collections.ArrayList] $Properties = New-Object -TypeName "System.Collections.ArrayList" $null = $Properties.Add("DatabaseEdition=$($Objectives.DatabaseEdition)") $null = $Properties.Add("DatabaseServiceObjective=$($Objectives.DatabaseServiceObjective)") $ImportParams.Properties = $Properties.ToArray() } $Params = Get-DeepClone $BaseParams $Params.DatabaseName = $NewDatabaseName Write-PSFMessage -Level Verbose "Start importing the bacpac with a new database name and current settings" Invoke-SqlPackage @Params @ImportParams -TrustedConnection $UseTrustedConnection -ShowOriginalProgress:$ShowOriginalProgress -OutputCommandOnly:$OutputCommandOnly -LogPath $LogPath if ($OutputCommandOnly) { return } if ($ImportOnly) { return } if (Test-PSFFunctionInterrupt) { return } Write-PSFMessage -Level Verbose "Importing completed" Write-PSFMessage -Level Verbose -Message "Start working on the configuring the new database" if ($ImportModeTier2) { Write-PSFMessage -Level Verbose "Building sql statement to update the imported Azure database" $InstanceValues = Get-InstanceValues @BaseParams -TrustedConnection $UseTrustedConnection if ($null -eq $InstanceValues) { return } $AzureParams = @{ AxDeployExtUserPwd = $AxDeployExtUserPwd; AxDbAdminPwd = $AxDbAdminPwd; AxRuntimeUserPwd = $AxRuntimeUserPwd; AxMrRuntimeUserPwd = $AxMrRuntimeUserPwd; AxRetailRuntimeUserPwd = $AxRetailRuntimeUserPwd; AxRetailDataSyncUserPwd = $AxRetailDataSyncUserPwd; AxDbReadonlyUserPwd = $AxDbReadonlyUserPwd; } $res = Set-AzureBacpacValues @Params @AzureParams @InstanceValues if (-not ($res)) { return } } else { Write-PSFMessage -Level Verbose "Building sql statement to update the imported SQL database" $res = Set-SqlBacpacValues @Params -TrustedConnection $UseTrustedConnection if (-not ($res)) { return } } if ($ExecuteCustomSQL) { Write-PSFMessage -Level Verbose -Message "Invoking the Execution of custom SQL script" $res = Invoke-D365SqlScript @Params -FilePath $CustomSqlFile -TrustedConnection $UseTrustedConnection if (-not ($res)) { return } } Invoke-TimeSignal -End } |