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

<#
    .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 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:\> 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,

        [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

    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
}