Abandon using migration scripts

moesother

We use the latest version of SQL Source Control 5 and the DLM tools for automated deployments

Our scenario

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.

Summary

Because of issues like the ones listed above we have decided SQL Source Control Migration Scripts are unusable for us.

Workaround

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.

Question

Has anyone else had to go this route? Did you experience any drawbacks doing this? Is there a better workaround?

Sergio R

Hi,

I am sorry that your experience with the new migration scripts hasn't been satisfactory.

Please see http://documentation.red-gate.com/displ ... ntMetadata: by changing the records on this table you can have some control of when scripts are deployed

There's a webinar tomorrow that covers branching in Git with SQL Source Control. If you are unable to attend the recording should be available soon in our video library.

Regarding the crashes etc., if you would like assistance with those please contact us at support@red-gate.com .

Thank you,

mzamora

Hi,

I also have several limitations and crashes while using migration scripts. In one ocassion a script failed to complete but during the execution process a table was dropped, not sure how that could happen.

I have been using RedGate products since quite a while and I am in general quite happy but I stopped trusting this migration scripts component. I am also moving to have the scripts ran manually. It would be an excellent feature to use but I will wait until future releases. Hopefully they manage to fix the bugs.

Best Regards,

moesother

Sergio, from what I understand, that table does not control the timing of when migration scripts are ran. Certainly, you can delete records in that table which would cause the migration scripts to be ran again, but that's not the issue here. What I'd like to hear about is why migration scripts are ran so early in the script, and not wait until after all the required DB objects are created. Especially when the db objects existed in the db long before the scripts were created.

mzamora, I agree. It would be a nice feature once it's working.

As a developer, I can tell some issues can be fixed easily enough with just more robust coding. It's clear the tool loads the source controlled information into memory and then never properly rechecks if anything has changed since the initial load. This causes all kinds of random errors. We have completely lost migration scripts because of crashes from this problem and had to start from scratch.

There are other issues that are more fundamental. Having read most of the documentation it seems that the Migration Scripts feature is focused on edge cases like, for example, working with new nullable columns where user intervention is required. If you read between the lines it seems like the message is that Red Gate does not have a solution yet for including the variety of scripts that get run on environments in a normal enterprise. If you don't believe that try to create a new database using Source Control 5 when you have a number of migration scripts and watch the mayhem ensue!

CraigEddy

We are also giving up on using SQL Source Control migration scripts.

Constantly running into issues with the scripts taking forever to construct in SQL Compare, if they get generated at all.

The most common inexplicable error is something about "2 conflicts prevent checkout" while generating a deployment script in SQL Compare.

We have made a heavy investment in time trying to troubleshoot issues with these scripts, all in the hope of one day automating our database deployments. But with so much manual intervention required in order to just get the scripts and their associated files to even be processed by SQL Compare, automation seems impossible.

Thanks,
Craig Eddy

annieluxton

Hi there,

I'd like to add my two cents regarding migration scripts. We also have had a huge amount of trouble with migration scripts. A lot of our problems seemed to stem from the fact that our DB schema wasn't setting explicit constraint names for PK, FK and default constraints, so we had to write special SQL to try and build up DROP statements for these elements. We've fixed this now so I expected our problems to go away, but it still seems to be an issue in the 'Validate database state' SQL CI step which runs from TeamCity upon each build.

Even after having explicitly set constraint names, the 'Validate database state' SQL CI step which we run from TeamCity fails often when we are modifying a table that requires dropping constraints (e.g. modifying an index requires any FKs defined on it's columns to be dropped and recreated), saying it cannot find the system named constraint. This makes sense, because when we wrote those migration scripts, we weren't setting constraint names. But the puzzling thing is that the 'Validate database state' process first wants to apply all changes (compare blocks) from the migration scripts. I believe it does this because the 'scratch' database it creates to compare the scripts folder to has no records in the RedGateLocal.DeploymentMetadata table, therefore it seems all migration scripts as needing to be run. I have tried adding /Options:IgnoreMigrationScripts to the TeamCity plugin but it does not seem to stop this from happening. I can see that by default, it always adds /Options:UseMigrationsV2 as well.

I do understand that this 'Validate database state' is trying to recreate the entire DB from scratch using the DB schema in the scripts, including old migration scripts, but I can also see how this can end up in situations where it just won't work anymore because the old migration scripts and/or the 'compare blocks' referred to elements that no long exist or clash with more recent changes, etc. Perhaps this is one of the reasons why migration scripts seem to be so damaging or hard to use, because they leave behind these 'snapshotted' compare blocks that just make no sense further down the line.

The only work-around I can see is to delete all the migration scripts we've used. Doing this lets the 'Validate database state' pass without errors.

Because of these sorts of issues, we have not used migration scripts for months now and have opted to have another process for deploying migration scripts instead - much like moesother described. We only use SQL Source Control for schema changes.

Finally, I'm sad to say that myself and my team have wasted COUNTLESS days on this and in all honesty, I really did expect more from such an expensive product. I would be very interested in hearing if anyone else has experienced the same issues as I've described above, or come to the same conclusions, and whether there's a work around.

swinghouse

Unfortunately, we are also seriously considering abandoning SQL Source Control migration scripts. We were very excited when this feature was announced, but it has given us lots of pain and cost us a lot of time.

We really wanted this to work but alas...

saulcruz

Same here, while migration scripts have been very useful to protect our data in our target environments when making structural changes, they're causing way more issues in our jenkins build schema validation, for some reason schema validation fails when looking at old migration scripts, and while I know I can Ignore Migration Scripts, I would really would like to validate the schema of the migration scripts that have not been deployed yet. So far I don't know how to do this when using:

Invoke-DlmDatabaseSchemaValidation
Any ideas or thoughts?