What are the challenges you face when working across database platforms? Take the survey

Migration Scripting and Static Data

rasolizrasoliz Posts: 3 New member
edited March 1, 2018 8:13PM in DLM Automation
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"

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

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?


  • Options
    AlexYatesAlexYates Posts: 264 Rose Gold 2
    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.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • Options
    rasolizrasoliz Posts: 3 New member
    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.

  • Options
    RichardLRichardL Posts: 417 Gold 4

    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.

    Customer Support
    Redgate Software
Sign In or Register to comment.