119 lines
3.1 KiB
PowerShell
119 lines
3.1 KiB
PowerShell
# Variables
|
|
$tenantId = "7844775a-a9cc-4c33-a5ae-36dcf6660f45" #Galenica
|
|
$clientId = "d28076dd-2108-4718-802e-cd3c35fd5505" #pcpl-BAGSpezListePrd-DBBackup
|
|
$vaultName="kv-BAGSpezListePrd-bkp"
|
|
$secretName="superuser"
|
|
|
|
$maxRetries = 5
|
|
$retryInterval = 10 # seconds
|
|
$retryCount = 0
|
|
$success = $false
|
|
|
|
#Managed instance related
|
|
$MILogin='superuser'
|
|
$MIPwd=$null #fetched from key vault later
|
|
$MIInstance="sqlmi-bagspezlisteprd-sqlinstance.75ff9425ac13.database.windows.net"
|
|
|
|
function log-out{
|
|
[CmdletBinding()]
|
|
param(
|
|
[Parameter(Mandatory=$true)]
|
|
[string] $msg
|
|
)
|
|
Write-Output "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") :: $msg"
|
|
}
|
|
|
|
# Log in to Azure
|
|
$retryCount = 0
|
|
$success = $false
|
|
|
|
while (-not $success -and $retryCount -lt $maxRetries) {
|
|
try{
|
|
$null = Connect-AzAccount -ServicePrincipal -ApplicationId $clientId -Tenant $tenantId -Subscription BAGSpezListePrd -CertificateThumbprint 7bd45f67999015c7742db25efc86bae97590c57d
|
|
$success=$true
|
|
}
|
|
catch{
|
|
$retryCount++
|
|
Start-Sleep -Seconds $retryInterval
|
|
}
|
|
}
|
|
|
|
if(-not $success){
|
|
write-error "Error auhthenticating to azure"
|
|
exit 1
|
|
}
|
|
|
|
#fetch the Managed Instance password
|
|
$retryCount = 0
|
|
$success = $false
|
|
|
|
while (-not $success -and $retryCount -lt $maxRetries) {
|
|
try{
|
|
$MIPwd = Get-AzKeyVaultSecret -VaultName $vaultName -Name $secretName -AsPlainText
|
|
$success=$true
|
|
}
|
|
catch{
|
|
$retryCount++
|
|
Start-Sleep -Seconds $retryInterval
|
|
}
|
|
}
|
|
|
|
if(-not $success){
|
|
write-error "Error fetching password from key vault"
|
|
exit 1
|
|
}
|
|
|
|
#craft credential for MI connection
|
|
$securePassword = ConvertTo-SecureString $MIPwd -AsPlainText -Force
|
|
$credentialMI = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $MILogin, $securePassword
|
|
|
|
#start sl2007.daily_batch_update
|
|
log-out -msg "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") starting sl2007 daily update"
|
|
$query="
|
|
exec sl2007.dbo.usp_Daily_Batch_Update
|
|
"
|
|
$retryCount = 0
|
|
$success = $false
|
|
|
|
while (-not $success -and $retryCount -lt $maxRetries) {
|
|
try{
|
|
Invoke-Sqlcmd -ServerInstance $MIInstance -Query $query -Credential $credentialMI
|
|
log-out -msg "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") sl2007 daily update done"
|
|
$success=$true
|
|
}
|
|
catch{
|
|
$retryCount++
|
|
Start-Sleep -Seconds $retryInterval
|
|
}
|
|
}
|
|
|
|
if(-not $success){
|
|
write-error "Error starting usp_Daily_Batch_Update"
|
|
exit 1
|
|
}
|
|
|
|
#start sl2007_superset generation
|
|
log-out -msg "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") starting sl2007_superset generation"
|
|
$query="
|
|
exec sl2007_superset.dbo.p_Fill__BAG__SL2007_Superset
|
|
"
|
|
$retryCount = 0
|
|
$success = $false
|
|
|
|
while (-not $success -and $retryCount -lt $maxRetries) {
|
|
try{
|
|
Invoke-Sqlcmd -ServerInstance $MIInstance -Query $query -Credential $credentialMI
|
|
log-out -msg "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") sl2007_superset generation done"
|
|
$success=$true
|
|
}
|
|
catch{
|
|
$retryCount++
|
|
Start-Sleep -Seconds $retryInterval
|
|
}
|
|
}
|
|
|
|
if(-not $success){
|
|
write-error "Error starting sl2007_suèerset production"
|
|
exit 1
|
|
}
|