How can we help you today? How can we help you today?

SQL DB Build & Deploy - Bit-bucket & Jenkins

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!

Ken_Vitt
0

Comments

17 comments

  • MayaMalakova
    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
    0
  • Ken_Vitt
    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
    0
  • MayaMalakova
    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
    0
  • Ken_Vitt
    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
    0
  • MayaMalakova
    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
    0
  • way0utwest
    I have a walkthrough of some of this in Jenkins as well: https://voiceofthedba.com/tag/jenkins/
    way0utwest
    0
  • Ken_Vitt
    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
    0
  • way0utwest
    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
    0
  • Ken_Vitt
    I use git(bit-bucket).
    Ken_Vitt
    0
  • Ken_Vitt
    Is there anyway someone walk thru the steps obviously something is missing.
    Ken_Vitt
    0
  • David Atkinson
    @Ken_Vitt - just to check - is your preference to use conventional Jenkins Jobs or to use a Jenkinsfile (pipeline as code)?

    David Atkinson
    0
  • Ken_Vitt
    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
    0
  • David Atkinson
    @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
    0
  • Ken_Vitt
    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
    0
  • way0utwest
    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
    0
  • Ken_Vitt
    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
    0
  • Ken_Vitt
    Invoke-DlmDatabaseSchemaValidation : The SQL Change Automation project folder 
    does not contain a single .sqlproj file.
    
    Ken_Vitt
    0

Add comment

Please sign in to leave a comment.