Ilya Baybikov's home page

Redgate SQL Compare

A friend of mine told me that some teams still deploy database schema changes manually - script by script in SSMS. Yes, it feels powerful. You’re the god of the SQL Server. But it’s time to stop, the risk is too high.

All you need is a source control system, Redgate SQL Compare, CI/CD tool and a little time to automate it.

Image

You’re using a source control system, right?

Ok, here’s a PowerShell script to execute SQL Compare. Nothing crazy - feel free to use it as-is or adapt it for your automations.

The main idea is to use it to compare the “Release” and “Main” branches. Redgate SQL Compare comes with comparison options - tweak them as necessary, and you’ll get a migration script plus an HTML differences report.

Below is an example of how to execute the PowerShell function:

 1. (Join-Path $PSScriptRoot "functions.ps1")
 2
 3$result = New-DeploymentReport `
 4    -SqlCompareExecutable "${env:ProgramFiles(x86)}\Red Gate\SQL Compare 16\SQLCompare.exe" `
 5    -DatabaseName "X-Files" `
 6    -SourceDirectory "X:\Confidential\ReleaseBranch" `
 7    -TargetDirectory "X:\Confidential\Main" `
 8    -Options "NoDeploymentLogging,IgnoreSystemNamedConstraintNames,IgnoreUserProperties,
 9    IgnoreWhiteSpace,IgnoreWithElementOrder" `
10    -OutputDirectory "X:\Confidential\RedGateReports"
11
12if (-not $result.Success) {
13    Write-Output "Comparison failed!" -ForegroundColor Red
14}
15else {
16    Write-Output "Confidential report created at $($result.ReportPath)"
17    Write-Output "Confidential script created at $($result.ScriptPath)"
18}

To run and test it locally, download the functions script and rename it to functions.ps1. Place both scripts in the same directory, adjust the New-DeploymentReport function parameters as needed, and you’re all set.

The final step is integrating it into your CI/CD system and running the resulting migration script on your database. This step will depend on the tools you’re using. I won’t cover that part here, but this is where you can achieve full automation - not just for database deployments, but for applications and everything else.

Catch you next time!

Disclaimer: I am not affiliated with Redgate Software. The opinions expressed here are my own.

#Powershell