X
06Oct

Database deployment using GitHub Actions

In this blog, I will be showing how to export the database file from SQL Server and import the same in another SQL server. When we do the export, we will be having 2 different file formats i.e DACPAC and BACPAC. The major difference between is DACPAC copies only schema whereas BACPAC copies both schema and data.

Export the bacpac file from sql server using SSMS
1.    Connect to SQL server using SSMS by providing the credentialsn
2.    Right Click on database -> Task -> Export data tier application
3.    Save the bacpac at required location.
11

At this point we have extracted the schema along with data from the database and stored in bacpac file.

Note: If we select Extract data tier application from the task, we will only be getting dacpac file i.e. we will only be getting schema of the database.

Next, we will push our backup file stored in GitHub along with the source code of the respective application in GitHub.

Before restoring the database, we need to have sqlpackage installed on the machine from where we will be initiating the import task, once the package is installed, we can proceed with the restoration part. 

What is SQLPACKAGE??

SQLPACKAGE.EXE is a command line utility that can be helpful for automations of various database deployment task. Mainly it can be used to export and import Data-tier Applications.

For more information on sql package checkout the below reference:

https://docs.microsoft.com/en-us/sql/tools/sqlpackage

When using the above utility, you must be using various options along with it. Above link can be helpful in using the utility and it also provides few examples which can be handy while using the package. Installing the sqlpackage can be done manually (for self-hosted runners) using this link or else we can do the same using the setup-sqlpackage action from the GitHub marketplace.

Below is the sample snippet for installing the sqlpackage using action from marketplace and command/action to import the database on the target sql server.

jobs:
  build:
    runs-on: windows-latest
    steps:
    - name: Checkout the source code
      uses: actions/checkout@v2

    - name: setup-sqlpackage
      uses: coreywebber/setup-sqlpackage@v1.0.0

    - name: Restoring the database
      run: sqlpackage /Action:Import /sf:${{github.workspace}}\partsnew.bacpac /tsn:${{secrets.DB_SERVER}} /tdn:${{env.database}} /tu:${{env.db_user}} /tp:${{ secrets.db_password }} /p:DatabaseMaximumSize=2 /p:DatabaseEdition=Basic

In the above snippet, I am executing all my tasks on windows runner machine (GitHub runner). Then in the tasks section, we need to check out the code from the repository using checkout action and then we will be installing the sqlpacakage utility on the same machine. Next, step will be the important step where we need to prepare the command to restore the database. I have prepared a command based on my requirement by providing the required options. Here, various options for the command have been passed as a GitHub action variable (using secrets) and some variables as environment variables in my workflow file (hidden in above snippet) and values for the variable are provided in respective sections.

Once everything is setup, we can trigger the action.

Special note: When importing to Azure SQL Server you will need to validate the database configuration that will be used with the utility (sqlpackage). Most important are the max size of the database which will define the size and the edition.

 For example, using /p:DatabaseMaximumSize=150 will result in a premium SKU edition with a database size of 150 GB. Please do not forget this because Azure SQL DB can be costly.

Related

Webinar -The curious case of native versus the cross platform development of mobile apps

The curious case of native versus the cross platform development of mobile appsBusiness is often cau...

Read More >

Microsoft Azure : Mobile Services - iOS with JavaScript

We will add cloud-based backend service to an iOS app using Azure Mobile Services.We will create bot...

Read More >

Bugzilla to TFS Migrator (Part 2 of 4) – Installation

This is the second part of the Bugzilla to TFS Migrator blog series covering the installation of the...

Read More >

How to use Model Binding with ASP.NET Data Controls

IntroductionASP.NET 4.5 provides a flexible alternative to server data controls called as, Model Bin...

Read More >

Microsoft Azure : Running JAVA Web Application using template

We will create Java web app in Azure App Service by using the Azure portal. The Azure...

Read More >

Fortira Exclusive CxO Roundtable at C-SOaP- SUMMIT 2013

img{ display:block; } #outlook a{ padding:0; } body{ width:100% !important; } body{ ma...

Read More >

How to implement and use Extension Methods in C#

Do you wish to extend the built-in types with new methods?Want to extend custom types with new metho...

Read More >

Git Command line Options

Git is a free and open source distributed version control system designed to handle everything from ...

Read More >

Connect apps and integrate data with workflows using Azure Logic Apps

Implementing complex business process has been made easy. Logic Apps in azure provide a way to imple...

Read More >

Introduction to Amazon S3 (Simple Storage Service) and EC2 (Elastic Cloud Computing) in Asp.Net

S3 is an Internet storage engine which has to be designed to make job easier for developers.Develope...

Read More >

Share

Try DevOpSmartBoard Ultimate complete Azure DevOps End-to end reporting tool

Sign Up

  • Recent
  • Popular
  • Tag
Tags
Monthly Archive
Subscribe
Name

Text/HTML
Text/HTML
Contact Us
  • *
  • *