Deploying Databases using Azure DevOps pipeline | Nitor Infotech
Send me Nitor Infotech's Monthly Blog Newsletter!
×
nitor logo
  • Company
    • About
    • Leadership
    • Partnership
  • Resource Hub
  • Blog
  • Contact
nitor logo
Add more content here...
Artificial intelligence Big Data Blockchain and IoT
Business Intelligence Careers Cloud and DevOps
Digital Transformation Healthcare IT Manufacturing
Mobility Product Modernization Software Engineering
Thought Leadership
Aastha Sinha Abhijeet Shah Abhishek Suranglikar
Abhishek Tanwade Abhishek Tiwari Ajinkya Pathak
Amit Pawade Amol Jadhav Ankita Kulkarni
Antara Datta Anup Manekar Chandra Gosetty
Chandrakiran Parkar Deep Shikha Bhat Dr. Girish Shinde
Gaurav Mishra Gaurav Rathod Gautam Patil
Harish Singh Chauhan Harshali Chandgadkar Kapil Joshi
Madhavi Pawar Marappa Reddy Milan Pansuriya
Minal Doiphode Mohit Agarwal Mohit Borse
Nalini Vijayraghavan Neha Garg Nikhil Kulkarni
Omkar Ingawale Omkar Kulkarni Pooja Dhule
Pranit Gangurde Prashant Kamble Prashant Kankokar
Priya Patole Rahul Ganorkar Ramireddy Manohar
Ravi Agrawal Robin Pandita Rohan Chavan
Rohini Wwagh Sachin Saini Sadhana Sharma
Sambid Pradhan Sandeep Mali Sanjeev Fadnavis
Saurabh Pimpalkar Sayanti Shrivastava Shardul Gurjar
Shravani Dhavale Shreyash Bhoyar Shubham Kamble
Shubham Muneshwar Shubham Navale Shweta Chinchore
Sidhant Naveria Souvik Adhikary Sreenivasulu Reddy
Sujay Hamane Tejbahadur Singh Tushar Sangore
Vasishtha Ingale Veena Metri Vidisha Chirmulay
Yogesh Kulkarni
Cloud and DevOps | 27 Apr 2022 |   14 min

Deploying Databases Using Azure DevOps Pipelines

featured image

The primary objective of this blog is to help you to create Microsoft Azure build/release DevOps pipelines to automate your SQL deployments. Additionally, we will also cover prerequisites which are needed for creating database projects/pipelines. With this help, you can implement and maintain automated processes for SQL deployments. Following is the prerequisite for creating an Azure build/release pipeline:

SSDT on a local machine

SSDT (SQL Server Data Tool) is required for creating a database project and for syncing your code to the Azure DevOps repository. Now that that’s in order, we can focus on getting started. If you are new to this process and want to create a database with the help of CI/CD process from scratch, then this blog will walk you through the process of:

  1. Consolidating SQL objects
  2. Creating a database project
  3. Deploying a database to a target server with the help of Azure DevOps

This blog includes three sections:

Three sections of the blog

Three sections of the blog

Creating your first Database Project

So, without further ado, let’s get started on creating your first database project! The first step is to consolidate SQL objects. If you have SQL objects present on any Azure repository, then you just need to clone that and start building pipelines. If not, create a database project and a check-in solution on the Azure repository. There are two ways to consolidate your SQL objects:

1. Importing objects from SQL server: With this, you can consolidate your SQL objects in a standard manner like tables, store procedures, views, functions, etc.

2. Importing objects from Visual Studio: With this, you can consolidate your SQL objects in the form of schema.

You can go with either of these ways to create your first database project.

4.1 Importing objects from SQL server

4.1.1 Importing SQL objects from SQL server

1. Open SSMS.

2. Select any database > Right click on it > Go to Tasks > Go to Generate scripts

Creating your first Database Project

Creating your first Database Project

3. Select database objects like Tables, Views, Stored procedures, Functions etc. as per your convenience and click on Next.

4. Save the file – Single file for all scripts or Single file per script. (It’s better to save a single file per script)

Create a separate folder to keep all SQL objects to consolidate all your SQL objects in one place.

Now it’s time to add all the consolidated SQL objects into one SQL database project in Visual Studio. Before that, make sure that you create a repository on the Azure portal. Once the repository is created, clone the same repository on your local machine to create an SQL Server database project inside cloned code.

4.1.2 Creating an SQL Server Database Project

To create the SQL Server database project, you need to have SSDT installed in your system. If not, you can easily download it from the web and install it.

1. Sign into your Azure DevOps organization and navigate to your project.

2. Clone the code locally and keep it in one folder.

3. Open the cloned code in Visual Studio.

4. Once the code opens, right-click on Solution > Go to Add > Go to New

5. Search ‘SQL Server’ and click on ‘SQL Server Database Project’. Enter a suitable name and path and click on Create.

6. Once the database project is created, you can add your SQL objects which we have consolidated in section 4.1.1 and maintain a proper folder structure.

After consolidating all your scripts, you can check-in your code in Azure DevOps.

4.2 Importing objects from Visual Studio

This is another way of creating your first database project.

1. Open Visual Studio.

2. Click on ‘Create new project’.

3. Search ‘SQL Server’ and click on ‘SQL Server Database Project’. Enter a proper name and path and click on ‘Create’.

4. Open SQL Server Object Explorer and login to your local DB.

5. After successful login, expand databases, right-click on the database that you want to import and select Create New Project.

6. Enter a name and a path for the project and click on Start.

7. Once the above process is complete, you can see your consolidated objects with schema names.

Now you are ready to check-in your code. You need to create a pull request and get it approved from your co-worker. Once that’s done, your code will merge in Master branch. Note: If you are pushing your changes from any of your local branches, then you need to raise a pull request; otherwise, it’s unnecessary. Once your code merges in the master branch, the build and release pipeline process will start.

After this, we will focus on creating your first Azure build pipeline.

Creating your first Azure DevOps Build Pipeline

1. Sign into your Azure DevOps organization and navigate to your project.

2. In your project, navigate to the Pipelines. Then create a new build pipeline.

3. Follow the steps of the wizard by first selecting Azure Repos Git as the location of your source code. Master will be the default branch.

4. You will be redirected to the next page to choose a template. Use .Net Desktop as the default template.

5. Go to Pipelines and select your solution. I selected **\*.sln as the solution.

6. Select tasks to create build pipeline. Select the following three tasks for this process:

a) Build solution: This task will build your solution again on the Azure platform. Make sure you select Solution. Keep all the other fields as they are.

b) Copy Files To: Use this task to copy files from a source folder to a target folder using match patterns. This will copy all the files from the build configuration that are being released. I copied files to the target folder – $(build.artifactstagingdirectory).

c) Publish Artifact: Use this task in a build pipeline to publish build artifacts to Azure Pipelines, TFS, or a file share. I chose ‘drop’ as the artifact name.

7. After adding the above three tasks, click on Save & queue. Also, you can change the name of the build pipeline.

8. The queued build will look for an available agent job and will execute all the tasks which we have defined in the pipeline.

This build pipeline will execute every time you merge code in Master branch. And there you go! You have created your first build pipeline.

Creating your first Azure DevOps Release Pipeline

Now let’s see how you can create your first Azure release pipeline. The following will be responsible for deploying your database/SQL scripts on the target server:

1. Sign into your Azure DevOps organization and navigate to your project.

2. In your project, navigate to the Releases. Then choose the action to create a new release pipeline.

3. Do not select any template (Keep it empty) and click on Empty job. Enter a suitable name. If you try to search for any template, there is no specific template for Azure database.

Creating your first Azure DevOps Build Pipeline

Creating your first Azure DevOps Build Pipeline

4. Click on Add an artifact and fill appropriate values in the fields. I selected ‘Blog’ under Project, ‘Databases build’ under Source (build pipeline), and ‘Latest’ under Default version.

Creating your first Azure DevOps Release Pipeline

Creating your first Azure DevOps Release Pipeline

5. Click on Job/Task.

6. Select + and select the task – Azure SQL Database deployment.

7. Numerous fields will appear like Display name, Azure Subscription, Target SQL server name, Database, User ID, and Password. Fill in all the necessary information carefully.

Make sure you select Deploy type as SQL DACPAC File and action type as Publish. Browse the DACPAC file by clicking on the three dots.

8. Once all the above steps are completed, enter a suitable name for the release pipeline and save it. I entered ‘Azure SQL DacpacTask’ as the display name.

Once saved, select Create Release and execute. Voila! You have created your first Azure DevOps release pipeline. You can now easily create Azure DevOps build/release pipelines that will enable you to automate your SQL deployments.

Reach out to us at Nitor Infotech to learn more about the process of creating build/release pipelines and how you can make the most of the various benefits associated with it.

Related Topics

Artificial intelligence

Big Data

Blockchain and IoT

Business Intelligence

Careers

Cloud and DevOps

Digital Transformation

Healthcare IT

Manufacturing

Mobility

Product Modernization

Software Engineering

Thought Leadership

<< Previous Blog fav Next Blog >>
author image

Nitor Infotech Blog

Nitor Infotech is a leading software product development firm serving ISVs and enterprise customers globally.

   

You may also like

featured image

Getting Started with ArcGIS Online

GeoServer is an open-source server that facilitates the sharing, processing and editing of geospatial data. When we are dealing with a large set of geospatial d...
Read Blog


featured image

Getting Started with Angular: A Step-by-Step Guide: Part 1

Its primary 'why' is to craft single-page applications. Angular has distinct advantages as a framework, and it also offers a standard structure for developers to work with. In today’s blog, I’m go...
Read Blog


featured image

Automating End-to-end Cypress Testing for Geospatial Based Apps

On a busy day at Nitor Infotech, we were working on a business use case based on geospatial data. Unlike ordinary spatial data, geospatial data is information th...
Read Blog


subscribe

Subscribe to our fortnightly newsletter!

We'll keep you in the loop with everything that's trending in the tech world.

Services

    Modern Software Engineering


  • Idea to MVP
  • Quality Engineering
  • Product Engineering
  • Product Modernization
  • Reliability Engineering
  • Product Maintenance

    Enterprise Solution Engineering


  • Idea to MVP
  • Strategy & Consulting
  • Enterprise Architecture & Digital Platforms
  • Solution Engineering
  • Enterprise Cognition Engineering

    Digital Experience Engineering


  • UX Engineering
  • Content Engineering
  • Peer Product Management
  • RaaS
  • Mobility Engineering

    Technology Engineering


  • Cloud Engineering
  • Cognitive Engineering
  • Blockchain Engineering
  • Data Engineering
  • IoT Engineering

    Industries


  • Healthcare
  • Retail
  • Manufacturing
  • BFSI
  • Supply Chain

    Company


  • About
  • Leadership
  • Partnership
  • Contact Us

    Resource Hub


  • White papers
  • Brochures
  • Case studies
  • Datasheet

    Explore More


  • Blog
  • Career
  • Events
  • Press Releases
  • QnA

About


With more than 16 years of experience in handling multiple technology projects across industries, Nitor Infotech has gained strong expertise in areas of technology consulting, solutioning, and product engineering. With a team of 700+ technology experts, we help leading ISVs and Enterprises with modern-day products and top-notch services through our tech-driven approach. Digitization being our key strategy, we digitally assess their operational capabilities in order to achieve our customer's end- goals.

Get in Touch


  • +1 (224) 265-7110
  • marketing@nitorinfotech.com

We are Social 24/7


© 2023 Nitor Infotech All rights reserved

  • Terms of Usage
  • Privacy Policy
  • Cookie Policy
We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it. Accept Cookie policy