SCA - Correct order for pull, commit, etc

PeterDanielsCRB

We've got some developers that made larger changes (schema and programmable objects) to their local DBs.  We got one dev's changes into source (git repo in Azure Devops), buildable, and pushed to the remote repo.  Then, the other dev, who had changes in his local DB, but NO migrations (or programmable objects) generated in the git working tree, pulled the changes down...and it was messy.  The docs here:

https://documentation.red-gate.com/sca/developing-databases/working-with-the-ssms-add-in/using-version-control-in-ssms/using-git-to-share-your-changes-in-ssms

Say:

"If another member on your team has pushed their commits you will need to use the Pull button to get the changes locally. In order to pull, you should not have any uncommitted changes. If you have uncommitted changes you can:

• Finish the changes you are working on and create a commit
• Temporarily Stash your changes using the 'git stash' command in the command line (advanced)"

Since there were no changed files in the working dir, we thought it was safe to pull and apply first.  But it was not.

Should we have generated migrations (and added/committed them) for the 2nd dev's changes BEFORE pulling the 1st dev's work and trying to apply it?

Thank you,

-Peter

DanC

Hi @PeterDanielsCRB

Ah no worries that's okay, one of the developers came up with one scenario in which you could face an issue:

- User A creates table 'Foo' on their development database.
- User B creates table 'Foo' on their development database, generates a migration, then pushes the change.
- User A pulls the change from user B, which causes a problem as table 'Foo' already exists.

Gear to hear you're having a smoother experience with the process and new strategy!

Thanks for the justification towards your move with VS, I think VS naturally lends itself towards being a better source control experience due to it's nature, but I'll be sure to feed that back to the team! 

Going forward with this particular issue, should we perhaps revisit it if you experience a similar issue?

Alex B

Hi Peter,

Perhaps I'm misunderstanding the scenario, but if Dev2 had some changes but there were no changed files in the working directory - doesn't that mean they had not committed those changes?  Or maybe a better question is, compared to what were the files not changed - the actual files, the local git repository, or the remote repository?

From your last sentence question, it sounds like you may not have committed the changes to the actual files to the local git clone, which is what it says to do.

Kind regards,
Alex

PeterDanielsCRB

Thanks, @Alex B.  I think I'm looking for clarity in the language about changes in the dev sandbox DB vs actual migrations created and saved in eth working directory vs adding them to git staging vs committing to git.

In our situation, there were changes in the dev's local sandbox DB, but no migrations created for them (and therfore no "uncommitted changes" from a src perepctive.  When we pulled the other dev's migrations from remote repo and applied to DB, things broke.   We ended up renaming his local dev DB, rebuilding from src, and then using SQL Compare to bring his changes back into the new local DB.

PeterDanielsCRB

@Alex B, Wondering if you might have some follow-up advice here.  I'm still a little confused about what to do when dev 1 has some changes in their local dev DB (not yet as migrations or updated programmable objects), and we have dev 2 that has committed and pushed.

Should dev 1:

1. pull and attempt to apply dev 2's changes?
2. Or - generate migrations/updated programmable objects and commit them before trying to pull and apply dev 2's changes?

Alex B

Hey Peter, sorry I'm trying to get a good/clear answer for you after having had a bit of discussion on the topic and it's just taking a bit longer than anticipated.  I'll let you know as soon as I have more clarity on this.

Also, were any of the objects that had been changed in his local dev db also modified by the changes that were pulled in?  What was the nature of the mess that ensued that you mentioned?

PeterDanielsCRB

Thanks, @Alex B - I don't mean to rush you and certainly appreciate your help.

I'm afraid I don't have detailed info on the type of "merge" conflict that arose.  i just have a vague recollection of a jumble of broken, pulled migrations when we tried to "apply to database" along with some perhaps unexpected "generate migrations".  We wound up rebuilding his local dev DB from source (with the pulled code from dev2), then cherry-picking items from his old (renamed) local dev DB using SQL Compare.  Finally, generating migrations for his changes.

On a related topic, I'm attempting to nudge the team towards an actual branching strategy, esp after reading @Kendra_Little 's awesome posts on the subject:

https://www.red-gate.com/blog/the-managers-guide-to-git-training-for-database-administrators?_ga=2.61671084.1604002557.1594680128-1689274135.1575913803&_gac=1.53677786.1591031064.CjwKCAjwztL2BRATEiwAvnALcinme8ckxPkFvrk69gYOI3ZGUujOzsawLl796KZjdWO82PZk8Ah8xBoCjY0QAvD_BwE

Our team is not git-strong at this point.  I would also say that the SSMS SCA plugin's git integration is not optimal.   But, that is a topic for a different thread.

DanC

Hi @PeterDanielsCRB

I don't think it will be related to what you've mentioned your latest response, also Kendra's resources are really grest and helpful for people of all levels!

If possible could we check a couple more things from you if that's okay?

So if possible, we would need to know if there was any changes your VCS tab in SSMS, including files external to the SQLProj (read me files etc)

Also if you could verify the following:

• You're not making any changes to your sqlproj.user file
• Assuring that you have both users using the same development on the local machine

If not you can add this to your .gitignore file manually in your repository so it's not accidentally committed (sqlproj.user file). 

PeterDanielsCRB

Thanks, @DanC.

The actual event is now quickly becoming a blurry memory.  I can't recall if the dev's VSC tab showed other files as coming through via pull.

We had not mad any mods to sqlproj.user at that time, but we have since - to ensure that we use localhost (dev edition compat 150) instead of a sql localDB instance (compat 130).

I'm not clearly undretanding your last question: "Assuring that you have both users using the same development on the local machine".  Assuming you mean "Assuring that you have both users using the same development DB on the local machine".  No - each dev uses their own sandbox dev DB on their laptops.

And yes, gitignore has .sqlproj.user:

# -----------------------------------------------------------------

# .gitignore for SQL Change Automation

# -----------------------------------------------------------------

bin/

obj/

/*.dbmdl

/*.ifm

/*.sqlproj.user

DanC

Hi @PeterDanielsCRB

Thanks for the update, I was in the process of double checking with the team about the scenario for your environment. 

However they're currently stuck on a large bug and unable to reply currently, however we will hopefully be able to reply as soon as possible!

DanC

Hi @PeterDanielsCRB

May I ask, in your original post you mentioned the following at the end " pulled the changes down...and it was messy" if possible could you clarify what you meant by the results being messy?

This would help us identify would cause of caused the issue!

PeterDanielsCRB

Hi @DanC

Well, now it's going to be messy AND fuzzy - since I don't recall the exact details. I just recall that the pulled changes couldn't be applied since there were other changes in the dev's local DB (not yet in migrations) that conflicted.  We also looked at trying to make migrations, but that, too...was messy.  Apologies.  I'm wishing I had a better record of the situation.

In related news, we are seeing fewer issue overall.  I think the devs are getting more familiar with the process and tend to "pull and apply" before making new changes.  I'm also heading them towards a branching strategy.

Lastly, we are moving the team to the VS SCA plugin for a number of reasons.  The primary one being a more familiar source control interaction, but also a more evolved SCA experience overall.  I wanted to work with the SSMS one, but it's just not "ripe" yet.

PeterDanielsCRB

Apologies for the delay.  Yes, let's table this one and revisit if it rears up again.  Thank you!