Getting started with Database Projects and Version Control

In one of my previous blog posts (Creating your first Azure DevOps Project), I described how to create an Azure DevOps Project and explained what kind of configuration you might want to consider.

In this blog post, we will focus on how to get started with Database Projects and how to get this into Source Control (Azure Repos). So together we will create our first Database Project, import our database into the project and push it to the Azure Repository.

Before we can start, we need to make sure that we have the required tools installed, in this blog post I will focus on Visual Studio. In order to create your first Database Project, you need to ensure that the SQL Server Data Tools extension for Visual Studio is installed. To check you need to execute the following steps:

1. Open Visual Studio
2. Continue without Code
3. Tools –> Get Tools and Features

Navigate to the Other Toolsets section and validate if SQL Server Data Tools is marked

If not make sure to install it before continuing this blog post.

Additionally, you will also need to make sure that you have a local installation of GIT available, you can use the following link: Git – Downloads (git-scm.com)

Creating a Database Project and importing an existing database

Creating the database project

For this example, we will use the AdventureWorksLT database to create our very first Database Project in Visual Studio.

In Visual Studio, navigate to File –> New Project… and choose SQL Server Database Project from the list and click the Next button.

Now you need to choose the name of your project. Depending on how you would like to structure your database projects naming might differ.

For now, I will call the project: AdventureWorksLT. So give your project a name and make sure that you specify the preferred location where your database project should be stored on your local machine (linking to Azure DevOps will happen afterwards).

Now, you can hit the Create button to create your first database project.

As a result, you should now see something similar:

Importing an existing database

Up next is to import the database, to do so, we will use SQL Server Object Explorer which is available in Visual Studio:

As soon as you have the SQL Server Object Explorer available, you can connect to an existing database. Right-click on SQL Server, and choose Add SQL Server. For this demo, I will connect to the AdventureWorksLT database which is an Azure SQL database.

After connecting, you can navigate to the database in the SQL Server Object Explorer. When you have found the database you want to import into your project, just right-click on the database and choose Schema Compare.

As soon as you have chosen this option, Visual Studio will open a SqlSchemaCompare window where you must point out what needs to be compared. On the left, the database of your choice will be visible and on the right, you will need to choose where to compare it to.

Now click the Compare button and as a result, you should see something similar to:

Adding your Database Project to Version Control

If we want to add our Database Project to Version Control, we first need to determine which Version Control system we would like to use:

Azure DevOps Repos
GitHub
BitBucket
….

I will choose Azure DevOps Repos for this blog post. To add your solution to Azure DevOps Repos you will need to have an active Azure DevOps subscription, access to an organization and a Project available.

If you’re wondering how to set up an Azure DevOps project, I have written a blog post about this as well, Creating your first Azure DevOps Project.

To import your Database Project into Azure DevOps Repos, you will need to execute the following steps:

As soon as the Create a Git repository pane has opened, choose “Existing remote” and paste the URL to your remote GIT repository.

To finalize choose “Create and Push”. You now managed to push your Database Project into Version Control.

To validate, navigate to Azure DevOps Repos. Your result should look like this:

Wondering how you can start development using Database Projects & Azure DevOps? You can find a dedicated blog post via the following link: Database Projects – Making changes – Feature Branching (oliviervs.be)