Migration Scripting and Static Data

rasoliz

Environment:
SQL Toolbelt
SQL Source Control (VSTS Git)
VSTS Build/Release

Example Scenario:
One single database "MyDatabase"
Static table "ValidPropertyTypes"
Table "Properties" with foreign key to ValidPropertyTypes

Production Enhancement:
Add new property type "Tiny House"
Update existing properties to new property type when State = "CA"

Development:
Insert new property type, which triggers static data script change.
Create a blank migration script to update existing data to the new property type.

CI/CD
I believe, the build will succeed, because the schema validation will work without any errors, however, the actual release will fail due to the non-existent static data value in the migration script.

In order to do this deployment, we would have to create a data script using Data Compare for the static data changes. Then run a SQL Compare to generate the migrations (and any other schema changes that need to be made). Currently, DLM doesn't take this approach and instead just stuffs all of the static data at the end.

In my opinion, this is a VERY common type of migration script. How can this be handled in an automated CI/CD environment?

AlexYates

You shouldn't need to write a migration scripts. DLM Automation will do it for you.

Migration scripts are for data that are not included in your static data tables. For example if you decide to split full name into fName and lName.

rasoliz

Thank you for the response, but that doesn't answer my question to be honest. My deployment is that I will need to update X # of records to use the new static data id. So what you're saying is that DLM will need to deploy my static data changes, and then I'll have to manually provide an update script that gets executed afterwards, which means, no continuous deployment because someone has to manually do something that can be automated by use of migration scripts.

RichardL

Hi @rasoliz

Thanks for your post. 

This looks like a question that one of Support engineers will need to investigate for you.

If you've a got support contract, please send us a ticket. Provide as much information as you can - screenshots of any errors, log files etc – so we can help you as fast as possible.

If you're not covered by a Support contract at the moment, email our Sales team at sales@red-gate.com, and they'll be able to help.