Abandon using migration scripts
We have tables that we cannot version the data. Therefor, Migration Scripts are the SQL Source Control way to update and insert data in these tables.
Unfortunately there are too many limitations in Migration Scripts for them to be usable for us. Below are a few.
1. There is no way to mark a migration script to be re-run
* There is the option to edit a migration script but the edits will not be re-run. This is problematic for Agile teams and also when using CI.
2. There is no convenient way to control or change when migration scripts are run
* Developers can't easily create a fresh database using SQL Source Control because it always wants to run migration scripts before all the dependent tables are created. Even though the tables were added to SQL Source Control long before the migration scripts were created, the tool for some reason tries, and fails, to understand each migration script's dependencies and run it as soon as possible instead of after all the objects are created.
3. SQL Source Control is not flexible to the different ways developers use source control and crashes regularly when using the Migration Script feature
* Instead of creating a new folder for every branch, some of our developers use a single folder and swap out the code each time they check out a new branch in git. This causes SQL Source Control to crash and behave inconsistently.
Because of issues like the ones listed above we have decided SQL Source Control Migration Scripts are unusable for us.
We have abandoned using SQL Source Control Migrations Scripts and decided to use SQL Source Control only for versioned schema and data. All custom scripts will now be managed outside of the Red Gate tools. Our automated deployments will package the script generated by Redgate DLM along with the custom scripts we create.
Has anyone else had to go this route? Did you experience any drawbacks doing this? Is there a better workaround?