SQL DB Build & Deploy - Bit-bucket & Jenkins

• 
  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

  MayaMalakova March 27, 2020 08:27

  - 0 +
• 
  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.

  Ken_Vitt March 27, 2020 11:57

  - 0 +
• 
  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

  MayaMalakova March 27, 2020 13:09

  - 0 +
• 
  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

  Ken_Vitt March 27, 2020 13:50

  - 0 +
• 
  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.

  MayaMalakova March 27, 2020 14:18

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

  way0utwest March 27, 2020 18:26

  - 0 +
• 
  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

  
  

  Ken_Vitt March 27, 2020 21:01

  - 0 +
• 
  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.
  

  way0utwest March 27, 2020 21:34

  - 0 +
• 
  I use git(bit-bucket).

  Ken_Vitt March 27, 2020 21:52

  - 0 +
• 
  Is there anyway someone walk thru the steps obviously something is missing.
  

  Ken_Vitt March 30, 2020 02:54

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

  David Atkinson March 30, 2020 10:44

  - 0 +
• 
  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

  Ken_Vitt March 30, 2020 12:37

  - 0 +
• 
  @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
  
  

  David Atkinson March 30, 2020 12:58

  - 0 +
• 
  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?

  Ken_Vitt March 30, 2020 14:29

  - 0 +
• 
  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.
  

  way0utwest March 30, 2020 15:40

  - 0 +
• 
  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 16:01

  - 0 +
• 

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

  Ken_Vitt March 30, 2020 21:30

  - 0 +

Add comment

Please sign in to leave a comment.