You might have heard about the advantages of getting your SQL development team onto a version control system, but may be stuck on how to get there. This article is here to help you quickly implement version control on your SQL Server database!
If you’re considering paying for a service that helps you version your database, think again! We’re going to show you how to do it for free! And it only takes about 30 minutes to set up. Here are the steps we’ll take:
- Install Git
- Install Azure Data Studio
- Connect to Your SQL Server Database
- Create a Project
- Initialize a Repository
- Track changes
Install Git
In order to quickly implement version control, you first need to install Git. For detailed instructions on how to do that, you can check out this video called: How to Install Git on Windows 10
Installing Azure Data Studio
Its likely that if you have SQL Server installed on your computer that Azure Data Studio was installed as well. But just in case you don’t have it, you can quickly download and install it by following the link: https://learn.microsoft.com/en-us/azure-data-studio/download-azure-data-studio
It’s important to note that Data Studio allows you to work with multiple different databases. This makes it a valuable tool for managing additional database management systems like: MySQL, MongoDB, PostgreSQL, and more.
With Data Studio installed we can continue to the next step of connecting to SQL Server.
Connect to Your SQL Server Database
With Data Studio open, navigate to the connections view. We’ll want to setup a new connection to SQL Server by clicking on the new connection icon.
This opens a dialog window to the right. By default the connection type will be for SQL Server. You have the option to either input a connection string or manually input parameters. If you already have a connection string built that might be the better option, but if not, its easy to pass the parameters here.
When you’re ready, click connect.
The new connection should then appear in the view. If you’ve previously used SQL Server Management Studio, then navigating will be very familiar.
With the connection made, we can move on to creating a project.
Creating a Project
Your ultimate goal is to version changes made to objects in SQL Server such as:
- Stored Procedures
- Views
- Schemas
- Tables
- Functions
We can do this by leveraging a plugin in Azure Data Studio called “SQL Database Projects”. This is a plugin developed by Microsoft and it was built for the purpose of tracking and applying changes to SQL Databases.
You can install the extension within Data Studio by navigating to the extensions view (its the four boxes). From here, you simply search for the package listed above.
When it comes to project file structure, you have 5 options to choose from:
- File
- Flat
- Object Type
- Schema
- Schema/Object Type
File
This option consolidates the entire database into a single script. This may not be the best option for larger and complex database models since it can be difficult to navigate a single large SQL script. My recommendation for versioning purposes would be to avoid this option altogether.
Flat
This option will create a separate file for each object and then store them all in the same folder. While this makes the project a bit more manageable, we aren’t able to tell what type of object each file creates. For development purposes, this structure is not very productive.
.
├── StoredProcedure1.sql
├── StoredProcedure2.sql
├── Table1.sql
├── Table2.sql
├── View1.sql
├── View2.sql
└── Sales.sql
Object Type
This option puts objects into folders based on their object types. Check out the structure below:
.
├── Functions
├── Security/
| └── Sales.sql
├── StoredProcedures/
│ ├── StoredProcedure1.sql
│ └── StoredProcedure2.sql
├── Tables/
│ ├── Table1.sql
│ └── Table2.sql
└── Views/
├── View1.sql
└── View2.sql
Its definitely a step up from the flat file structure, but there are still some drawbacks. One drawback is that there’s now way to tell what schema is being used unless you open the files.
For example, many objects in your database might use the default dbo schema. But lets say that you have a Sales schema as well. Utilizing the Object Type structure, you wont know which schema the objects are using unless you open and check the files. As a side note, schemas are stored in the Security folder.
Schema
This option organizes the scripts by schema type. It solves the issue we face with the Object Type structure in that it organizes scripts based on schema. Checkout the structure below:
.
├── dbo/
│ ├── StoredProcedure1.sql
│ ├── Table1.sql
│ └── Table2.sql
├── Sales/
│ ├── StoredProcedure2.sql
│ ├── View1.sql
│ └── View2.sql
└── Sales.sql
While this option gives us more context around schema, we lose the organization of the object type that was actually quite helpful. If only there was an option to do both. Guess what, there is!
Schema/Object Type
The Schema/Object Type option is the best of both worlds. With this option you get organization by schemas and object type. Check out the example structure below:
.
├── dbo/
│ ├── Functions
│ ├── StoredProcedures/
│ │ └── StoredProcedure1.sql
│ ├── Tables/
│ │ ├── Table1.sql
│ │ └── Table2.sql
│ └── Views
├── Sales/
│ ├── Functions
│ ├── StoredProcedures/
│ │ └── StoredProcedure2.sql
│ ├── Tables
│ └── Views/
│ ├── View1.sql
│ └── View2.sql
└── Sales.sql
This is by far the best structure for development and versioning. And its the one I’m recommending for this walkthrough. Let’s quickly implement version control on your database by creating a new project.
Within Data Studio, navigate to the connections view where we added our SQL Server connection. Expand the databases folder and locate the database where you want to apply version control. Right click and find the option that says “Create Project From Database”.
This will open a dialog on the side that will ask for a project name, the save location, and the folder structure you want to use. For my example I’ll keep the default project name. I’ll select my downloads folder as the location and then I’ll pick the “Schema/Object Type” as the Folder structure.
After clicking create, the project should generate and reload in Data Studio. Your view will be changed to “Database Projects” where you can navigate your newly created project.
Initializing a Repository
Now that you’ve created your first project you have a version control friendly version of your SQL Server Database. But we do have a few more steps to actually get this database versioned.
We need to initialize a git repository within our project folder.
There are a number of ways that you can do this. The easiest way is to navigate to the “Source Control” view and click on either the “Initialize Repository” or “Publish to GitHub” buttons.
Let’s explore these two options!
Initialize Repository
When you click the Initialize Repository button, you’ll be prompted to select the project you’d like to create a repository in. Once you’ve selected a project folder, a .git folder will be placed inside the folder behind the scenes. The Source Control tab will then refresh with the entire contents of the project.
From here, you simply need to stage all of the changes and create an initial commit message. Simply type initial commit into the message field and then hit the plus button to stage all of the changes. To commit the changes, simply click the commit button.
After creating a local version of the repository, you will also want to create a remote repository so that everyone on the team can collaborate on the database. You can publish to remote repository providers such as GitHub or GitLab.
Publish to GitHub
This option simplifies your work a bit. Rather than creating a local repository and then pushing it to a remote repo, you can simply publish it directly to GitHub.
This means that you need to have a GitHub account ready. GitHub offers both free and paid solutions. If you are a solo developer, the free edition makes the most sense. But if you’re working in an organization, your company may need to pay for an enterprise account.
When you click the “Publish to GitHub” button, you will again be asked which project you want to create a repository for. After selecting the project, you’ll then be asked to connect to your GitHub account and give access to Azure Data Studio. This is done in your web browser.
After completing that connection, close the web browser and navigate back to Data Studio. You’ll see a prompt at the top asking what you want to name your repository and if you want to publish it publicly or privately. Since I don’t care to make this a public repo, I’ll click on the private option.
You’ll then be prompted to specify what files you’d like to include in the repo. All files are selected by default. I’ll keep this option and click “ok”.
After completing these steps you should get a notification in the bottom right of Data Studio telling you the repository was published successfully.
In the background a .git folder will have been added to your project and it will be open in Data Studio. The best part about this option is that the link to your remote repository will already be established so you can easily commit and push changes.
Track Changes
Now that the repository is setup, you can start tracking changes to the database. Changes can occur in two places.
- Inside the Database
- Inside the Project
Inside the Database
The most common way that databases are changed is through creation of objects in the database itself. For example, when working in Data Studio or SSMS you can create a new view on the database and it is immediately put into production.
Its important to note that changes made within the database directly are not automatically version controlled. In order to track changes made inside the database, you must update your project. This is easy to do in Data Studio.
After making a change on the database, simply navigate to the connections view and right click on the database. You should see an option to “Schema Compare”. This will open a new compare window that allows you to view changes between your database and the project.
You will need to select your project in the target window and then hit the compare button. If you have changes they will show up below. If you click on a change, it will show you the details in a diff window.
To apply changes, you can hit the apply button at the top. Once the changes are in your project, you can then track the changes in git.
Inside the Project
Another way you can track changes to a database is to create and change objects within the project itself.
Within the “Database Projects” view in Data studio, you can right click on any folder and it will give you a number of options. For example, you can add a table, view, or stored procedure. Any additions or changes you make in the project will immediately be tracked in git.
Its important to note that when you make updates through the project, you must push those changes to the database in order for them to be put in production. This is done using the same tool as discussed above. The only difference this time is that we’ll use the schema compare tool to publish changes from our project to our database.
In the “Database Projects” view, right click on the main project folder. You should see the option to compare schema. This will open up the dialog we saw before, but now the source and target are switched.
Applying the same process as before, we can click compare and then apply any changes we see.
Conclusion
With that you should be ready to go! The next step is to get your team up to speed on the process. When everyone is on board with version control, you’ll be able to better collaborate and manage your SQL development. For more on the benefits of version control check out 3 Reasons Why You Need to Version Your SQL Code!