Combining SQL Source Control with SQL Change Automation

alesadvz

I am looking into using SQL Source Control to manage our database schema changes. 

In the future we would also like to use SQL Change Automation as we would like to be able to build the database as a Nuget package in our CI and use the Nuget package to deploy the changes.

After evaluating both for a while it seems like we would need to follow a workflow like this to use both:

1. Make changes on the dev DB using SQL Source Control and commit them

2. Then separately generate migration script in SQL Change Automation project and commit and push them

3. CI builds Nuget package which is then deployed

The above approach is a bit cumbersome so I am wondering is, if there is a better way to somehow use the two tools together? As said above we would like to use Nuget packages to deploy DB changes. 

AlexYates

SQL Change Automation has two main components:

1. A Visual Studio Extension (previously called ReadyRoll) which is an alternative format for source controlling your database changes to SQL Source Control. The Visual Studio extension creates a project based on migration scripts rather than a desired end state (like SQL Source Control). When you deploy this package against a target database only the required scripts are run. This sounds a bit like what you want.
<a rel="nofollow" href="https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/sql-change-automation-projects" title="Link: https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/sql-change-automation-projects">https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/sql-change-automation-projects</a>

2. A set of PowerShell cmdlets that can be used to automate the deployment of either a SQL Source Control project or a SQL Change Automation Visual Studio Extension project. If deploying a SQL Source Control project you can create a "DatabaseReleaseArtifact" which contains, amongst other things, a generated upgrade script that can be used to deploy a given version from source control to a given target database. This upgrade script can be eyeballed, tested and documented in advance however you see fit before deployment. This also sounds a bit like what you want:
<a rel="nofollow" href="https://documentation.red-gate.com/sca3/reference/powershell-cmdlets/new-databasereleaseartifact" title="Link: https://documentation.red-gate.com/sca3/reference/powershell-cmdlets/new-databasereleaseartifact">https://documentation.red-gate.com/sca3/reference/powershell-cmdlets/new-databasereleaseartifact</a>

So using either SQL Source Control or SQL Change Automation Visual Studio Extension project types for development, combined with the SQL Change Automation PowerShell cmdlets for deployment, you can generate your upgrade scripts in advance, store them however you wish and automate deployment. The question is whether you like the SQL Source Control or the SQL Change Automation Visual Studio Extension project type for development and the way each gets deployed by the PowerShell cmdlets.

Please correct me if I'm wrong, but I think you are asking to combine the two project types because you prefer the development experience of SQL Source Control, dealing with desired end states in SSMS, but you prefer the SQL Change Automation Visual Studio Extension project format for deployment, and you are looking for a best of both worlds solution.

My advice is that you are making life more complicated for yourself by combining both project types. If I were you I would accept that there a pros and cons of either project format, but that either is better than a complicated hybrid of the two. The folks at Redgate would urge you to consider switching to the SQL Change Automation Visual Studio Extension project type for CI and CD use cases - but to make that switch you need to consider whether you are happy to drop SQL Source Control.

If you would like to spend 30 minutes discussing the pros and cons in more detail let me know. We offer a free 30 minute consultation to everyone. After that we can discuss rates if that's what you want.

<code><a rel="nofollow" href="http://www.dlmconsultants.com">www.dlmconsultants.com</a>

alesadvz

Thanks Alex. That was the info I was looking for

AlexYates

No probs. Good luck and happy deployments!

alesadvz

For future visitors here:

Following the link to PS Cmdlets I ended up with this, which demonstrates the concept.

The first script builds a nuget package from your SQL Source Control scripts

# Path to SQL Source Control scripts
$project = "C:\dev\Sandbox\MyDbProject\"
$validatedProject = $project | Invoke-DatabaseBuild
$buildArtifact = $validatedProject | New-DatabaseBuildArtifact -PackageId MyDatabase -PackageVersion 1.1.0
$buildArtifact | Export-DatabaseBuildArtifact -Path "C:\dev\Sandbox\MyDbProject\"

And this one deploys nuget package built in previous step to target db

$buildArtifactPath = "C:\dev\Sandbox\MyDbProject\MyDatabase.1.1.0.nupkg"
$buildArtifact = $buildArtifactPath | Import-DatabaseBuildArtifact

# The below line uses windows authentication, if you want to connect with SQL user append these params: -Username "user" -Password "pwd"
$production = New-DatabaseConnection -ServerInstance "." -Database "DeployDB" 

$update = New-DatabaseReleaseArtifact -Source $buildArtifact -Target $production

Use-DatabaseReleaseArtifact $update -DeployTo $production