In this article we will look at maintaining source control and enabling continuous integration for Microsoft SQL Server Databases. The objective is to put database code in a cloud-based source control system such as Github and configure continuous integration and delivery pipelines for smooth database development and deployment cycles.
Why we need Source control for database?
In conventional database development process, there is a centralized development server on which multiple developers work simultaneously with remote access as it is hard to maintain the databases in local(individual) development machines. This method is acceptable when there is a single developer working on the project but as the team size increases, it becomes tedious to find the changes that have been made between two deployment cycles. In addition, the chances for manual errors during deployment are high.
The solution to this problem is to create a database project and maintain the code repository in a source control system. The repository can then be published as a database in various environments, i.e., development, QA, UAT and Production. Since individual developers can clone the repository and database to work separately, it will also be easy to track changes. By building the repository upon each merge, we can also eliminate manual errors and ensure smooth deployment procedure.
To understand how to implement continuous integration for database projects, let us take a use case with a database developed in Microsoft SQL Server and source control system on the cloud as Github. The IDE used as part of this example is Visual Studio 2019. Once we have the Source Control mechanism up and running, we will use Azure DevOps for integrating continuous build methods and will be automatically deployed to different environments without any manual intervention.
As part of this tutorial, following tools were used.
- Microsoft SQL Server 2017 (any edition that suits your need)
- Visual Studio 2019 (2015+) with Data Storage and processing Workload installed
- A Github.com account
- Git Bash/ Git integrated with Visual Studio and basic understanding of Git
- Azure DevOps
It is time to do some hands on :
Step 1: Create a Database Project for a SQL Server Database using Visual Studio 2019. Starting with VS 2019, start with new project by clicking on Create a new Project as shown in below screen –
Step 2: Select Project Type as SQL Server Database Project and press Next button as shown in below screen:
Step 3: Setup project name and location as per your preference.
Step 4: Once you setup Project name and location, a project solution will appear and go to Solution explorer as shown in next screen.
Step 5: We are all set to add database to our project, which we can do by import option as seen in below screen:
Step 6: Next, we need to setup database connection by providing SQL server name, database name and credentials as seen below screen.
Step 7: Once, you setup database connection, press start button to start importing database objects like Tables, Stored Procedure and many more as shown in below screen:
Once your SQL Server project is ready, you can add project to source control like Github, bitBucket or Azure DevOps. I am not going to show you how to push code to cloud repository.
How to push database changes to Source Control and Deploy to Cloud?
As a team member, it is your responsibility to push those changes you have made to database objects Tables like adding new column, adding constraints, changing data type or any other changes. Same goes with Stored Procedure or Functions if you add new Stored procedure or modify existing stored procedure or function, you need to update your SQL database project and can be achieved through using Schema Compare in Visual studio. You will get that option by right clicking o project library as shown in next screen:
Schema Compare will show us the changes in window like we can see in next screen, as I did in table Customer where I add new column CreatedOn when you will click on option Compare. Once you see the results, you can update database by clicking Update button.
There are other third party tools which are paid like Redgate SCA which is commericial in use but if your organisation doesn’t want to invest in source control, you can still go with Database Project with VS.
With today’s tutorial we learned the advantages about source control like:
- If you accidentally delete something, don’t worry, it’s backed up!
- You can track down who added that mystery line of code and (hopefully) why they added it
- Developers have the freedom to make any local changes that they want. If they don’t like them, with one click, they can revert back.
- Automation of database deployment.