Deploying Only New Versions Of SQL Objects With Octopus Deploy

ChrisM

We have a Continuous Integration process set up which is working well with TeamCity and Octopus Deploy building and deploying updates to several test databases as changes are checked in to our code repository from our development databases.

Very occasionally we have a requirement where an object is amended in one of the test databases outside of the standard CI process for specific testing due to specific test data being in them. When this happens, those amendments are lost when the CI process runs as it sees the differences and overwrites with the version in the latest NuGet package created from source control.

Does anyone know if there is any way of stopping this behaviour, i.e. if the timestamp on the object (table, proc, function etc.) in the target database is newer than the timestamp in the source control system?

Thanks in advance for any suggestions,
Chris.

brucevanhorn2

Is there a reason why you wouldn't ultimately have someone check those amendments in? I get sometimes you need to make a quick adjustment on the fly to get a test to pass and a release out the door, but I think the answer to your question is no-you can't tell octopus / red gate to only deploy newer changes - their job is to bring the schema into sync with what's checked in. Making changes outside that cycle are problematic. Maybe if you made your spot changes in a difference schema it might work, but that doesn't make it a good idea.

ChrisM

No, there is no reason why the changes wouldn't eventually be checked in and yes, I agree that it's not a great idea. I have made the point and the request is still there. If it can't be done then it can't be done, we will have to manage this process differently.

Sergio R

If you have a test environment in which you want to make persistent changes, it shouldn't be the CI sync target - the CI sync target should be something that matches the production database so that you can see if there are any issues before creating a release from that change.

If the test environment was a release target rather than a CI sync target, then the DLMA process would stop at the drift check and request manual intervention, which would let you take the change back into source control if appropriate, or add a filter to the deployment if you just wanted the change to persist in test temporarily.