Octopus Database deployment issue surrounding constraints - Guidance needed

casterlight

The below error is received after the update script tries to execute as part of the "Redgate - Deploy from Package" step process in Octopus deploy. This is the last step to run. 

The update script that is created as part of the deploy step cannot work correctly due to those constraints. 

Applying update script to database 'Log' on server '####.####'.

December 21st 2018 13:55:57

Info

WARNING: The error 'The DELETE statement conflicted with the REFERENCE constraint "FK_Hit_Usage_Area". The conflict

December 21st 2018 13:55:57

Info

occurred in database "log", table "dbo.PM_CANDIDATE", column 'PM_USAGE_AREA_ID'.

December 21st 2018 13:55:57

Info

Delete 15 rows from [dbo].[REF_PM_USAGE_AREA]' occurred when executing the following SQL:

December 21st 2018 13:55:57

Info

PRINT(N'Delete 15 rows from [dbo].[REF_PM_USAGE_AREA]')

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 1

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 2

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 3

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 4

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 5

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 6

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 7

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 8

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 9

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 10

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 11

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 12

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 13

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 14

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 15

December 21st 2018 13:55:57

Info

PRINT(N'Delete 3 rows from [dbo].[REF_PM_RESULT_TYPE]')

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_RESULT_TYPE] WHERE [PM_RESULT_TYPE_ID] = 1

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_RESULT_TYPE] WHERE [PM_RESULT_TYPE_ID] = 2

December 21st 2018 13:55:57

Info

DELETE FROM [dbo].[REF_PM_RESULT_TYPE] WHERE [PM_RESULT_TYPE_ID] = 3

December 21st 2018 13:55:57

Info

COMMIT TRANSACTION

December 21st 2018 13:55:57

Info

The database update failed

December 21st 2018 13:55:57

Error

Use-DlmDatabaseRelease : Applying update script failed: The DELETE statement conflicted with the REFERENCE constraint

December 21st 2018 13:55:57

Error

"FK_Hit_Usage_Area". The conflict occurred in database "log", table "dbo.PM_CANDIDATE", column 'PM_USAGE_AREA_ID'.

December 21st 2018 13:55:57

Error

Delete 15 rows from [dbo].[REF_PM_USAGE_AREA]

December 21st 2018 13:55:57

Error

Local parameters [connectionString = Data Source=######.#####\####;Initial Catalog=Log;Integrated

December 21st 2018 13:55:57

Error

Security=True;Application Name="Redgate DLM Automation"]

Sergio R

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

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.

casterlight

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? 

Sergio R

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

casterlight

DLM Automation 2.0.32.1199

Sergio R

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.

casterlight

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. 

Sergio R

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

casterlight

Tellem to give you a raise Sergio. Always a pleasure. Thanks for your expertise.