Branching in Git with SQL Database Projects


By:   |   Updated: 2020-09-29   |   Comments   |   Related: More > Database Design


Problem

As a SQL Server Database Developer, I need to understand and implement the concept of Dev branch in Git source control especially when multiple developers have to work on the same branch in a state based development.  How can I accomplish this with Git?

Solution

The solution is to build, refactor, test, deploy and manage your database code with SQL Server Data Tools (SSDT) using Git source control by following standard practices of branching in Git to be observed by all the members (multiple developers) of the team.

About Dev Branching in Git

Git is one of the most widely used source control applications in the programming world today which has built-in support for multiple branching and it is flexible enough to cover the needs of multiple teams with multiple goals.

On one side, the SQL Database Project offered by SQL Server Data Tools (SSDT) can help you to implement state based development, on the other side Git can make it easier to manage and deploy the database code changes to any environment including Dev, Test and Production.

Please go through the article SQL Database Project Code Branching in Git to get a know-how of Git source control and state based development if you are not already familiar with these concepts.

What is a Dev Branch?

A Dev branch is a branch of the source code which is normally created from the master branch in the beginning of the project so that any new (database) code changes by the developer(s) should be done here first before they find their way to the master (origin) branch.

A Dev branch usually named as "dev" and at the minimum the following two branches of code are expected for a healthy database project with few exceptions.

  1. Master (master)
  2. Dev (dev)

Why a Dev Branch is created?

A dev branch is created to protect direct changes to the master branch because the master branch holds production ready code and any direct changes by the developers can be very confusing.

The dev branch takes care of all the development work which is in progress (by the developers) so that they can save their changes to this branch whenever they would like to.

How many developers can work on a Dev Branch?

There is no limit on the number of the (database) developers working on the dev branch unless an organization or a team decides to limit it for any legitimate reason.

How can two developers work on a Dev Branch?

Two or more developers can comfortably work on a dev branch because each developer is going to have his own copy of the complete source code extracted from master.

How can a SQL Database Project be managed via Git?

A SQL database project can be easily managed through Git source control as long as the team follows the standard practices of Git.

How can the changes by one developer be visible to others?

The most interesting question is how two developers working independently on their requirements using their own version of dev branch can see the changes of each other in their dev tools such as SQL Server Data Tools (SSDT) and in fact this question was actually asked by one of our community members in the comments and formed the basis for this tip.

How can we replicate two developers working on their Dev Branch?

The most important question, though, is how we can replicate the scenario where two developers are working on their local copies of the source code? This is the question along with the one before that which we are going to answer with the help of a completely implementable walkthrough in this tip.

Git Dev Branch in use by Multiple Database Developers

Let us now go through the steps of creating test environment for two developers responsible for two different changes working on their exclusive version of dev branch but able to see all the recent changes once they are preserved (saved).

Please go through the reference article SQL Database Project Code Branching in Git to see the details of the initial steps in the upcoming walkthrough beginning from setting up an Azure DevOps project.

Create an Azure DevOps Project

Please go to the Azure DevOps website and create an Azure DevOps project called SQL Database Project Dev branching in Git after signing in to your account as follows:

Azure DevOps Project created successfully

Rename the Repo (repository)

The Git repo (repository) will have the same name as the project, but it is better to shorten the repo name as database-dev-branching:

Renaming the Repo as database-dev-branching

Connect to the DevOps Project in Visual Studio

Choose the option to create a copy of the repo in visual studio as follows:

Creating local copy of the repo

Click Connect once you see the following window pointing to the Azure DevOps repo of the project:

Connect to the project repo Git repository

You are now connected to the Azure DevOps project through master branch.

Putting blank Database Project into the source control (master branch)

Please remember in real time scenarios no changes should be saved directly to the master branch but in this tip, we are assuming that only a blank SQL database project is saved in the master branch.

Now that we are connected to the master branch of the repo it is time to create a new SQL database project under a new solution.

In the Team Explorer, click on New… under Solutions to create a new solution as follows:

Creating a new database solution

Create a new SQL Database project WatchesDevBranching under Git Dev Branching Solution as follows:

Creating SQL Server Project

Once you see the SQL Database project WatchesDevBranching has been created please click the pen icon in the status bar or alternatively press shortcut key CTRL+ALT+F7:

Unsaved changes

Have a quick look before saving these changes and write initial commit in the comments box of the changes before committing them to the remote master branch of the repo:

Changes to be saved to the master branch

Select Commit All and Push from the drop down and observe all the changes getting pushed to the master branch of the remote repository successfully as shown the figure below:

Database code has been saved to the master branch

Now we have prepared a suitable environment for this project to be shared across multiple developers to observe how different changes done by these developers find their way to the dev branch and become visible to everyone even in their development tools despite the fact that each developer works on his/her copy of the (database source) code.

Replicating multiple developers working on the same project

Let us assume that we have two database developers Asif and Peter who are tasked to do different changes against the same SQL database project.

We can replicate or test create the scenario where two developers are working on the same project in the following ways:

  1. Create two different copies of the source code for each developer
  2. Use two different instances of the Visual Studio to imitate both developers working on the same project

Create local copy of the repo for Asif (database developer)

Go back to the Repos section of the Azure DevOps online project (web portal) we created earlier and click a new local copy of the repo as shown below:

Creating a new local copy of the repo

Since we are creating this copy for one of the developers whose name is Asif, we can straight away name this local copy as asif-database-dev-branching as shown below:

Creating asif-database-dev-branching repo

The local copy of the repo is ready to be used by the developer Asif:

Developer Asif

Create local copy of the repo for Peter (database developer)

Using the same method which we used for the first developer (Asif) we can create another local copy for the database developer Peter from the Azure DevOps website and name it as peter-database-dev-branching:

Create local copy of the repo for Peter

Once the local copy is created successfully, we may have to manually add this repo in the local repos folder accessible via Visual Studio.

To add this repo in the local repos please click the active local repo (which is asif-database-dev-branching in our case) and then click Add option under Local Git Repositories.

Type the name of your local copy as peter-database-dev-branching after ensuring the path to the Git local repos folder is correct or simply click the ellipses (three dots) next to the input box and locate the local copy of the repo in the repos local folder (created by default).

Finally click Add button at the bottom of the input box where you typed the name of the repo.

This is illustrated as follows:

Adding new local repo for the developer Peter

Once the copy of the code is added to the local Git repositories please search for it and click it:

Local copy of the repo created for the developer Peter

The SQL Database Project for the other developer (Peter) is ready to be used now:

The local project copy for Peter is ready

Creating dev branch from master for each of the developers

Since both copies of the code are ready to be used by the developers, as per standard practices, we need to create a dev branch where they can begin their development journey without affecting the master branch which should not be directly updated.

Please click the master branch in the status bar and then click New Branch… followed by entering branch name dev and clicking Create Branch as follows:

Creating dev branch from master to begin work

Use above method for both developers to create dev branch in their local copy of the repo.

Each developer's local version of the project should look like as follows:

Each developerís local version of the project

Create and Save Watch Table in the Database Project managed by Asif

Now we are going to add and save a new database object to the SQL database project (copy of the repo) managed by the developer Asif.

Right click on the WatchesDevBranching project and then click Add > Table followed by inputting Watch as table name:

Adding Watch table to the project by Asif

Click the pen icon to save the changes:

Unsaved changes

Add the comments "Watch table added" and choose Commit and Push to push all the changes into the Git source control:

Push all the changes into the Git

The changes have been successfully pushed to the online dev branch of the remote repository:

Watch table saved successfully

Please go to the Azure DevOps web portal to see the saved changes to the dev branch:

Azure DevOps Web Portal

Sync Changes for other developer Peter

If we look at Peter's copy of the code then it is clear that he has no idea what Asif has added to the project.

Please switch to the other instance of Visual Studio which shows us the copy of the code handled by the developer Peter:

Switching to Peter

Click on dev branch and then click Manage Branches:

Manage Branches

Right click on master branch under remotes/origin node followed by clicking Fetch:

Fetching changes from remote dev

As a result of the above action you are going to see the dev branch under remotes/origin node which is the remote dev where both developers are going to push their changes.

However, we know that Asif has already saved a new table Watch in the dev branch but this change is not visible in the local dev branch copy of the repo which Peter is using.

To see all the changes by other developers we need to merge changes from the remote dev into the local dev for Peter.

Right click on dev (local) branch and then select Merge from… as shown below:

Fetching changes from remote dev

Select origin/dev to merge from it and click Merge as follows:

Merging from remote dev

So, finally the Watch table created by the developer Asif can be seen by the developer Peter who is working on his local copy of the code (SQL Database Project) but can merge anytime to see the most recent changes in the dev branch.

This is illustrated as follows:

Watch table is visible in Peter

Congratulation, we have successfully demonstrated how two developers working on their local copies of the database code can see each other's changes even in their dev tools, plus it is a good habit to keep on synching the changes with the remote branch so that you know what's going on apart from the changes you are doing to the project.

Next Steps
  • Please try adding a new table WatchType in the project code managed by Peter and see if you can make that change visible in the project code in use by Asif
  • Please try to add another developer in this simulation and see how you can sync the code among all the three developers this time


Last Updated: 2020-09-29


get scripts

next tip button



About the author
MSSQLTips author Haroon Ashraf

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

View all my tips






Comments For This Article





download





Recommended Reading

Find and Remove Duplicate Rows from a SQL Server Table

Working with SQL Server Extended Properties

Surrogate Key vs Natural Key Differences and When to Use in SQL Server

What is a GUID in SQL Server

SQL Server Database Diagram Tool in Management Studio








get free sql tips
agree to terms