SQL Database Project with SQL Server Data Tools and GIT


By:   |   Updated: 2020-10-19   |   Comments   |   Related: More > Database Design


Building Better Data Models

Free MSSQLTips Webinar: Building Better Data Models

Learn how to build data models from concept to actual model and what you should know when building a model. We will also look at how to take the logical model to the physical model to be implemented in SQL Server.


Problem

As an entry level database developer, I would like to create and manage SQL databases using a simplified source control approach especially when working as a solo developer.

Solution

The solution is to start your development journey with SQL Database Project using SQL Server Data Tools (SSDT) for Visual Studio along with using Git source control centralized workflow.

Centralized Workflow and SQL Database Project

This tip is primarily focused on using SQL Database Project to build and manage databases and a simpler approach to put your database code under source control provided the developer is familiar with database development and source control concepts.

There are many ways to create SQL databases and using SQL Server Data Tools (SSDT) is one of them. Similarly, there are many patterns which can be used with Git source control and centralized workflow is one of them. Let us get some more understanding of this workflow before we implement it in a walkthrough.

What is a Centralized Workflow?

A centralized workflow is a simple way of saving database code changes to a central repository which serves as single source of truth.

Why we use Centralized Workflow?

A centralized workflow is easy to use and manage and often preferable for the projects migrated from a centralized source control system to Git until the development team behind the projects is ready to utilize the full potential of Git source control.

When is Centralized Workflow most Preferable?

When a single developer is working on a database project then centralized workflow is a safe and flexible option with some exceptions.

However, that does not mean a team of 2-3 developers cannot use it, but they have to be aware of the pros and cons.

How is Centralized Workflow Implemented?

A centralized workflow is implemented by using a single branch of code until completion. Since only one developer or a very small team of 2-3 developers work on a single branch of code it is relatively easier to avoid conflicts when releasing the final code.

For example, when you connect your SQL database project to an Azure DevOps Git repository you start working on a local version of master branch which is used to save the very first changes along with any new changes to the database project to the remote master branch until the project is complete.

In other words, you work on a local version of the master branch and you save your changes to the remote version and the story continues.

SQL Database Project with Centralized Workflow (Git)

Is Centralized Workflow the way forward?

We can say, No. Centralize workflow is not the best way forward, but an excellent source control approach to begin with until you are fully ready to leverage the fully featured Git source control.

On the flip side we can say, Yes. It can be the way forward if a team is comfortable with this approach, but they may have to understand the caveats (precautions) that come up with this approach. It also depends on their willingness to consider the flexibility and benefits of other approaches fully supported by Git.

At the end of the day it depends on many factors including the development team itself, their level of expertise, the business requirements and preferences.

Using SQL Database Project with Centralized Workflow

Let us get hands-on experience of working with centralized workflow to save database project changes.

Pre-Requisites

This tip assumes that the readers are interested in following the walkthrough are familiar with the following technologies:

  1. Azure DevOps
  2. SQL Database Project
  3. Git source control

Create Azure DevOps Project

Please sign up to create a new Azure DevOps account if you don’t have one and are interested to implement this example.

Please sign in to your online Azure DevOps account and create Azure DevOps project named SQL Database Project with Centralized Workflow:

Azure DevOps Project

Rename Repository

Please navigate to the Repository section of Azure DevOps and rename the current repository as sql-centralized-workflow:

Renaming repo

Now if we click on Branches under Repos from the left navigation bar, we need to select the option Switch to the default sql-centralized-workflow repository to switch to the repository which has just been renamed:

Switch to the default sql-centralized-workflow repository

Create Local Copy of the Repository

Next, you have to create a local copy of the repository to start working locally by clicking the following options:

Create Local Copy of the repo

Please follow the instructions to complete the process:

Create Local Copy of the repo

Connect to the Repository

Please click on the sql-centralized-workflow repository in the status bar in Visual Studio as follows:

Connect to the repo

Right click on the repository and click Connect as shown below:

Connect to the repo

Things before creating a new SQL Database Project

Once you are connected to the repository this means you are now using a local copy of the master branch either to create a dev branch out of it or straight away start working on the master branch.

In a centralized workflow you are going to carry on with master branch provided you know that all the database changes are directly going to be pushed to the only main steam branch which is responsible for development, testing and release. This may look like a code safety hazard, but it is fine to do so especially when you are the only developer working on this project and aware of the merits and demerits of this approach.

Create a new SQL Database Project

Time to create a new SQL database project so please click New… under solutions:

Create a new SQL Database Project

Please create a new SQL database project named WatchesCW under a solution called Centralized Workflow and click on the Create button:

Configure SQL Database Project

Please switch to the Solution Explorer to the see the newly created SQL Database Project:

Newly created SQL Database Project

Add first database object

We need to add a database object to the project now.

Right click on the project (WatchesCW) and then click Add followed by clicking Table… as shown below:

Adding new table

Create a new table called Customer leaving it to be created with default settings which means a primary key column ID is also going to be there:

Creating Customer Table

Commit (Save) Changes to the Git Source Control

We have just created the first database object so please Build the project by clicking F5 to get the green signal (and keep it a habit) before saving your changes to the source control:

Build successful

Next, click the pen icon in the status bar or use the shortcut key CTRL+ALT+F7 to open Changes section.

Add the comments "Customer table added" and select Commit All and Push to save the changes to the remote repository:

Pushing changes to the remote repo

Once all the changes are successfully saved, the Team Explorer window should be showing the following messages:

SQL Database Project saved successfully

Add second database object

Let us now add another table called Supplier using the default settings as shown below:

Adding Supplier table

Commit (Save) Second Database Object to the Git Source Control

Please push the new database code changes just like the way we did earlier adding the comments "Supplier table added" and selecting "Commit All and Push":

Pushing Second Database Object

If we switch to the Solution Explorer, we can see our database solution with both tables and padlock icons next to them showing they are protected by the source control:

Database Solution with latest changes

Git Repository Check

Let us now navigate to the Azure DevOps project we created earlier to see how these changes are maintained by the Git source control.

Go to Repos > Pushes from the left navigation bar to see all the changes we saved directly to the master branch in a historical order as shown below:

Repos Pushes View

Similarly go to Commits under Repos to view the Graph of changes (commits) which is simple and straight without any complications:

Commits Graph

Finally, please navigate to the Files under Repos to view the complete database code:

Database (Project) code

Congratulations, you have successfully implemented centralized workflow using Git source control for SQL Database Projects. This is an excellent approach to begin with and to be carried on as long as your requirements are simple and linear (one after the other) and there are no chances of team conflicts because either you are the only developer working on these projects (using centralized workflow) or (if working in a team) your team is aware of the pros and cons of this approach and are happy to continue with it.

Next Steps





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



Article Last Updated: 2020-10-19

Comments For This Article





download














get free sql tips
agree to terms