MSSQL Database Migration from On-Premises to Azure Cloud: Minimum Downtime Approach Using GitHub Actions

Using GitHub Actions for migration here offers flexibility and automation. In the Initial Setup, a full backup is taken and restored at the target location, establishing a seed copy. This is followed by Continuous Synchronization, where differential backups are regularly applied to capture and update changes made post-initial backup. Finally, during the Final Sync, transactional log backups ensure the most recent changes are included, facilitating a comprehensive and up-to-date migration with minimal downtime. Once configured, this workflow runs seamlessly, greatly streamlining the migration process.

Prerequisites

Before starting the migration process, ensure the following prerequisites are met:

1. Setup GitHub Runners:

• Create Windows GitHub runners in both the source (on-premises) and target (Azure) environments. If a single runner has connectivity to both on-prem and Azure environments, it can be used instead.
• Alternatively, self-hosted runners can be utilized if setting up separate servers is challenging. For instructions on creating and adding self-hosted runners, refer to the GitHub documentation.

2. Install PowerShell Modules:

• Ensure that the required PowerShell modules (SqlServer module) are installed on the runners to enable them to connect to the remote SQL Server instances.

3. Create SQL Server Credential to connect to the azure blob.

Migration Process

The migration process is structured into two primary jobs in the GitHub Actions workflow: the Backup job and the Restore job.

1. Backup Job at Source:

• The GitHub runner in the source on-premises environment initiates the backup process. This job involves taking a backup of the MSSQL database and copying it directly to Azure Blob storage.

2. Restore Job at Target:

• In the Azure environment, the runner associated with the target MSSQL server initiates the restoration of the database from the Azure Blob storage backup to the target Azure MSSQL server.

Achieving Minimum Downtime

To ensure minimum downtime during the migration, follow these steps:

1. Initial Full Backup and Restore:

• Start by taking a full backup of the database. This backup serves as the seed data for the migration. Once the backup is completed, restore it on the target Azure MSSQL server to establish the initial database state.

2. Differential Backup and Restore:

• Continuously take differential backups at the source and restore them at the target until the time of the cutover. Differential backups contain all changes made since the last full backup, ensuring that the target database stays updated with the latest changes from the source.

3. Cutover with Transaction Log Backup:

• During the cutover, take a transaction log backup from the source database. This backup captures all the recent transactions since the last differential backup.
• Immediately restore this transaction log backup on the target Azure MSSQL server. This step brings the target database completely up-to-date with the source, marking the completion of the migration process.

Notes

• Ensure to test the migration process in a non-production environment before executing it in production to validate all steps and minimize potential issues.
• Regularly monitor the backup and restore jobs for any errors or interruptions during the migration process.
• Make sure to use proper backup and restore queries based on the type of the backup and restore.

Here is the sample code:

name: MSSQL database Azure Migration
on:
  workflow_dispatch:    
    inputs:
       database_name:
         description: 'db name to backup'
         required: true
         type: string   
       backup_type:
          description: 'database backup type'
          required: true
          type: choice
          options:
          - full
          - differential
          - log
       source_db_host:
         description: 'source db host'
         required: true
         type: string
       source_db_user:
         description: 'source db user'
         required: true
         type: string
       credential_name:
         description: 'azure credentail backup/restore path'
         required: true
         type: string
      target_db_host:
         description: 'target db host'
         required: true
         type: string
       target_db_user:
         description: 'target db user'
         required: true
         type: string
env:
  SOURCE_DB_PWD: "${{ secrets.SOURCE_DB_PWD }}"
  TARGET_DB_PWD: "${{ secrets.TARGET_DB_PWD }}"
jobs:
  db-backup:
    runs-on: source_runner
    steps:
      - name: chekout repo
        uses: actions/checkout@v3
      - name: DB Backup
        run: |
          $backupType = ${{github.event.inputs.backup_type}}
          $databaseName = ${{github.event.inputs.database_name}}
          $credentialName = ${{github.event.inputs.credential_name}}
          if ($backupType -eq "full") {
            $query = "BACKUP DATABASE [$databaseName] TO URL = '$credentialName/$databaseName.bak', FORMAT;"
          } elseif ($backupType -eq "differential") {
            $query = "BACKUP DATABASE [$databaseName] TO URL = '$credentialName/$databaseName_Diff.bak' WITH DIFFERENTIAL;"
          } elseif ($backupType -eq "log") {
            $query = "BACKUP LOG [$databaseName] TO URL = '$credentialName/$databaseName_Log.trn';"
          } else {
            Write-Host "Invalid backup type specified"
          }    
          Try {
            Invoke-Sqlcmd -ServerInstance ${{github.event.inputs.source_db_host}} -Query $query -Username ${{github.event.inputs.source_db_user}} -Password $Env:SOURCE_DB_PWD -TrustServerCertificate -ErrorAction Stop
          } Catch {  
              $errorMessage = $_.Exception.Message
              exit 1
            }
          }
  restore-backup:
    runs-on: target_runner
    needs: db-backup
    steps: 
      - name: chekout repo
        uses: actions/checkout@v3          
      - name: Restore Backup
        run: |
          $backupType = ${{github.event.inputs.backup_type}}
          $databaseName = ${{github.event.inputs.database_name}}
          $credentialName = ${{github.event.inputs.credential_name}}
          if ($backupType -eq "full") {
             $query = "RESTORE DATABASE [$databaseName] FROM URL = '$credentialName/$databaseName.bak', NORECOVERY;"
          } elseif ($backupType -eq "differential") {
              $query = "RESTORE DATABASE [$databaseName] FROM URL = '$credentialName/$databaseName_Diff.bak', NORECOVERY;"
          } elseif ($backupType -eq "log") {
              $query = "RESTORE LOG [$databaseName] FROM URL = '$credentialName/$databaseName_Log.trn', RECOVERY;"
          } else {
              Write-Host "Invalid backup type specified"
          }    
          Try {
            Invoke-Sqlcmd -ServerInstance ${{github.event.inputs.target_db_host}} -Query $query -Username ${{github.event.inputs.target_db_user}} -Password $Env:TARGET_DB_PWD -TrustServerCertificate -ErrorAction Stop
          } Catch {  
              $errorMessage = $_.Exception.Message
              exit 1
            }
          }