SQL DB Build & Deploy - Bit-bucket & Jenkins

Ken_Vitt · March 26, 2020 10:24PM

Database - SQL Server
Version control - GIT/Bit bucket
Automation - Jenkins pipeline.

Question/task - I need to build sql database project & deploy thru jenkins pipeline.
Currently we manually build & publish the database but i have scripts which i can use to build(dacpac) & publish to the database but problem scripts only work in VS(visual studio) command prompt.

Build SQL database project thru Jenkins - Is it possible, if so how ?
Publish SQL database project thru Jenkins - Is it possible, If so how.

Please help me understand the process involved.
Thanks!

MayaMalakova · March 27, 2020 8:27AM

Hi @Ken_Vitt ,

You can use the RedGate SQL Change Automation Jenkins plugin in your deployment pipeline to do that.

Here is an example Jenkins configuration file - https://documentation.red-gate.com/sca/deploying-database-changes/add-ons/jenkins/example-jenkins-ci-cd-pipeline

Does this help?

Regards,
Maya

Ken_Vitt · March 27, 2020 11:57AM

So do i need to install SQL Change Automation tool separately or just a plugin in Jenkins will do the work?
For instance there is MSBuild plugin available within Jenkins. So trying to check if that's all i need or separate software SQL Change Automation tool needed. If we need to install Red-gate SQL Change Automation as a tool then i have to deal with compliance issues. Anyway let me know.

MayaMalakova · March 27, 2020 1:09PM

Hi @Ken_Vitt
You don't need to install SQL Change Automation with the Jenkins plugin. The agent will automatically install the SCA PowerShell module from the Powershell gallery on each run.

Regards,
Maya

Ken_Vitt · March 27, 2020 1:50PM

What's the guarantee that Powershell gallery will have SCA Powershell module. I see only thing we have is PowerShell Studio 2019 on software app store and i'm not sure if SCA Powershell module is available in Powershell Studio 2019

MayaMalakova · March 27, 2020 2:18PM

Maybe we are talking about different things?
I mean this module https://www.powershellgallery.com/packages/SqlChangeAutomation/4.2.20085.17694. It is available online and the agent will download and install it on each run.

way0utwest · March 27, 2020 6:26PM

I have a walkthrough of some of this in Jenkins as well: https://voiceofthedba.com/tag/jenkins/

Ken_Vitt · March 27, 2020 9:01PM

Thanks Maya & WayOutwest. I realized we do have the SCA plugin on Jenkins and followed the article however i got an error trying to build the DB.
Here is the error msg

Invoke-DlmDatabaseSchemaValidation : The SQL Change Automation project folder 
does not contain a single .sqlproj file.

+ CategoryInfo          : ReadError:  [Invoke-DatabaseBuild], TerminatingException
    + FullyQualifiedErrorId : CanNotLocateASingleSqlProjFile,RedGate.Versionin 
   g.Automation.PowerShell.Commands.InvokeDatabaseBuildCommand

way0utwest · March 27, 2020 9:34PM

You have some pathing error here. This isn't the main project folder if there isn't the SCA project inside. If you are using SQL Source control, then you have the step mis-configured. You need the action to be a build for a SQL Source Control project.

Ken_Vitt · March 27, 2020 9:52PM

I use git(bit-bucket).

Ken_Vitt · March 30, 2020 2:54AM

Is there anyway someone walk thru the steps obviously something is missing.

David Atkinson · March 30, 2020 10:44AM

@Ken_Vitt - just to check - is your preference to use conventional Jenkins Jobs or to use a Jenkinsfile (pipeline as code)?

Ken_Vitt · March 30, 2020 12:37PM

I don't think i have a preference but i started of with conventional jenkins job here. I can convert job to pipeline as well but i need directions as i'm struck missing on some vital pieces. Thanks

David Atkinson · March 30, 2020 12:58PM

@Ken_Vitt - it's generally a matter of organisational preference. Pipelines as code are becoming increasingly popular so it's worth considering if you're starting out afresh. The idea is that the pipeline configuration itself is part of what is versioned, so it's very easy to move the pipeline to different Jenkins environments, and also it means that changes to your pipeline trigger a new build, which makes sense because this could be a change that needs testing. If you do choose to use a pipeline as code, here's a sample Jenkinsfile you can adapt for your purposes: https://documentation.red-gate.com/sca/deploying-database-changes/add-ons/jenkins/example-jenkins-ci-cd-pipeline

Ken_Vitt · March 30, 2020 2:29PM

I looked at it and it's very generic. Me being new to database deployments with jenkins automation tool is getting little difficult. I think i need more help. Red-gate support provide any technical support services?

way0utwest · March 30, 2020 3:40PM

We don't really offer support here for Jenkins. The thing to note is that Jenkins does a git clone to get this repo to your build agent spot. The pathing should be relative to the root of your git repo.

Ken_Vitt · March 30, 2020 4:01PM

Thanks - I was able to clone the repo perfectly fine. 1. I think i miss during nuget packageID maybe. How would i get this nuget packageID - is it the name of the database or do i need to do anything special? 2. Create temporary DBServer SQL localDB, where do i need to create this temporary DBServer SQL localDB ? or this gets created automatic on windows agent? - I think answers to these would be the key to get my dacpac file maybe?

Ken_Vitt · March 30, 2020 9:30PM

Invoke-DlmDatabaseSchemaValidation : The SQL Change Automation project folder 
does not contain a single .sqlproj file.

SQL DB Build & Deploy - Bit-bucket & Jenkins

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk