Deploying Databases Using Azure

Deploying Databases Using Azure DevOps Pipelines

Listen to this article

The primary objective of this blog is to help you to create Azure Build/Release Pipelines to automate your SQL Deployments. Additionally, we will also cover prerequisites which are needed for creating Database Projects/Pipelines

With the help of this, you can implement & maintain automated processes for SQL deployments.

Following is the prerequisite for creating an Azure Build/Release pipeline:

  • SSDT on local machine:

SSDT (SQL Server Data Tool) is required for creating Database Project and for syncing your code to 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 guide will walk you through the process of:

  • consolidating SQL objects
  • creating a database project
  • deploying a database to a target server with the help of Azure DevOps

This guide includes 3 sections:

  • Creating your first Database project
  • Creating your first Azure Build pipeline
  • Creating your first Azure Release pipeline

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 with you present on any azure repository then you just need to clone that and start building pipelines. If not, create Database Project and check-in solution on Azure repository.

There are 2 ways to consolidate your SQL objects:

  1. Importing objects from SQL server: With this, we can consolidate our SQL objects in a standard manner like tables, store procedures, views, functions, etc.
  2. Importing objects from Visual Studio: With this, we can consolidate our SQL objects in the form of Schema.

You can go with either of 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

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

 

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

  • Create a separate folder to keep all SQL objects to consolidate all your SQL objects at 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 to create a repository on the Azure portal. Once the repository is created, clone same repository on your local machine to create SQL Server Database Project inside cloned code.

4.1.2 Creating 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 in to your Azure DevOps organization and navigate to your project.
  2. Clone code locally and keep it in one proper folder.
  1. Open cloned code in Visual Studio
  2. Once code will open, right-click on Solution > Go to Add > Go to New
  3. Search “SQL Server” and click on SQL Server Database Project. Give a proper name and path and click on Create.
  4. Once database project is created, you can add your SQL objects which we have consolidated in section 4.1.1 and maintain proper folder structure

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

  • 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. Give 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 and right-click on the database that you want to import and select Create New Project.
  6. Give name and path for the project and click on Start.
  7. Once the above process is complete, you can see your consolidated objects by 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 then Build and Release pipeline process will start.

Creating your first Build Pipeline

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

  1. Sign in to 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 default branch
  4. You will redirect to next page to Choose template. Use .Net Desktop as default template.
  5. Go to Pipelines and select your solution
  6. Select tasks to create build pipeline. Select below 3 tasks for this process
    a)Build solution: This task will build your solution again on Azure platform. Make sure to select Solution. Keep all the other fields as it is
    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 Build configuration that are being released.

    c)Publish Artefact: Use this task in a build pipeline to publish build artefacts to Azure Pipelines, TFS, or a file share.

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

  1. Queued build will look for available Agent Job and will execute all the tasks which we have defined in pipeline.


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

Creating your first Release Pipelines

Now we will see how you can create your first Azure release pipeline.

Following will be responsible for deploying our Database/SQLScripts on target server:

  1. Sign in to 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. Give proper name. If you try to find out any template, there is no specific template for Azure database.

  1. Click on Add an artefact and fill appropriate values in fields.

  1. Click on Job/Task
  2. Hit on + and select task Azure SQL Database deployment
  3. Numerous fields will appear like Display name, Azure Subscription, Target SQL server name, Database, UserID, Password etc. Fill all the necessary information carefully.

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

  1. Once all the above steps are completed, give proper name for release pipeline and save it

Once Saved, hit on Create Release and execute.

Voila! You have created your first Release pipeline.

You can now easily create Azure 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.

About Shubham Hedau

Senior Software Engineer

  • Business Intelligence
  • Business Analytics
  • Retail
Shubham is a BI developer with more than 3 years of experience in various BI tools like SSMS, SSIS, SSRS, PowerBI, LogiAnalytics, Looker,etc. He holds good knowledge of retail domain. He is passionate about learning new things and loves to explore new places. He is a tea lover. His favorite quote is : "Never give up"