Working with SSDT and GIT: Best practices

This article is intended to help people new to SSDT and relatively new to git, comprised of two separate yet somewhat related parts.

As a .net developer, writing code has never been easier with all the tools and the great community, most of us are used to the SQL Server > ASP.NET MVC > HTML + CSS + JavaScript stack.

This stack always plays well with version control, git or TFS, especially with the power of visual studio, the greatest IDE on the planet IMHO.

When embarking on a new project, when deciding on the database level, the majority pick a code-first approach for many reasons, be it the ease of defining your types and mapping them to the database (with a simple migration command), not having to care much about the database layer, the ability to use fluent api for that much extra customizability of your types or even ease of unit testing and dependency injection, this option is good for the majority of development.

But there comes the occasional need for writing your own SQL scripts, and that presents a problem with versioning.

SSDT comes to the rescue...

Short for Microsoft SQL Server Data Tools, SSDT makes it easier to maintain a repository of all your sql script changes.

Basically you would have is a project inside your solution, containing different folders keeping your different sql entities, which looks something like this:

You can create it with right-clicking on your solution, "Add" > "New project" and search for "SQL Server Database Project".

Configuring this project is outside of the scope of this article, please visit this Microsoft doc for more info:

So now that we have our project set-up, we now need to add our ".sql" files in their respective folders.

At this point, assuming that you are comfortable using git for source control, let's commit the code and push it to our remote repo, and let's assume this is the current and latest version now for the sake of this article.

That was a quick introduction to SSDT, now starts our journey for how to actually work with SSDT.

Say that you have this task of adding a table to your database and doing some changes to a couple of stored procedures, you are used to go do these changes to your local database, copy that script and paste somewhere for safekeeping, then this script is managed by someone else who does all the migrations (maybe you do that), we want to get rid of that model. That's when we see the power of SSDT.

So let's start by defining what we need to have a good process:

1. Your solution containing your logic, sql server database project (SSDT) and connection.
2. A development database (used only for development).
3. A staging/ testing database. (Used for shipping and testing your scripts, this is key to mimic the actual live environment, you should never apply manual scripts to this database)

Now for our task to make changes to the database, let's go ahead and write that script in the dev database, execute it and make sure everything is working. At this point i will assume you have completed also the work on the application layer, meaning you have generated all your models through the .edmx file, you have written your logic and the work is ready.

Here comes the most important part: have all your modified scripts copied to their respective sql files in SSDT under tables folder (since this is a new table, if this was an old table that you had edited, you will only edit the sql file for this table to reflect the same changes you did) and under the stored procedures folder, and save everything.

At this point, the new table file is now new under source control, and the stored procedures files are changed, great.

Now is the time to test if this works, right click on your SSDT project, and click on "Schema Compare".

Now the source is selected as this SSDT project, we want to target the staging/testing database, so go ahead and select target as this database.

Now click "Compare". This will make a schema comparison between what you have in this SSDT project and the staging database, and it would get back all the changes between these two schemas.

After it is finished, you have the option to compare each entity from the SSDT project to its counter-part in the staging database, and you have the option to un-select entities that you do not want to apply the updates to, check the following image for a visual reference:

Remember, the only changes that you should expect to appear are changes you made to your SSDT sql files (3 entries in total in our case, one new table and two modified stored procedures), nothing more and nothing less, if something else appears, revise your work so that you do not lose any chunk of hard work!

Now click "Update". This will let visual studio consolidate all of the checked items you want to apply to the database and generate a sql script, and then apply it to your target database (our staging database) via a command tool called SQLCMD.

You can even check this generated script later if you use another tool for deployment on your live environment, but that is also outside of our scope.

Now as a precaution, you can make a quick schema comparison between dev and staging database to triple-check that your changes were applied, this comparison should return with no changes at all.

You have now successfully changed your db script and saved it to version control, now comes the part to commit your files.

Best practice for pushing code to a remote repo on git:

This part is highly dependent on your development environment, your team, your tools and your own liking, but there are some guides that we should all consider, here is a process that honors these guides:

After you are satisfied with all your changes and have revised everything, make sure to stage the files you intend to commit and push. (given our example above, we should see at least 4 files, one new file for the table, two modified files for stored procedures and one for the SSDT .sqlproj file that has one new entry which is the new table file, plus any other files related to the rest of your solution)

Now create a new branch from master (some developers prefer that the main branch is dev, it all depends on how you manage your environment, as previously mentioned) and give it a clear and meaningful name that describes your intention for the changes.

Now commit all the your staged files to this branch, and make sure that the non-staged files are of no effect to the state of your project (if they are undone/discarded, the project still runs as expected)

Here is the tricky part: there are good days and there are bad days when it comes to pushing the code, there might be conflicts, and there might be too much conflicts if a colleague has been working on the same files as you are. Let's dive in.

First of all, you can push your code as-is to the remote repo under the new branch. Now go ahead and create a pull-request to the main branch.

In github for example, when you create a pull request, github will tell you if this can be merged or not, if it can, then your job is done and you can create the pull request, but if not, we will have to resolve all conflicts first. (Check the below images)

Example of when you have a good day:

...and a bad day:

Why conflicts?

First of all, we must understand what a conflict is. A conflict in source control occurs when two versions of the same file have conflicting changes, meaning that for example, you changed the body of the function X and your colleague at work was working on changing the same function's body, now source control will not just apply the latest change, it is a bit smart and understands if this will create a problem, and this is called a conflict.

Conflicts can be resolved by comparing the two versions and deciding which version is the one that makes more sense, or even changing the implementation of a part altogether.

So resolving conflicts might need collaboration with the colleague responsible for the source of conflict and even project management/ business analysts. But in most cases, it is easy to resolve and maintain.

So how do we resolve our conflict?

First, make sure that you have your branch with the your new changes checked out.

Then pull from master/dev (the main branch that you are trying to merge into)

This was not available in visual studio until recently when they added the new GIT interface, here is how to enable it: (You do not have to install the VS preview, just navigate to the options and you will find it under "experimental" with the latest update in 2019)

If you are a hardcore git user (unlike me, i am lazy) who wants to use terminal, your bash command will be something like this:

git checkout <feature_branch>
git pull <remote_master>

And now what will happen is, the code/version on master (the one you want to merge into) will be pulled "over" your current feature branch, and will display that there are conflicts you need to resolve.

Go ahead and resolve the conflicts, and when you are ready, go ahead and commit and push the new changes/ resolution to the conflicts.

Now refresh the web page on your git repo server, and it will surely mention that you are ready to merge your pull request.

Merge and then switch back to master branch in visual studio, pull and test, and sure enough, everything will be working fine and dandy!

There are so much sources on the internet and what i wrote here is not something new or rare, but i decided to compile it that way because it will help people new to SSDT (like myself) and new to git also, i hope this helped you.