This article is a walkthrough of how to use the working folder option of source control for managing SQL Server databases.
In this article, I am also underlining some of the benefits and limitations of using a working folder as compared to other available options to use with source control.
Let us discuss some key concepts before delving into the technical details of this article.
What is Source Control
Source control is a system or (part of software best) practice that keeps track of all the changes in the code done by developers.
Why Source Control is needed
The application code written by the developers needs to be saved from time to time in such a way that all the changes done by any developer can be not only traced back but also reverted if required.
For this reason, the application code is put under source control to make sure that the history of all the changes along with comments is well kept, plus there are many other benefits of using source control, which is beyond the scope of this article.
Source Control vs Version Control
There is no difference between Source Control and Version Control and mostly these two terms are used freely interchangeably.
Do We Put Databases under Source Control
Just like application code, the database objects such as tables, views, stored procedures etc. also need to be version controlled. However, the method of putting database objects under source control is slightly and in some cases entirely different from when application code is put under source control.
Version Controlling Database Example
Suppose you create a sample database called “Cars” as per business requirements.
Then you create a table called “Car” with the CarId and CarName columns to meet another requirement.
In your absence, another developer is assigned a task to add the CarType column to the “Car” table.
He decides to remove the CarName column thinking it is not required and replace it with the CarType column.
You come back after a long time and are surprised to see that your CarName column is not only missing but also is replaced with the CarType column.
Now, you don’t remember the original data type and length you chose for CarName unless you go through the whole set of business requirements.
Wait a minute! This problem can easily be resolved if you have considered using source control for your database in the first place. Then you can easily see the first-ever change you’ve made, which contains the column definition, and then the second change is done by another developer.
So, you and the other developer sit together and run through the historical changes made to the database (object) using source control which is keeping track of every single change made by any developer on the database.
This is illustrated as follows:
Most Important Use of Source Control
One of the main reasons to use source control is to be able to maintain multiple versions of the code at the same time by creating the following branches of the code:
- Dev (Development branch)
- Test (Test branch)
- QA (QA branch)
- Prod (Production branch)
The technical details of creating Dev, Test, QA and Production branches from source control are beyond the scope of this article.
This article is best suited for those readers who fulfill the following requirements:
Basic Knowledge of T-SQL
You must have some basic T-SQL knowledge to create, query, and modify database objects such as tables, views, and stored procedures.
Database Development Tools
You must have SSMS (SQL Server Management Studio) or dbForge Studio for SQL Server installed on your machine to create and manage databases and their objects.
Working Folder Data Source Availability
Although any source control which offers the working folder option is fine, it is recommended to use dbForge Source Control to follow all the steps of the walkthrough in this article.
Working Folder Source Control
Working Folder with limited functionality to version control database objects can be used just like other source control systems such as TFS, Git, etc.
A working folder simply contains SQL script files used to create and manage database objects.
When to use Working Folder
Suppose you want to create a database and its related objects from scratch but not yet sure which source control your team is going to use ultimately. Then it is better to begin with the working folder source control option.
Another reason might be when you simply want to store the current state of the database and are not interested to keep track of the historical changes than the working folder is a good candidate to be used as source control.
Working Folder Limitation
Working Folder to version control database objects is limited in terms of keeping the most recent version of the database and its objects and there is no way to trace back the changes or revert them.
So, you have to be careful when using Working Folder as your source control option because it cannot show you all the changes made to the database and its objects from time to time.
Walkthrough: Linking Database to Working Folder
Let us go through the steps to link your database to a working folder using source control.
Requirements to Add Book and BookType
You have received internal requirements to create a test database called “SQLBookShopV2” which contains the following two tables:
The database does not necessarily require source control at this point and it is not important to keep track of all the changes made.
Check the Requirements
It is often a good practice to double-check the requirements before using a working folder. A working folder is best suited if you are asked to create a database with the following requirements:
- Test database or database prototype is required
- Database change(s) history is not mandatory
- The decision which source control is ultimately going to be used is not yet decided
Setup Working Folder
The first step is to set aside a folder where your test database scripts will reside after you start checking your database code into the working folder.
Create a new folder called “SQLBookShopV2 Scripts” under C Drive.
Setup SQLBookShopV2 Sample Database
Open dbForge Studio for SQL Server and from the Database menu click “New Database”:
Type “SQLBookShopV2” in the name of the database and click the “Apply Changes” button at the bottom of the window:
Link Database to Working Folder
It is better to get the database linked with the source control right after creating it.
Right-click the database (SQLBookShopV2) and select Source Control à Link Database to Source Control menu item:
Locate the working folder created earlier to link it with the database:
You can choose your desired database development model. We are choosing the shared database development model:
Check the tiny source control icon next to the database which confirms that the database has been successfully linked with the working folder source control:
Create Book Table
Right-click Tables and then click New Table and create book table using the following code and apply changes:
CREATE TABLE SQLBookShopV2.dbo.Book ( BookId INT IDENTITY ,BookTitle VARCHAR(50) NOT NULL ,Notes VARCHAR(200) ,CONSTRAINT PK_Book_BookId PRIMARY KEY CLUSTERED (BookId) ) GO
Commit Database Code Changes
Since we have created a new table in the database, these local changes must be picked up by source control to be saved.
Open Source Control Manager (which shows the latest changes to be saved) by right-clicking the database and then click Source à Show Source Control Manager
Click Commit to check into the Working Folder Source Control:
Working Folder Check
Go to your working folder and see the table object saved there as a result of the latest commit:
Create BookType Table
Create another table BookType using the following code:
CREATE TABLE SQLBookShopV2.dbo.BooKType ( BookTypeId INT IDENTITY ,Name VARCHAR(50) NULL ,Detail VARCHAR(200) NULL ,CONSTRAINT PK_BooKType_BookTypeId PRIMARY KEY CLUSTERED (BookTypeId) ) GO
Add Table to Source Control
Add the newly created table to the source control using the same method as mentioned earlier and then check the working folder to see that both tables are there:
Congratulations! You have successfully linked your database to the working folder source control.
Working Folder Precautions
Please remember a working folder in its pure form as a source control is just like an ordinary Windows folder and if it gets modified externally, it can no longer remember its latest state.
For example, if we delete the Book.sql code from the working folder and then check for any changes using Source Control Manager, we would have to add Book table code again into the Working Folder.
The responsibility of securing the working folder lies on the shoulders of developers rather than source code (in its original form) unless you strictly follow the workaround which has proven to be successful.
Things to Do
You can now easily put your database objects using the Working Folder source control option:
- Please try to create another database by linking the Book table with the BookType table in such a way that the BookTypeId primary key of the BookType table is passed as the BookTypeId foreign key column in the Book table after using working folder source control.
- Please try creating a stored procedure called AddBook to add a new book to the Book table after linking your database with working folder source control.
- Please try creating a database view Books to see the list of all the books with their types and check all the changes into the Working Folder source control.
dbForge Source Control – powerful SSMS add-in for managing SQL Server database changes in source control.
- Mastering the Use of Stoplists With SQL Server Full-Text Search (FTS) - November 17, 2020
- How to use Stopwords and Stoplist to improve SQL Server Full-Text Search (FTS) - September 23, 2020
- Learn to Store and Analyze Documents on Windows File System with SQL Server Semantic Search – Part 3 - August 14, 2020