Using Git on a shared development model

buinauskas · October 25, 2017 11:21AM

I'm in a discussion with my team about one of our databases versioning. We currently have a shared development database where all changes committed to TFS. We'd like to migrate to Git and I'm advocating to move into a dedicated development model because that's how Git is designed to work. My team wants to migrate to Git and keep shared development model and I'm against that.

What would be strong arguments not to use Git in a shared development model?

Mike U · October 28, 2017 7:56PM

SQL Source Control does not support the shared model when using Git because we agree with you - it doesn't make sense to use a shared development database with a distributed version control system. The shared model relies on having a 1:1 mapping between the shared database and a single central repository version (like in SVN or TFS).

LeeRobinson · July 21, 2022 10:12PM

to create multiple test databases we would also have to create multiple program environments, link them to the databases, keep the test data updated. The overhead in time, diskspace, servers and effort would be enormous, just to make a small change in a stored procedure.

enoch · October 25, 2017 9:35PM

Git support in SQL Source Control is poor. TFS Git doesn't work and we had issues with "Get Latest" trying to resurrect old changes. Redgate has ignored Git bugs and focused on TFVC, even 6 is still broken.

buinauskas · October 26, 2017 6:57AM

@enoch I know it's poor. But I'm asking about using Git (doesn't matter whether it's GitHub, Gitlab, TFS Git) on shared development model.

LeeRobinson · July 21, 2022 7:30PM

I have not seen any reasonable explanations of problems using shared databases. When I use Git with my C# code, I can see the difference between my local repository and the central one. Is this missing in SQL Source Control? Did they decide that this comparison was not necessary? The repositories are stored independently of the actual database. I do not see the disconnect here. The example given in "Using Shared Mode with Git" indicates that SQL Source Control is lacking the ability to compare a stored procedure in the database I have open to the stored procedure in the central repository. That does not make sense to me. I am using Git with my C# code and I can see the comparison there. Why would Red Gate decide not to implement something as essential as that?

David Atkinson · July 21, 2022 9:03PM

The problem with git and shared is that multiple users can only too easily commit the same set of files, which means the last one to push will get a spurious merge conflict. This can be avoided with a lot of discipline, if users can guarantee that they will only ever save their own changes.

LeeRobinson · July 21, 2022 9:09PM

I find it odd that it does not happen with program source code. The commits are separate and have to be merged. "Commits" are something that happens in the repositories, not in the database.

David Atkinson · July 21, 2022 9:13PM

The difference is that SQL Source Control manages the files for you. So you end up with two identical files that have been saved by different people and there's actually no difference between the two, so it's a no-op merge, which is very confusing for end users. Many database professionals are still relatively new to git and this behavior is highly confusing. The analogy doesn't exist with app code as there's never a case when a change is saved to the working folder that doesn't "belong" to the user.

LeeRobinson · July 21, 2022 9:17PM

Manages what files? That does not make any sense to me. Which two identical files? We are talking about database objects, not files.

LeeRobinson · July 21, 2022 9:19PM

All of the "working folders" that I know about belong to the user. Which working files are shared?

David Atkinson · July 21, 2022 9:56PM

SQL Source Control creates CREATE scripts for each of the objects that have been changed and saves these to the project. So that this point they are files. So user A will save a change to ObjectX and so might user B and user C because they are all seeing the same change as they are sharing a dev DB. So you end up with n identical files in n users' projects which are subsequently committed and pushed. Each push will result in an apparent merge conflict.

David Atkinson · July 21, 2022 9:59PM

That's not to say the tooling couldn't anticipate this. ie, if the tool does the push, it can detect the merge conflict, and could in theory take it upon itself to "check" whether it's a real merge conflict or just one where lots of users have ended up inadvertently saving the same change. But at this point the tool would be overriding standard git behavior in order to compensate for the shared database scenario which git hasn't been designed to accommodate.

LeeRobinson · July 21, 2022 10:03PM

The exact same situation could arise with my C#. Since it is less likely I guess it was decided that the tools would ignore it. I don't see how it overrides standard Git behavior since standard Git behavior does not handle that situation. It would be an extension to make up for a deficiency in Git.

David Atkinson · July 21, 2022 10:06PM

Another potential way round this is building in a mechanism to stop users from saving/committing each other's changes. We could use a DDL trigger or other techniques to identify who last made a change and prevent other users from saving these changes. Personally I think this method has the most legs, but we ended up prioritising the dedicated database workflow as this is what we think database developers should be striving for. That's not to say we won't revisit this in Flyway Desktop, which is a ground-up rewrite of SQL Source Control, but it will come after we've nailed the dedicated database workflows. Out of interest what is preventing your developers using LocalDB or a local SQL Server Developer Edition as a dedicated dev instance?

David Atkinson · July 21, 2022 10:11PM

Yes, it is arguably a deficiency in git. I'm not sure how it could happen with C# as changes come from the end user alone, so any merge conflict is legitimate, and not a false positive. The shared database scenario is different to this as database changes to UserA's local git clone can come from other users.

David Atkinson · July 21, 2022 10:20PM

Understood. I think it's fair that we'll not be able to avoid the shared dev scenario, so watch this space. Bear in mind that additional features in this area would most likely be added to Flyway Desktop, so it might be worth familiarising yourself with this. Flyway Desktop has a more robust git support so it could be that with some discipline (ie devs being told not to save changes they didn't make) this could work out for you. Do let us know how you get on.

LeeRobinson · July 21, 2022 10:22PM

So with SQL Source Control, how does it determine when there has been a change? Does it look at the date and time that the tool created the script file? If so, that seems to be a little short-sited since the change is not happening in the file but rather in the database. If the change is determined by looking in the database, how could two identical files be committed?

LeeRobinson · July 21, 2022 10:24PM

My answer to your previous question does not appear to have posted. We don't see how we can use local databases without spending more time setting them up and maintaining them than we spend making the on the actual database maintenance. To create multiple test databases we would also have to create multiple program environments, link them to the databases, keep the test data updated. The overhead in time, diskspace, servers and effort would be enormous, just to make a small change in a stored procedure.

David Atkinson · July 22, 2022 12:46PM

SQL Source Control runs SQL Compare under the hood to compare the state of the database with the state of the project (the folder of CREATE scripts on disk within your "project"). It doesn't use the dates - instead there's a cached version of the previous state saved in a temp folder that allows us to do a 3-way comparison to determine whether the change should move left to right or right to left.

VBJV · October 5, 2023 11:54AM

I know I'm late to the party, but my two cents here:
My team is now moving from TFVC to Git, because it's the defacto standard source control provider just about everywhere. We have SQL Source Control through the SQL Toolbelt. So we will be "trying" to develop database changes using Git and a Shared DB model.

I agree with @LeeRobinson that the cost (in disk space (close to 2TB of data), effort, and time) to have dedicated development SQL Server environments setup locally precludes us from diving deep into that "distributed" model for DB development.

@David Atkinson you mentioned the Flyway Desktop project. What's frustrating is that Flyway, if truly a rewrite of SQL Source Control, is now a separate cost. Selling that to my IT procurement team would be next to impossible. I love the Redgate tools, but me and my team are in the minority. So there's no path forward here to use Flyway. That means we are "stuck" using Git on SQL Source Control and developing in-house processes to make sure we don't do something stupid, or going back to TFVC for our database code.

LeeRobinson · October 5, 2023 1:41PM

We decided to use ApexSQL Source Control. It is fully implemented using the shared development environment. It is working nicely, but, as with Git for program code, it takes some training and practice.

David Atkinson · October 6, 2023 11:16AM

@VBJV - Flyway Desktop is indeed a rewrite of SQL Source Control and although it's a separate SKU today, the intention is to provide customers with a natural transition to Flyway Teams. This is likely to be something we have in place in H1 2024. If this is something you'd like to look at earlier, and in particular that Flyway Desktop has been optimize for use with Git, please contact us and we'll see what options are available. https://www.red-gate.com/our-company/contact-us

Using Git on a shared development model

Best Answers

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk