What are the challenges you face when working across database platforms? Take the survey

Best Practices for SCA and git branching

Hello RG Peeps,

I've read a few articles and forum posts on using SCA and git branching.  Notably, @way0utwest and @Kendra_Little have shared good ideas on this topic.  I wanted to start another post to catch up on the latest ideas around this.

I see a lot of conversation that leads away from more complex "git flow" style (main/develop) flows and leans towards more MS-embraced "release flow" styles - work off of master, make branches for topics/features and releases.  First question, is, "Is that still the case?"

Quick Side note - I am currently working with Azure DevOps for CI, and Azure SQL DB (with private and public endpoints) for deployment targets.  Using git repos within Azure Devops (not guthub).  I am also using "classic pipelines" rather than YAML-based at this point. Open to moving in the YAML direction, tho.  We have 3 BI developers/architects actively working on a DW DB using SCA (VS-plugin).

I have also read some good forum posts where Steve chimed in and reiterates (I will paraphrase) about how "DB src is different due to importance or Chronology" and "feature branches should not be long-lived".  I'd also like to discuss how we might address the first idea in order to perhaps mitigate the importance of the second.  This conversation leads me towards ideas about tracking more dependency metadata about DB changes so we can intelligently make decisions about the following scenario.

Here's the gist of what I'd like to solve (or at least discuss):

1) Developer A makes changes - feature1.  This set of changes makes its way into the dev integration environment.

2) Developer B makes changes - feature 2.  This set of changes makes its way into the dev integration environment, too - after feature 1.

3) We decide that we want feature 2 to go to prod, but not feature 1.

Obviously, if feature 2 relies on schema changes in feature 1, we have an issue.  That's where I see dependency metadata coming in - some way to say, "Hey - you can't do that".  That's more of a long term strategic discussion, tho.

Let's say, for the sake of argument, that we know that feature2 is schema-isolated and does NOT depend on feature1.  Please help me understand the git/azure devops (PR) branching flow that would occur.




  • Options
    Also, if I wanted to complicate this already complicated discussion, I would add in the possibilities of both the SQL Clone for branch switching and the Hybrid SSC/SCA model.
  • Options
    Hi Peter,

    This is a really great question, and I think we can develop some good content around this concept, and maybe even go a little farther.

    I think there are a couple of clarifying questions that might help me get my head around the best way to think through this.

    1) How many total environments are in the mix outside of production? In other words, after the integration environment, is there a QA or Staging environment that things get deployed to before production?

    The reason I ask is that if we don't have another environment in the pipeline before production, then it may become more important to "reset" the integration environment and then only deploy feature 2 to it. (Even if the two things are feature isolated, the application might have some sort of dependency we wouldn't find without doing this.)

    2) My second question was if we had the option to use something like Clone or a snapshotting tool to reset the environments in the scenario. It sounds like we do in this case, so I will pull that into consideration.

    3) Are you working primarily with SCA in  Visual Studio or SSMS? If it's SSMS, then I'm curious if you've updated the plugin since Sept 23rd -- there was an update in that release which helps with removing migration scripts in terms of the schema model. (Visual Studio already had the ability to handle deletions of migration scripts.)  

    Thanks very much for the thoughtful question and looking forward to working through this scenario more on Monday. Have a great weekend.


  • Options
    Thanks, Kendar.  Stoked to have you engage in this thread.

    To answer your questions...

    1) At this point, we only have a production and dev integration environment (both Azure SQL DBs).  As I dive into this, I'm considering adding some new ones to test releases/deployments - esp a stage before prod.  Not sure if short-lived (even containers) might come into play.  I do you hear you in regards to "resetting" the dev int env.

    2) We have done a little with RG SQL Clone, but aren't using it on a reg basis at this point.  I'm willing to consider options here.  I also want to be cognizant of other devs with perhaps a lower tolerance for complexity and new tools.  I do see value here, tho.

    3) We are using VS SCA.  We started with SSMS, but found it lacking in several areas.  Plus our devs were actually more comfy with VS as they do a fair amount of SSIS dev.

    PS On a side note, I did play around some with a demo project n repo yesterday.  I used a develop and main branch (git-flowy) config.  Was able to get feature branches to CI build, but not release/deploy anywhere.  PR into develop would CI build + release and deploy to a dev integration DB.  PR into main does same for "prod".  

    I'm heading to the mountains with my wife shortly.  Catch you next week.  Thanks for all you do!

  • Options
    Hi Peter,

    Thanks for the extra info.

    As you're off to the mountains, I think I am going to collect up info as I go and post it back in one piece, rather than putting in a load of smaller updates which might become quite disjointed between big picture things and more detailed things. Putting myself a reminder in to make sure I update here this Friday with what I have together at that point.

    Have a really good holiday!

  • Options
    We are back. :) I did have some disjointed visions of schema version and dependency tracking capabilities during my hike in the Sangres.  The visions included using some blend of git commit messages and hashes along with the built in dependency metadata in SQL Server...along with stamping version paths for schema objects - tables n column - possibly in SCA metadata tables.  Did I mention disjointed?
  • Options

    I've posted a blog thinking through lots of different options for this here: https://www.red-gate.com/simple-talk/blogs/reordering-deployments-in-database-devops/

    It got long enough that a post makes sense.

    Hope this helps, and happy to dig into things in greater detail and address other scenarios if that would be helpful!

Sign In or Register to comment.