A few questions from an individual who started using the product when it was ReadyRoll

wculler

Hello all.

Looking for some insight into what appears to be some changes since some of the later versions of SQL Change Automation.

When we first started building a POC for our CI/CD pipeline, the product was ReadyRoll.

We would push our changes to Git, trigger an automatic build in Jenkins via a post-commit hook to generate a Nuget package using MSBuild and then push that package to OctopusDeploy while also having that package deployed to our DEV environment using Octo.exe as the last step in the build process in Jenkins.

We would then push that Nuget package to QA, STAG, PROD through OctopusDeploy.

Over time, we have upgraded to SQL Change Automation and validated that it worked pretty much the same way regardless of the introduction of the Powershell modules.

We have picked up the POC again and after upgrading to the latest version of SQL Change Automation and we have noticed a few things that are different.

1.  Running a MSBuild step in Jenkins doesn't appear to work any longer.  An error occurs for Could not load file or assembly CredentialManagement.
2.  Using MSBuild with the right parameters, we could generate the change report, and if I recall correctly, it would be included in the Nuget package.

I switched to the Jenkins build templates that utilize the Powershell modules to get around the issue with MSBuild, but it appears that in order to generate the change report, you have to create a release.

Something that stood out to everyone about creating a release is that a compare appears to be initiated against the target database to create a deploy script and change report.

If I remember correctly, previously, the deploy script would be in the Nuget package and OctopusDeploy would apply those changes as they existed in that package.  We deploy changes out of order and all over the place so we used feature branches to ensure that only the targeted changes were deployed.

Everyone is just a little wary of what appears to be a compare after compare when promoting to the next environment so we wanted to find out a little more about why this might have changed.

Thanks for any insight.

Tianjiao_Li

Hi @wculler

The error for Could not load file or assembly CredentialManagement was caused by a bug which we've fixed. Could you upgrade to the latest version if not already and try again?

With regards to the change report, I'll run that past the development team and let you know!

Tianjiao_Li

So with the old ReadyRoll build process, it was possible to specify a target database at build time. In this case, we would generate a deployment preview report by comparing a snapshot of the project state with the snapshot (if it exists) in the target database.
This didn't make much sense unless that database was also the database which would be deployed to,

With SCA we fully separated the concept of a build from that of a release.
In a build there is no target database, and we just run validation on the project scripts (and also generate the package script)
In a release we generate a release artifact comprising a targeted deployment script and a series of reports against a particular database.

In terms of your concerns about the comparisons:
SQL Change Automation does not perform any comparisons using SQL Compare at build or deploy time for SCA projects for generating any deployment scripts.
During a build, a snapshot of the current project state is generated from the temporary database which is deployed to. The deploy preview report is generated by comparing that state to the snapshot stored in the __SchemaSnapshot table in the target database.

In general, we would recommend users to deploy using the targeted deployment script instead of using the package script.

wculler

Thanks for getting back to me.

I'll test MSBuild again with the latest update.  Now that I've tried out the RedGate template steps in Jenkins that use the Powershell modules, we'll probably just stick to using those.  It seems to make more sense to do so and is much easier.

Yes.  That's exactly right.  We would specify a target database during the build.  This is what we termed our gold copy of the database that was just used for comparisons.

Ok.  So it sounds like including the RedGate template step for OctopusDeploy "Create Release from Package" is a good idea regardless of passing the create-release command from Octo.exe in Jenkins for the auto deployment to the first environment, in our case, DEV.

I'll go over this with our team and do some more testing.

Thanks again.