Getting PK error with Static data

Shawn_D

Hello!

I'm trying to implement static data deployments with our current process and running into an issue. I've got a table with a PK but on the second time of deployment I get a "Use-DlmDatabaseRelease : Applying update script failed: Violation of PRIMARY KEY constraint 'PK_NotificationType'. Cannot insert duplicate key in object". Isn't the DLM automation supposed to detect and handle that scenario?

AlexYates

You say "on the second time of deployment".

Have you tried to redeploy the same release against the same DB twice?

You should note that a DlmDatabaseRelease includes a specific upgrade script (in your case, I imagine it inserts some data). If you run the release twice it will try to run that upgrade script twice. This should be caught in the pre-deployment drift check (unless you have disabled it) and the deployment should be aborted.

If you want DLM Automation to generate a script on the fly to just make it match your source you should use the sync cmdlet instead. The idea of a DlmDatabaseRelease is that you can re-use the same deployment script in multiple environments. For example, if you wanted to do a dry-run deployment on a staging or pre-prod DB

Shawn_D

Yes. I had deployed the same package twice to the same environment. We are using the template for Octopus as a base and using the New-DlmDatabaseRelease cmdlet for this deployment.

Can you provide a link or more documentation on how to accomplish this? Based on the documentation here https://documentation.red-gate.com/sr1/deploying-static-data-with-sql-release it was my understanding that it would handle that scenario as long as the PK is set on that table.