How to automate the building of SQL Snapshot images using scheduled tasks

dean.obrien DeanonLeave a Comment on How to automate the building of SQL Snapshot images using scheduled tasks
SQL Server

Microsoft provide a framework for building container images in Azure called ACR, which can be called using the Azure CLI: https://docs.azure.cn/zh-cn/cli/acr?view=azure-cli-latest

Normally to trigger a build, you need to have docker installed on the local machine. However, if you use the ‘service principal’ method of authentication, this is not required: Registry authentication options - Azure Container Registry

With the above, it means we can create a PowerShell script (to be called by windows scheduler) that will authenticate with Azure Container Registry (ACR) and trigger a build in Azure, then push the resulting image to our ACR.

The script below does the following:

  1. Delete local copy of SQL backup (.BAK) files
  2. Copy fresh set of backups (Note: the script below relies on fresh BAK files being available on the same machine. You will need to update the script if the files need pulling from another location).
  3. Rename backups to common set of names (in our circumstance the backup files had filenames linked to time and date of backup)
  4. Log into Azure (Service Principal flow)
  5. Switch to correct subscription
  6. Log into Container Registry
  7. Confirm Connected (only required for debugging)
  8. Trigger a build (specifying Dockerfile and also context, i.e. snapshot of data) and tag as current date
  9. Tag recent build as ‘latest’
  10. Delete Image from 7 days ago

The build process above uses the Dockerfile and approach described here:How to create MSSQL Image With Custom DBs Attached (using BAK files)

Save the file below to disk and setup a windows scheduled task to call this every 24hrs.

Build.ps1

$Myreg = "my-reg"
$Myregfull = "$Myreg.azurecr.io"
$Date = Get-Date -format "yyyyMMdd"
$DateOneWeekBefore = (Get-Date).AddDays(-7).ToString("yyyyMMdd")
$Location = "E:\Sql-Image-Builder"
$Logfile = "$location\Log-$Date.txt"
$User ="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$Pass="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$Tenant="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$Subscription="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$MyImage="sql-on-windows:"
$MyRepo="sql-on-windows"
$CopySource = "D:\Backups*"

function checkExists {
	
    param (
        $Tag
    )
	
	$array = az acr repository show-tags -n $Myreg --repository $MyRepo
	foreach ($letter in $array)
	{
		if($letter.Contains($Tag)) { 
			return $true;
		}
	}
	return $false;
}

Start-Transcript -Path "$Location\transcript-$Date.txt" -Append

Try {
	# 1. REMOVE BACKUPS
	Get-Date
	Write-Host "--- Remove files ---" -ForegroundColor Green
	Write-Host "Remove-Item  $location\backups\* -Recurse -Force | out-null" -ForegroundColor Cyan
	Remove-Item $location\backups\* -Recurse -Force | out-null
}
Catch{
    Write-Host "Remove old backups failed at $(Get-Date). Error: $($_.Exception.Message)"
}

Try {
	# 2. Copy NEW BACKUPS
	Get-Date
	Write-Host "--- Copy Cores to image builder directory ---" -ForegroundColor Green
	Write-Host "Copy-Item -Path $CopySource -Destination $Location -Recurse | out-null" -ForegroundColor Cyan
	Copy-Item -Path $CopySource -Destination "$Location" -Recurse | out-null
}
Catch{
    Write-Host "Copy NEW BACKUPS failed at $(Get-Date). Error: $($_.Exception.Message)"
}

Write-Host "$Location\backups"
$files = Get-ChildItem "$Location\backups"

Write-Host "Looping through files"

for ($i=0; $i -lt $files.Count; $i++) {
    $databaseName =  $files[$i].FullName
    Write-Host "$databaseName"
    #Get-Content $files[$i].FullName | Where-Object { ($_ -match 'step4' -or $_ -match 'step9') } | Set-Content $outfile

    if ($databaseName -like "*_Master*") { 
        Write-Host "Rename-Item -Path $databaseName -NewName $Location\backups\10_Corp_Sitecore_Master.bak"
        Rename-Item -Path "$databaseName" -NewName "$Location\backups\10_Corp_Sitecore_Master.bak"
    }
    elseif ($databaseName -like "*_Core*") { 
        Write-Host "Rename-Item -Path $databaseName -NewName $Location\backups\10_Corp_Sitecore_Core.bak"
        Rename-Item -Path "$databaseName" -NewName "$Location\backups\10_Corp_Sitecore_Core.bak"
    }
    elseif ($databaseName -like "*_audit*") { 
        Write-Host "Rename-Item -Path $databaseName -NewName $Location\backups\10_Corp_Sitecore_audit.bak"
        Rename-Item -Path "$databaseName" -NewName "$Location\backups\10_Corp_Sitecore_audit.bak"
    }
    elseif ($databaseName -like "*_Sessions*") { 
        Write-Host "Rename-Item -Path $databaseName -NewName $Location\backups\10_Corp_Sitecore_Sessions.bak"
        Rename-Item -Path "$databaseName" -NewName "$Location\backups\10_Corp_Sitecore_Sessions.bak"
    }
    elseif ($databaseName -like "*_CourseData*") { 
        Write-Host "Rename-Item -Path $databaseName -NewName $Location\backups\10_Corp_Sitecore_CourseData.bak"
        Rename-Item -Path "$databaseName" -NewName "$Location\backups\10_Corp_Sitecore_CourseData.bak"
    }
    elseif ($databaseName -like "*_Messaging*") { 
        Write-Host "Rename-Item -Path $databaseName -NewName $Location\backups\10_Corp_Sitecore_Messaging.bak"
        Rename-Item -Path "$databaseName" -NewName "$Location\backups\10_Corp_Sitecore_Messaging.bak"
    }
    elseif ($databaseName -like "*_ExperienceForms*") { 
        Write-Host "Rename-Item -Path $databaseName -NewName $Location\backups\10_Corp_Sitecore_ExperienceForms.bak"
        Rename-Item -Path "$databaseName" -NewName "$Location\backups\10_Corp_Sitecore_ExperienceForms.bak"
    }
    else
    {
	Write-Verbose "Couldnt find $databaseName"
    }
}



Try {
	# 3. Logging in as service principal
	Get-Date
	Write-Host "--- Logging in as service principal ---" -ForegroundColor Green
	Write-Host "az login --service-principal -u $User -p $Pass --tenant $Tenant" -ForegroundColor Cyan
	az login --service-principal -u $User -p $Pass --tenant $Tenant
}
Catch{
    Write-Host "Logging in as service principal failed at $(Get-Date). Error: $($_.Exception.Message)"
}

Try {
	# 4. Switching to subscription
	Get-Date
	Write-Host "--- Switching to subscription ---" -ForegroundColor Green
	Write-Host "az account set --subscription $Subscription" -ForegroundColor Cyan
	az account set --subscription $Subscription
}
Catch{
    Write-Host "Switching to subscription failed at $(Get-Date). Error: $($_.Exception.Message)"
}

Try {
	# 5. Logging in to registry
	Get-Date
	Write-Host "--- Logging in to registry $Myregfull ---" -ForegroundColor Green
	$TOKEN=$(az acr login --name $Myreg --expose-token --output tsv --query accessToken)
	Write-Host "docker login $Myregfull -u 00000000-0000-0000-0000-000000000000 -p $TOKEN" -ForegroundColor Cyan
	docker login $Myregfull -u 00000000-0000-0000-0000-000000000000 -p $TOKEN
}
Catch{
    Write-Host "Logging in to registry failed at $(Get-Date). Error: $($_.Exception.Message)"
}

Try {
	# 6. Confirm connected
	Get-Date
	Write-Host "--- Confirming connected ---" -ForegroundColor Green
	Write-Host "az acr show -n $Myreg" -ForegroundColor Cyan
	az acr show -n $Myreg
	az acr repository list -n $Myreg
}
Catch{
    Write-Host "Confirm connected failed at $(Get-Date). Error: $($_.Exception.Message)"
}

Try {
	# 7. Triggerng Build
	Get-Date
	Write-Host "--- Triggering build of $Myregfull/${MyRepo}:$Date  ---" -ForegroundColor Green
	Write-Host "az acr build -t ${MyRepo}:$Date -r $Myreg -f $Location\dockerfile $Location --platform windows" -ForegroundColor Cyan
	az acr build -t ${MyRepo}:$Date -r $Myreg -f $Location\dockerfile $Location --platform windows
}
Catch{
    Write-Host "Triggerng Build failed at $(Get-Date). Error: $($_.Exception.Message)"
}

Try {
	# 8. Tag as latest
	Get-Date
	Write-Host "--- Tag as latest ---" -ForegroundColor Green
	Write-Host "az acr import --name $Myreg --source ${MyRepo}:$Date  --image ${MyRepo}:latest --force" -ForegroundColor Cyan
	az acr import --name $Myreg --source $Myregfull/${MyRepo}:$Date  --image ${MyRepo}:latest --force
}
Catch{
    Write-Host "Tag as latest failed at $(Get-Date). Error: $($_.Exception.Message)"
}

Try {
	# 9. Delete 7 days ago
	Get-Date
	Write-Host "--- Deleting image from 7 days ago (if exists) ---" -ForegroundColor Green
	Write-Host "Checking if ${MyRepo}:$DateOneWeekBefore exists" -ForegroundColor Green
	if(checkExists $DateOneWeekBefore)
	{
		Write-Host "az acr repository delete --name $Myreg --image ${MyRepo}:$DateOneWeekBefore -y" -ForegroundColor Cyan
		az acr repository delete --name $Myreg --image ${MyRepo}:$DateOneWeekBefore -y
	}
	else
	{
		Write-Host "Tag doesnt exist - no action taken"-ForegroundColor Cyan
	}
}
Catch{
    Write-Host "Delete 7 days ago failed at $(Get-Date). Error: $($_.Exception.Message)"
}

Get-Date
Write-Host "--- COMPLETE ---" -ForegroundColor Green

Summary

Using the process described above, you can make available a set of images that contain a snapshot of a set of databases, for multiple points in time. This can prove very useful if you need to pull up a version of a production environment locally, or if you need to rapidly deploy a kubernetes cluster with a recent version of your production environment.

Leave a Reply

Your email address will not be published. Required fields are marked *