mssql2019-posh5 #27
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: mssql2019-posh5 | |
| on: | |
| pull_request: | |
| branches: | |
| - release | |
| schedule: | |
| - cron: '0 3 * * *' # Runs at 3am UTC every day | |
| workflow_dispatch: | |
| jobs: | |
| run_auto-masklet: | |
| runs-on: windows-latest | |
| environment: 'build-posh5' | |
| steps: | |
| - name: Checkout code | |
| uses: actions/checkout@v3 | |
| - name: Install rgclone CLI | |
| id: installCLI | |
| env: | |
| RGCLONE_API_ENDPOINT: ${{ secrets.RGCLONE_API_ENDPOINT }} | |
| RGCLONE_ACCESS_TOKEN: ${{ secrets.RGCLONE_ACCESS_TOKEN }} | |
| run: | | |
| # Download the rgclone CLI | |
| $downloadUrl = $env:RGCLONE_API_ENDPOINT + "cloning-api/download/cli/windows-amd64" | |
| $zipFile = ".\rgclone.zip" | |
| $rgCloneLocation = "." | |
| Write-Output " Downloading rgclone.exe zip file..." | |
| Write-Output " from: $downloadUrl" | |
| Write-Output " to: $zipFile" | |
| Invoke-WebRequest -Uri $downloadUrl -OutFile $zipFile | |
| Write-Output " Extracting zip to: $rgCloneLocation" | |
| Add-Type -assembly "System.IO.Compression.Filesystem"; | |
| [IO.Compression.Zipfile]::ExtractToDirectory($zipFile, $rgCloneLocation); | |
| - name: Create data image | |
| id: createIm | |
| env: | |
| RGCLONE_API_ENDPOINT: ${{ secrets.RGCLONE_API_ENDPOINT }} | |
| RGCLONE_ACCESS_TOKEN: ${{ secrets.RGCLONE_ACCESS_TOKEN }} | |
| run: | | |
| $emptyDiName = 'autoMasklet-sqlserver-2019-empty' | |
| $output = .\rgclone get di $emptyDiName --ignore-not-found -o json | |
| if ($output -eq $null -or $output -eq "") { | |
| Write-Output "Image does not exist. Creating image..." | |
| .\rgclone create di -f .\helper_scripts\empty-image.yaml | |
| } else { | |
| Write-Output "Image already exists. Moving on..." | |
| } | |
| - name: Create data container | |
| id: createDc | |
| env: | |
| RGCLONE_API_ENDPOINT: ${{ secrets.RGCLONE_API_ENDPOINT }} | |
| RGCLONE_ACCESS_TOKEN: ${{ secrets.RGCLONE_ACCESS_TOKEN }} | |
| run: | | |
| # Create an empty SQL Server instance on which to run auto-masklet | |
| Write-Output "Creating data container" | |
| $emptyDiName = 'autoMasklet-sqlserver-2019-empty' | |
| $output = .\rgclone create dc -i $emptyDiName -t 20m -o json | ConvertFrom-Json | |
| $dbPassword = $output.password | |
| $dbUser = $output.user | |
| $sqlhost = $output.host | |
| $sqlport = $output.port | |
| $instance = "${sqlhost},${sqlport}" | |
| Write-Output "Data container created successfully and available at: $instance" | |
| # Set output values so that I can use them in subsequent steps | |
| echo "::set-output name=dbUser::$dbUser" | |
| echo "::set-output name=dbPassword::$dbPassword" | |
| echo "::set-output name=instance::$instance" | |
| - name: Run AutoMasklet in PowerShell 5 | |
| shell: powershell | |
| env: | |
| REDGATE_LICENSING_PERMIT: ${{ secrets.REDGATE_LICENSING_PERMIT }} | |
| run: | | |
| Write-Output 'Running in PowerShell 5' | |
| $PSVersionTable | |
| $psVersion = $PSVersionTable.PSVersion.Major | |
| if ($psVersion -notlike "5"){ | |
| Write-Error "PowerShell version should be 5, but it is $psVersion" | |
| exit 1 | |
| } | |
| Write-Output 'Running Auto-Masklet...' | |
| Write-Output ' -sqlInstance ${{ steps.createDc.outputs.instance }}' | |
| Write-Output ' -sqlUser ${{ steps.createDc.outputs.dbUser }}' | |
| .\run-auto-masklet.ps1 -sqlUser '${{ steps.createDc.outputs.dbUser }}' -sqlPassword '${{ steps.createDc.outputs.dbPassword }}' -sqlInstance '${{ steps.createDc.outputs.instance }}' -autoContinue -skipAuth | |
| - name: Validating results | |
| shell: powershell | |
| if: ${{ always() }} | |
| run: | | |
| import-module dbatools | |
| Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true | |
| Set-DbatoolsConfig -FullName sql.connection.encrypt -Value $false | |
| $SqlCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList ${{ steps.createDc.outputs.dbUser }}, (ConvertTo-SecureString ${{ steps.createDc.outputs.dbPassword }} -AsPlainText -Force) | |
| # Validating rgsubset | |
| $totalOrders = (Invoke-DbaQuery -SqlInstance '${{ steps.createDc.outputs.instance }}' -Database Northwind_Subset -Query "SELECT COUNT (*) AS TotalOrders FROM dbo.Orders" -SqlCredential $SqlCredential).TotalOrders | |
| Write-Output "If rgsubset ran successfully, there should be 12 orders in the Northwind_Subset database" | |
| Write-Output "Total Orders: $totalOrders" | |
| if ($totalOrders -eq 12) { | |
| Write-Output "rgsubset ran successfully" | |
| } else { | |
| Write-Error "rgsubset did not run successfully" | |
| exit 1 | |
| } | |
| # Validating rganonymize | |
| $order10248City = (Invoke-DbaQuery -SqlInstance '${{ steps.createDc.outputs.instance }}' -Database Northwind_Subset -Query "SELECT ShipCity FROM dbo.Orders WHERE OrderID = 10248" -SqlCredential $SqlCredential).ShipCity | |
| Write-Output "If rganonymize ran successfully, order 10248 should NOT be Reims" | |
| Write-Output "ShipCity: $order10248City" | |
| if ($order10248City -like "Reims") { | |
| Write-Error "rganonymize did not run successfully" | |
| exit 1 | |
| } else { | |
| Write-Output "rganonymize ran successfully" | |
| } |