Can't create a migration script based on static data

Bas_K

Hi Guys,

I'm trying to create a migration script to run AFTER my static data has been deployed. The migration script contains a select from the static data table that is ran in the same deployment script. Redgate sees the migration script as a model change aswell, which means it's embedded in the same script. Sadly the order of scripts being ran is in my case wrong. The static data should first be inserted, after that the migration script needs to run. Is there any way I can do this?

I saw the article that this does not seem to work: https://documentation.red-gate.com/display/SOC5/Static+data+and+migrations

way0utwest

There isn't a good way to do this now. The ordering of scripts isn't something you can control. The only good way to do this is schedule two deployments, where you deploy static data changes ahead of the migration script.

May I ask what the migration script is doing?

Bas_K

Deploying in 2 seperate deployments works.. Sadly when I'm going to production the 2 changes are combined into 1 "big" deploy, which creates an error again..

I have the following tables as static data:

dbo.Permission
dbo.Action

My migration script is as followed:

INSERT INTO dbo.RoleAction
( FK_RoleId ,
FK_ActionId ,
FK_PermissionId ,
Enabled
)
VALUES ( 1000 , -- FK_RoleId - int
2028 , -- FK_ActionId - int
3340 , -- FK_PermissionId - int
1 -- Enabled - bit
)

This fails in production because the static data is not yet deployed and the migration script already runs..

Anyway to solve this?

way0utwest

This is a hack, but what I'd do for this case, which is a bit of an edge case, is cause production drift. I'd test this, but have the script executed in production first manually. Then I'd ensure my migration script is idempotent, so

if not exists( select fk_actionid from RoleAction where fk_roleid = 1000 and fk_actionid = 2028 and fk_permissionid = 3340 and enabled = 1)
INSERT INTO dbo.RoleAction
( FK_RoleId ,
FK_ActionId ,
FK_PermissionId ,
Enabled
)
VALUES ( 1000 , -- FK_RoleId - int
2028 , -- FK_ActionId - int
3340 , -- FK_PermissionId - int
1 -- Enabled - bit
)

Note, I'd be careful here with using static values. This is a place where the db development can go south. Ideally you'd insert using some other tokens, like the text or action, and let the ID values fall into place. If you're sure they're always in sync, then this can work, but usually this bites you later.

I realize this is a hack, and gets away from automation, but is this something you do often with migration scripts?

Bas_K

Meeh.. I actually need to do another query to validate that the insert statement is valid. Because Permission and Action are also static data, I need to check if the "newly" added row inside the Action and Permission table is being added. This also means I need to run the deploy twice for my production env, which is not really recommended..

Is there any other way to do this? Instead of this "hack"?

I would love to see this "fix" on the roadmap! I got like 3 manual steps now in my deployment process because of this. It really slows down our automation process.

Cheers

Sujith_Chowdari123

Have the same issue. Is there any solution for this in Redgate source control 7 version? I hope not. Also I wonder this is something many users of the product must be facing already, why isn't there a fix for this?