Octopus Database deployment issue surrounding constraints - Guidance needed

• 
  It looks like you are using static data and the changes caused by that static data would cause a Foreign Key to be violated: the table with static data is a parent in Foreign Key relation and one of the values that you are attempting to delete exists in the child table.
  
  You need to either adjust your static data so that it doesn't drop that value or adjust the data in the child table.
  

  Sergio R January 08, 2019 20:14

  - 0 +
• 
  Also if you are not worried about static data changes and want to prioritize any schema changes, you can use the option to Ignore Static Data, which should enable you to deploy any pending schema changes but will, of course, ignore all static data changes.
  

  Sergio R January 08, 2019 20:20

  - 0 +
• 
  Thanks Sergio. The DBA team just went in and edited the RedgateDatabaseInfo.xml file. They updated the DataFileSet elements count to 2 from 0. They also added the qty 2 DataFile sql statements inside this "DataFileSet" element. We re-built and deployed with success. 
  
  Is their any reasoning why this would fix it?

  casterlight January 08, 2019 20:29

  - 0 +
• 
  The behavior around this changed a few months ago, what version of DLM Automation\SQL Change Automation Powershell are you using?

  Sergio R January 08, 2019 20:36

  - 0 +
• 
  DLM Automation 2.0.32.1199
  

  casterlight January 08, 2019 20:38

  - 0 +
• 
  I believe that on that version, if you have a data file in the data folder, and that file is not listed in the xml then all data on the target will be dropped when you deploy.
  
  I am not sure how you got in that situation, this usually only happens if the xml or the data folder were changed manually.
  
  Anyway this will not be an issue anymore if you upgrade: this behavior changed in version 13.6.0 of the SQL Compare engine which was merged into SQL Change Automation Powershell 3.0.7 (DLM Automation's new name).
  
  From SQL Compare version 13.6.0's release notes :
  

  • The DataFileSet node of the RedGateDatabaseInfo.xml file in a scripts folder has been deprecated and no longer affects static data deployment

  Please note that if you have a SQL Toolbelt license with non-expired Support and Upgrades, you can upgrade free of charge from DLM Automation to SQL Change Automation Powershell.
  
  Upgrading from DLM Automation 2.0.32 to SCA Powershell is just a case of updating the templates in Octopus Deploy and installing SCA Powershell (if the tentacles are able to connect to the Powershell Gallery, you won't even need to install SCA Powershell, since the template downloads and installs the latest version automatically). The process is similar for any other add-ons that you might be using.
  
  

  Sergio R January 08, 2019 21:07

  - 0 +
• 
  Thanks Sergio. Great feedback. From the RedGate documentation, I see that using SCA requires a different build template in TFS also which is expected but it requests a path to the SQL Change Automation project file (.sqlproj). The DLM Template points to the root path of the Database so this is different. Their is no solution or sqlproj file type available since this has been our standard up to this point. 

  casterlight January 09, 2019 16:56

  - 0 +
• 
  SCA supports 2 types of projects: SQL Change Automation and SQL Source Control Projects.
  
  SQL Source Control Projects are the project type that you are using and weare the only project type supported by DLM Automation 2.
  
  The reference to .sqlproj only applies to SQL Change Automation Projects, which are created with SCA Visual Studio (a Visual Studio add-on) rather than SQL Source Control.
  
  The build template is very similar, any changes exist to accommodate SQL Change Automation projects and to allow you to lock to a particular version of the tool (otherwise by default it will attempt to download and use the latest available version).
  
  SQL Source Control Projects (which is what you are using) work the same way they did in DLM Automation 2, and you don't need to change anything in the project (scripts folder in this case).
  
  If you select SQL Source Control Project as the type of operation you will find that's very similar to DLM A 2:
  https://documentation.red-gate.com/sca3/automating-database-changes/add-ons/visual-studio-team-services/use-the-vsts-build-extension-with-sql-source-control-project
  
  
  
  

  Sergio R January 09, 2019 17:09

  - 0 +
• 
  Tellem to give you a raise Sergio. Always a pleasure. Thanks for your expertise.

  casterlight January 09, 2019 18:21

  - 0 +

Add comment

Please sign in to leave a comment.