Skip to content

Adding Version Control in SSDT

Note: In Visual Studio version control is called source control. These two terms are synonyms.

Using Version Control with database projects can be a bit of a hassle. One of the most difficult parts, for me, was changing the way I deploy objects to the database itself.

I was used to (1) open SQL Server Management Studio and (2) start writing scripts to create and drop objects. But when using version control, there is the need to have a code twin of all the database objects. And, you need some tool to deploy this code to the actual database. This has to involve some kind of comparing mechanism to check what the differences are between the code and the actual database.

Visual Studio and SSDT can solve this problem. SQL database projects in Visual Studio gives you the power to save your database entirely in code. SSDT can then create a publish script based on the sql files in the project and differences between the project and the actual database. A large number of different SQL Server versions are supported. Switching between versions is easy as long as the code in your project is compatible.

For a short introduction to Visual Studio and SSDT I have written another post. This covers the basics of creating a database project. This post is about adding version control to the SSDT projects. The examples are all done on the Wide World Importers project I created at the end of my last post.

What is version control?

Version control is a system of managing your code in such a way that changes are logged and can be reversed. The code is stored in repositories. Because changes are tracked to the user who submitted them, it’s well suited for teams.

There are different version control systems and different cloud providers. In this post I focus on Git (git-scm.com). Git is also out-of-the-box supported by Visual Studio.

For my cloud provider I use Bitbucket or Azure DevOps.

To learn more about Git in general, see the linked tutorials below.

Git tutorials from different cloud providers:

Back to Table Of Contents

Before I continue showing how to add the project to source control, I like to mention that I use a customized layout for my Visual Studio. This means that the Visual Studio in the screenshots may look different than they would look on other machines; I like my main code area on the right (much like the default in SSMS), and my explorer windows on the left. Also I use the dark mode.

Maybe, someday, I will do a post on customizing the look of Visual Studio. For now it will suffice to say that windows inside Visual Studio can be moved around. And dark mode is available through ‘Tools > Options > General > Color Theme’.

Add the database project to Version Control

First you need to open the project that you want to add to source control. In this post I will use the Wide World Importers database project I created in a previous post.

When a project, that hasn’t been added to source control, is open in Visual Studio you will see a blue link in the bottom right of the screen. Click this link to start the wizard to add the project to source control. Instead of clicking the link, you can also click CTRL+ALT+F9.

A small window will open showing all the available source control systems. By default, only Git is available in Visual Studio. Click the desired source control system.

Visual Studio will now start the wizard that guides you through the process of creating a repository.

Back to Table Of Contents

Local repository only

By default Visual Studio suggests that you create a remote repository on GitHub to push your local changes. I always create a local repository first and then later add this to a remote repository.

Click ‘Local only’, leave the local path as suggested by Visual Studio and click ‘Create’.

Visual Studio will add, using Git, the git repository files to the project directory. In the pictures below there is a before and after view of the project folder on the local hard disk.

Local-repository-only versus local-repository-and-remote-repository

Source control will function fine with only a local repository. The only thing is that the changes won’t be synchronized with a remote, changes cannot be easily shared with team members across computers and there is no backup of the repository in case of a system failure on the local machine.

Back to Table Of Contents

The file ‘.gitignore’

One of the files that is added to the projectfolder is the .gitignore file. This is an important file, it contains definitions of files and folders that won’t be added to source control.

Ignoring some files from being added to your repository is important. You, or Visual Studio, may generate temporary files, for example during the project build. These files should not be added to the repository. Visual Studio will create a .gitignore file that is specifically tailored for SSDT projects.

I recommend using the gitignore file created by Visual Studio.

When creating a remote repository, the remote system may want to generate a .gitignore file. If possible choose to not create a .gitignore file and just create an empty remote repository.

You may add custom files and folders to this .gitignore file as you see fit. For more information about gitignore please see Git – gitignore Documentation (git-scm.com).

Sometimes you realize that a file should be ignored after you have comitted the file to the repository. Removing a file after it is added to the repository can be a pain. To accomplish this I always follow the steps in this guide: Untrack files already added to git repository based on .gitignore – CodeBlocQ.

The commands presented in this post by CodeBlocQ are run in the windows command line.

Please close Visual Studio before running these commands!

Back to Table Of Contents

Repository information in Visual Studio

After the projected is added to source control, Visual Studio starts showing information about the repository: the changes made to the project, the number of commits that are not pushed to a remote repository yet and the branch that is currently checked out.

This information is always shown in the bottom of the Visual Studio Screen. Clicking the icons will open the associated views.

As you can see, adding source control to the project lets Visual Studio create two commits. We haven’t pushed these commits to a remote repository yet, so there is a ‘2’ showing.

Back to Table Of Contents

‘Git Changes’ view

To interact with the project repository and remote repositories Visual Studio has a special view called ‘Git Changes’. If this view isn’t visible go to ‘View > Git Changes’.

In the ‘Git Changes’ window you can check-out branches, commit changes to the checked-out branch, fetch and pull changes from the remote and push changes to the remote.

Push the local repository to a remote repository

Now, the project is added to source control, but the repository only exists on my local machine. This way I can reap some benefits of version control; My code changes are tracked and I am able to revert my changes.

But, the best part of source control lies in the features you get when using a remote repository. With a remote repository your code is back-upped on a remote server, you can access it from everywhere and you can work on the code as a team, each member contributing its changes.

Therefore we need to push our local repository to a remote repository. We need to create a remote repository with a cloud provider. I am using bitbucket, but the process of creating a remote repository is not very different with other providers and they all generate a remote url for you to paste in Visual Studio.

How to set up a project in Azure DevOps is explained in this post: Azure DevOps as cloud Git Provider.

In Visual Studio go to the Git Changes view and click ‘Push’.

A new window will open.

In this window you can:

1) Enter a URL to an existing github repository,

2) Create a new github repository after signing in.

3) Choose ‘Other > Existing remote’, to enter a URL to another repository provider

Because I use Bitbucket for my repositories, I choose option 3.

Then paste in the remote URL and click ‘Push’.

Visual Studio will now try to log into the remote repository. This may involve entering your password. These steps should all be provided by Visual Studio in combination with your local git and your remote repository cloud provider.

When the authentication process is completed, the code from the local repository will be pushed to the remote repository by Visual Studio and is available in your remote repository.

In bitbucket this looks like this:

Back to Table Of Contents