Manage SSDT project file properly with version control (*.sqlproj)

Question

We have constant problem with project XML file (*.sqlproj). If the files are added/renoved/changed location then it automatically adds/removes records in some unexpected places. After that we have big troubles by merging it when somebody changes that file also.

We came to conclusion that we might sort it before checkin. We would alphabetically sort it and in that case merge tool will understand it much better.

So, my questions would be:

1. Is it possible to re-arrange sqlproj file somehow before EVERY check-in? Maybe there are somekind of options/tools that doing that already?
2. Are there any other ways to make developers life easier?

UPDATE:

Once again I got the same problem. sqlproj file was modified 3 times and I want to merge to production only the last change, other 2 are not tested yet. in the merge tool I have the option to add all these 3 new objects or leave it without changes. I am not able to select only the last change ...

EXAMPLE:

1. developerA created tableA and checked in;
2. developerB got the latest version of dev branch, created tableB and checked in;
3. developerC got the latest version of dev branch, created tableC and checked in. DeveloperC tested the code and ready to go to production. He tries to merge his code to QA and get's the conflict where he has an option only to go with ALL changes.

Answer 1

1 - what source control are you using? No source control that I am aware of understands the context of sqlproj files but this isn't normally a problem.

2.a - This shouldn't be a problem you get constantly, are you checking in/out regularly? I would only expect to see issues if different developers are making large scale changes to the projects and not checking out / checking in before and after.

2.b - It is also possible you are not merging correctly, if you take both both sets of changes then it is normally fine.

ed

Answer 2

I understand the scenario you are running into very well. This typically happens when you have multiple work streams happening in the context of a single repository and you don't have a common promotion schedule (as in all work will go to QA at the same time and PROD at the same time).

There's a few ways I can think to get around this problem and there are pros and cons to each option.

1. Lock each environment until everything can promote together. Not realistic in most cases.

2. When you are ready to promote, create a promotion branch from source environment and take things out of the promotion branch that aren't ready to promote to destination environment. This allows devs to keep working and be able to promote without freezing.

3. Hybrid approach... Don't source control anything in Dev until it's ready to promote to test. Then either do option #1 or 2 from there onward.

4. Create a more flexible ecosystem that can spin up an environment for each Feature branch in order to demo/test with others(or at least allocate/rotate enough between the developers to accomplish the same objective). Once it's accepted promote. This is what we are working towards currently but building out the infrastructure and process when you have a ton of interconnected databases and apps that share them is a bit challenging to say the least (especially in the Microsoft world).

Anyways hopes this helps...