What are the challenges you face when working across database platforms? Take the survey
Options

SQLCompare options in Change automation cmdlet behavior with dependencies

Hello,

I'm using SQL Change Automation cmdlets to deploy script changes with SQL Compare Options to include dependencies. But sounds like when the changes script is generated, the script changes order is not taking into account dependencies.
I need to deploy a table change before a stored procedure, but into the script the stored procedure modifications are applied before table modifications which is causing deployment failure.
Is there any solution for that case?

Thanks in advance for you support,

Regards,

BenT
Tagged:

Answers

  • Options
    Hi @BenT

    Could you please confirm whether you're using a SQL Change Automation project or SQL Source Control project?

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    BenTBenT Posts: 5 New member
    Hi DanC,

    I'm using a SQL Source Control project, comparing the repo on which we are synchronizing our developments with a target database

    Thanks for your support,

    Regards,

    BenT
  • Options
    Hi @BenT

    Thank you for confirming so promptly!

    Are you using the PowerShell directly or any of the Add-ons with the cmdlets for your deployment?

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    BenTBenT Posts: 5 New member
    Hi Dan,

    I created PowerShell scripts for the deployments using SQL Change Automation cmdlets.

    Regards,

    BenT

  • Options
    Hi @BenT

    Would you mind sharing those with me to review the code?

    If you'd prefer not to share them publicly, I can reach out via a support ticket

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    BenTBenT Posts: 5 New member
    Hi Dan,
    I think there is nothing confidential in the code I already adapted from a Red-Gate employee post.
    Please find the code below:

    Database build from Source Control db folder synchronized:
    --------------------------------------------------------------------------------

    <# -------Create a build artifact from source control --------#>
    [String]$path ="$(Build.SourcesDirectory)\DataBases\EDW"
    [String]$packageVersion = "$(Build.BuildId)"
    [String]$packageID = "EDW_pkg"   

    [String]$buildArtifactPath = "$(Build.ArtifactStagingDirectory)"
       
    $path |
      New-DatabaseProjectObject | #wrap up the script and create a build artefact
       New-DatabaseBuildArtifact -PackageId $packageID -PackageVersion $packageVersion `
         -PackageDescription 'EDW scripts' | # and save it as a file
            Export-DatabaseBuildArtifact `
              -Path $buildArtifactPath


    Database deployment:
    -------------------------------

    <# -------Create a release artefact from a build package --------#>

    [String]$currentEnv =[System.Environment]::GetEnvironmentVariable('RELEASE_ENVIRONMENTNAME')
    [String]$deployDataBase_YesNo ="$(DeployDatabase_Y_N)"

    if ( $currentEnv -ne 'Development' )  #do no deploy to dev DB EDW, already synchronized
    {

    if ( $deployDataBase_YesNo.ToUpper() -eq 'Y' ) #deploy when user put "Y" (Yes) in variable
    {
    [String]$packageVersion = "$(Build.BuildId)"   
    [String]$buildArtifactPath = "$(System.ArtifactsDirectory)\$(Release.PrimaryArtifactSourceAlias)\drop\EDW_pkg.$packageVersion.nupkg"
    [String]$targeServerDB = "$(SQLConnectionString)$(SQLDataBase)"
    [String]$ReleasesPath = "$(System.ArtifactsDirectory)\$(Release.PrimaryArtifactSourceAlias)\drop\DB_Releases\EDW\$(ReleaseFolder)\$(Release.ReleaseName)"
    [String]$BuildReportPath = "C:\BusinessIntelligence\ExportReleases\$(Release.DefinitionName)\$(Release.ReleaseId)_$(Release.ReleaseName)\$(ReleaseFolder)\BuildReport.html"

    [String]$options = "IncludeDependencies,NoTransactions"


    $iReleaseArtifact=New-DatabaseReleaseArtifact   `
    -Source $buildArtifactPath    `
    -Target $targeServerDB    `
    -SQLCompareOptions $options
     
    Use-DatabaseReleaseArtifact $iReleaseArtifact    `
    -DeployTo $targeServerDB   `
    -SkipPostUpdateSchemaCheck   `
    -QueryBatchTimeout 900

    $iReleaseArtifact.ReportHtml>$BuildReportPath
    start $BuildReportPath

    }

    }



    Regards,

    Ben
  • Options
    Hi @BenT

    In this case IncludeDependencies is the default behavior of the SCA PS modules and shouldn't need to be included.

    In this case, I'm going to reach out via a support ticket as I'll need your Build Artifact and a copy of the database

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    BenTBenT Posts: 5 New member
    edited April 21, 2023 10:23AM
    Hi Dan,

    Ok thanks let's do it this way so.
    Waiting for the support ticket to be created.

    Regards

    Ben
Sign In or Register to comment.