SQLCompare options in Change automation cmdlet behavior with dependencies

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

  DanC April 20, 2023 13:44

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

  BenT April 20, 2023 14:00

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

  DanC April 20, 2023 14:12

  - 0 +
• 
  Hi Dan,
  
  I created PowerShell scripts for the deployments using SQL Change Automation cmdlets.
  
  Regards,
  
  BenT
  
  

  BenT April 20, 2023 14:29

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

  DanC April 20, 2023 14:37

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

  BenT April 20, 2023 14:51

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

  DanC April 21, 2023 09:21

  - 0 +
• 
  Hi Dan,
  
  Ok thanks let's do it this way so.
  Waiting for the support ticket to be created.
  
  Regards
  
  Ben

  BenT April 21, 2023 10:22

  - 0 +

Add comment

Please sign in to leave a comment.