internal/functions/set-azurebacpacvalues.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

<#
    .SYNOPSIS
        Change the different Azure SQL Database details
         
    .DESCRIPTION
        When preparing an Azure SQL Database to be the new database for an Tier 2+ environment you need to set different details
         
    .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 AxDeployExtUserPwd
        Password obtained from LCS
         
    .PARAMETER AxDbAdminPwd
        Password obtained from LCS
         
    .PARAMETER AxRuntimeUserPwd
        Password obtained from LCS
         
    .PARAMETER AxMrRuntimeUserPwd
        Password obtained from LCS
         
    .PARAMETER AxRetailRuntimeUserPwd
        Password obtained from LCS
         
    .PARAMETER AxRetailDataSyncUserPwd
        Password obtained from LCS
         
    .PARAMETER TenantId
        The ID of tenant that the Azure SQL Database instance is going to be run under
         
    .PARAMETER PlanId
        The ID of the type of plan that the Azure SQL Database is going to be using
         
    .PARAMETER PlanCapability
        The capabilities that the Azure SQL Database instance will be running with
         
    .EXAMPLE
        PS C:\> Set-AzureBacpacValues -DatabaseServer dbserver1.database.windows.net -DatabaseName Import -SqlUser User123 -SqlPwd "Password123" -AxDeployExtUserPwd "Password123" -AxDbAdminPwd "Password123" -AxRuntimeUserPwd "Password123" -AxMrRuntimeUserPwd "Password123" -AxRetailRuntimeUserPwd "Password123" -AxRetailDataSyncUserPwd "Password123" -TenantId "TenantIdFromAzure" -PlanId "PlanIdFromAzure" -PlanCapability "Capabilities"
         
        This will set all the needed details inside the "Import" database that is located in the "dbserver1.database.windows.net" Azure SQL Database instance.
        All service accounts and their passwords will be updated accordingly.
         
    .NOTES
        Author: Rasmus Andersen (@ITRasmus)
        Author: Mötz Jensen (@Splaxi)
         
#>

function Set-AzureBacpacValues {
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns", "")]
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param (
        [Parameter(Mandatory = $true)]
        [string] $DatabaseServer,

        [Parameter(Mandatory = $true)]
        [string] $DatabaseName,

        [Parameter(Mandatory = $true)]
        [string] $SqlUser,

        [Parameter(Mandatory = $true)]
        [string] $SqlPwd,

        [Parameter(Mandatory = $true)]
        [string]$AxDeployExtUserPwd,

        [Parameter(Mandatory = $true)]
        [string]$AxDbAdminPwd,

        [Parameter(Mandatory = $true)]
        [string]$AxRuntimeUserPwd,

        [Parameter(Mandatory = $true)]
        [string]$AxMrRuntimeUserPwd,

        [Parameter(Mandatory = $true)]
        [string]$AxRetailRuntimeUserPwd,

        [Parameter(Mandatory = $true)]
        [string]$AxRetailDataSyncUserPwd,

        [Parameter(Mandatory = $true)]
        [string]$TenantId,

        [Parameter(Mandatory = $true)]
        [string]$PlanId,
        
        [Parameter(Mandatory = $true)]
        [string]$PlanCapability
    )
        
    $sqlCommand = Get-SQLCommand -DatabaseServer $DatabaseServer -DatabaseName $DatabaseName -SqlUser $SqlUser -SqlPwd $SqlPwd -TrustedConnection $false

    $commandText = (Get-Content "$script:ModuleRoot\internal\sql\set-bacpacvaluesazure.sql") -join [Environment]::NewLine

    $commandText = $commandText.Replace('@axdeployextuser', $AxDeployExtUserPwd)
    $commandText = $commandText.Replace('@axdbadmin', $AxDbAdminPwd)
    $commandText = $commandText.Replace('@axruntimeuser', $AxRuntimeUserPwd)
    $commandText = $commandText.Replace('@axmrruntimeuser', $AxMrRuntimeUserPwd)
    $commandText = $commandText.Replace('@axretailruntimeuser', $AxRetailRuntimeUserPwd)
    $commandText = $commandText.Replace('@axretaildatasyncuser', $AxRetailDataSyncUserPwd)

    $sqlCommand.CommandText = $commandText

    $null = $sqlCommand.Parameters.Add("@TenantId", $TenantId)
    $null = $sqlCommand.Parameters.Add("@PlanId", $PlanId)
    $null = $sqlCommand.Parameters.Add("@PlanCapability ", $PlanCapability)

    try {
        $sqlCommand.Connection.Open()

        Write-PSFMessage -Level Verbose "Execution sql statement against database" -Target $sqlCommand.CommandText
        $null = $sqlCommand.ExecuteNonQuery()
        
        $true
    }
    catch {
        Write-PSFMessage -Level Host -Message "Something went wrong while working against the database" -Exception $PSItem.Exception
        Stop-PSFFunction -Message "Stopping because of errors"
        return
    }
    finally {
        if ($sqlCommand.Connection.State -ne [System.Data.ConnectionState]::Closed) {
            $sqlCommand.Connection.Close()
        }

        $sqlCommand.Dispose()
    }
}