How to address resolve merge conflicts in the .sqlproj <DeployChangesExecutionOrder> line?

Shaggy

Scenario:

Consider the following sequential timeline of events:

1. Dev A - creates branch (BR1) from master.
2. Dev B - creates branch (BR2) from master.
3. Dev  B - creates a stored procedure in BR2, generates a migration script, commits locally, and pushes to origin.
4. Dev  B - does a pull request to successfully merge changes to master. - Yay!
   
   >> Thus, master is now different from when Dev A created branch BR1. <<
   >> Branch BR2 activity is complete. <<
   
   
5. Dev  A - creates a stored procedures in BR1, generates a migration script, commits, pushes, etc.
6. Dev  A - is presented with a merge conflict when attempting the pull request on the <DeployChangesExecutionOrder> line in the .sqlproj file.

Issue: This line in the .sqlproj file was modified to include the new procedure created by Dev B.  The inbound .sqlproj file for Dev A doesn't include Dev B's procedure.

Questions:

1. What is the best way to address the merge conflict?
   
   
2. Is there a way to eliminate the <DeployChangesExecutionOrder> line from the .sqlproj file?

Thanks in advance for your support.

Kendra_Little

Hi @Shaggy ,

Sorry for the delay on this one.

For your second question, if there is a way to remove DeployChangesExecutionOrder, I am not knowledgeable about this one and am going to defer this one to other team members. 

For your first question, the best way to deal with the merge conflict, here's what I recommend:

A) In general it's good for DevA and DevB to regularly merge to their branches from trunk (or where-ever they are eventually going to merge TO), if their branches will be updated on a regular basis. This way they can be aware of other changes as they happen and not have to find out about them all at the end.

It's also best for both DevA and DevB to do that merge again right before they create the pull request. That way they can review the conflict locally and handle it. 

C) If there's a race condition and someone merges in right before you, the Pull Request will notify you of the conflict as you mention. In this case I'd go back to the local repo, handle the conflict, and re-push.

For handling the conflict locally, if you're using the SSMS plug-in, we recommend the free VSCode as a merge tool if you don't already have a favorite.

To handle the conflict:

• In the trunk branch, pull changes
• Change to the branch in question (in this case BR1)
• Merge changes from trunk into BR1 -- in the example where trunk is named master: git merge master
• Open the sqlproj file with the conflict and merge the changes, in this case we would accept both (image below)
• Stage and commit
• Push

Hope this helps,
Kendra

Pete_Ruiz

Hello @Shaggy

I've escalated this to our developers who provided the following comments to your questions.

1. What is the best way to address the merge conflict?

If they have <EvaluateExecutionOrderOnImport> set to True, then they can remove the <DeployChangesExecutionOrder> line completely, and re-open the project, which should then
result in it being regenerated. Although they should only do this if they haven't manually altered the deployment order.
Alternatively, if they are using a manually ordered deployment order then they'll have to resolve the conflict manually.

2. Is there a way to eliminate the <DeployChangesExecutionOrder> line from the .sqlproj file?

Currently, if it isn't present in the .sqlproj file, then SCA should auto-generate it, which means it's not possible to have a project without this line at all.
However, the line is still valuable, as changes can still be made that may require a different ordering.

I hope this is helpful. Please let me know if you have further questions.

Kind regards,

Pete Ruiz

Pete_Ruiz

Hello @Shaggy

Thanks for reaching out and sorry that you're having trouble with SQL Change Automation. I'm going to escalate this to our developers to see what is the best way to approach this. I'll get back to you with further instructions.

Kind regards,

Pete Ruiz

Shaggy

Thanks Kendra & Pete.  The combo of your responses illustrates how we can effectively deal with the scenario.  Going forward do you recommend the SCA/migrations approach or the SOC/state approach when multiple developers (3-6) are actively working in the database project?

Note: We've been using the SOC/state approach for 2-years, and we are currently evaluating SCA/migrations on a greenfield project.  Kicking the tires so to speak before converting.

Pete_Ruiz

Hello @Shaggy ,

We don't recommend either approach based off the size of the team but rather the type of changes you're making to the database. If you're making DDL related changes with very minimal table refactoring, then SOC/state should suffice. If you're making DML or data related changes and doing lots of table refactoring then SCA/migrations would make more sense.

You can read more about this in the documentation below.

https://documentation.red-gate.com/sca/getting-started/about-sql-change-automation/how-does-sql-change-automation-work

I hops this helps. Please let me know if you have any further questions.

Kind regards,

Pete Ruiz

cjoneslib

I know this thread is a little old. I want to add that this is the single largest pain point in using SCA. We have a team of 6 developers and the race conditions are constant, which causes a backlog in getting to done. Dev A, B, C, and D have code all passed in code review and don't realize they are about to have conflicts. All 4 are ready for testing. QA 1 tests Dev A and QA 2 tests Dev B, do all the testing, Acceptance is completed first on Dev A's card and it gets merged. Now Dev B, C, D have to deal with the aftermath. In the meantime, Dev E has submitted a new change and we are in a hot mess. This is actively happening and it's super painful to get the cards moved through the process. Every migration script is a conflict if more than one dev has a change.

How could this possibly scale on larger teams?

Shaggy

Thanks for sharing your experience cjoneslib.  We abandoned our POC of this product in Q1.  Reverted back to SQL Source Control for all database repos.  I've been stalking the release notes every month to see if the product changes to address our issues.  Our team would really like to use it due to all of the other benefits.  However, this issue is a blocking obstacle for us.

Kendra_Little

Hi @cjoneslib and @Shaggy,

Thank you for the feedback on this. 

The structure of the project file within the existing implementation of SQL Change Automation is pretty integral to the implementation, and this would be a tough thing for us to change for existing implementations/projects.

We are currently working on previews of our next-generation tooling for Database DevOps, and the solution will provide the ability to deploy via migrations without needing to track a list of scripts inside a project configuration file. 

Would you be interested in joining our research program as we develop this  tool? If so, please drop our team a note at databasedevops@red-gate.com.

Cheers,
Kendra

cjoneslib

As much as I'd love to assist, I unfortunately don't have the availability.

Kendra_Little

Thanks @cjoneslib, totally understand. Thank you for the feedback in any case, it's really useful for us to know these things.

ducman

A little bit late to the game but I'm surprised no one mention in the previous version of SCA (probably 3) that this line contains line breaks between each programmable object whereas in the latest version all programmable objects are all in one line (pipe separated). This makes it impossible to merge if you have a lot of programmable objects versus the diff tool easily can tell the changes if each programmable object were in their own line.

I would still say that a user doing the merge can still be flawed so there for the answer by @Pete_Ruiz
to remove or leave the line empty and have SCA re-generate the order is the best answer (there is a button to generate in the project property screen) for my use.

No way are we going to go back to SQL Source Control, ReadyRoll (older SCA name) was the biggest win we came across when we went to Build2018.

Fergy

HAs there been an update on this? Conflict are still constant