Continous Integration Within SQL Server

Question

Continous Integration Within SQL Server

MrFlinstone 686

Hi All.

I would like to know scenarios or use cases for SQL server and continuous integration tools. Is it such that when changes are made to database objects a CI took kicks in. With applications, what I have read is that when an application is developed, it goes through a build process and a continuous integration tool can do this especially when working within a team of multiple developers who could also be changing the code. I am curious as to how this works within a database only setup, and also within a setup where you have scripts and non script based applications.

Thanks in advance.

2 answers

Your answer

Answer 1

Nandan Hegde 36,146 MVP Volunteer Moderator

Hey @MrFlinstone ,
Assuming you have a SQL server :

Create a visual studio database project and checkin all your database scripts in that projects.

Then using Azure Devops:

Create a build Pipeline that would build and validate your database project to generate a DACPAC
Create a release pipeline to publish the DACPAC to the database server.

The Build pipeline can be linked to your database project in either Github or GIt or VSTS and whenever anyone makes changes to the database projects, the build and release pipelines would be triggered and your changes would be deployed

You can refer the below link:
https://medium.com/multinetinventiv/automate-build-and-deployment-of-azure-sql-database-using-azure-devops-with-azure-pipelines-9cf9b576b5c1

which would be somewhat similar for SQL server

MrFlinstone 686 Reputation points

2021-03-09T11:48:28.407+00:00

Thanks for the response, I have read about the scenario you described above.
Let us assume the environment here is on-premise so not in the cloud.
Would it still be possible to implement the above ?.
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2021-03-09T11:53:45.583+00:00

yes, it is possible.
Rather than using the Azure agent you would have to install a Self hosted agent which would be 1 of your on prem VMs that has connectivity access to your SQL server:

https://learn.microsoft.com/en-us/azure/devops/pipelines/agents/agents?view=azure-devops&tabs=browser

The build pipeline is dependent on your repo so that would remain same irrespective of destination being on prem or Azure.
The only changes would be in Release pipelines
MrFlinstone 686 Reputation points

2021-03-09T11:58:24.703+00:00

Thanks for the reply. I would still like to understand how this works and what benefits it brings.
The environment will be on-premise as described, the CI tool will be jenkins.
Lastly, to setup the self hosted agent is there a licencing cost here, I am thinking of a POC using the self hosted agents.
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2021-03-09T12:28:12.19+00:00

In case of Azure Devops Self hoasted agent , there is no seperate licensing cost.
The additional cost would be of the VM which you would need to install the SH agent

Answer 2

Vaibhav Chaudhari 38,916 Volunteer Moderator

You already have mentioned the use case of it.

Developers work on the DB changes like creating new tables, updating existing SPs etc. Once dev and testing is completed, the SQL scripts are checked in to the code repository and the CI (I have used Azure DevOps tool) process triggers the build which builds the database project and ensure all scripts are fine, no any syntax error or no inconsistency.

For reference -
https://www.sqlshack.com/continuous-integration-sql-server-data-tools-visual-studio-2017/
https://www.mssqltips.com/sqlservertip/5164/basic-database-continuous-integration-and-delivery-cicd-using-visual-studio-team-services-vsts/

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

MrFlinstone 686 Reputation points

2021-03-09T12:13:24.75+00:00

Can this be achieved using jenkins ?
Tom Phillips 17,771 Reputation points

2021-03-09T16:19:15.797+00:00

That is a better question for a Jenkins forum. But, yes it can be done using Jenkins.

Share via

Continous Integration Within SQL Server

2 answers

Your answer