Rolling Back Failed Deployments

Rolling back failed deployments is an essential aspect of DevOps and CI/CD practices. In the context of database management, it is especially critical to ensure that your database schema, data, and application logic are consistent and stable. The process involves automatically or manually reverting changes made during a deployment in case something goes wrong.

This detailed guide will walk through various strategies, tools, and techniques for rolling back failed deployments in databases and applications, specifically focusing on SQL Server and other database systems, with best practices for integration into CI/CD pipelines.

1. Introduction to Deployment Rollbacks

A deployment rollback is the process of undoing changes made to a system during a deployment. Deployments can fail for a variety of reasons, including errors in the code, unexpected bugs, incorrect configurations, or infrastructure issues. A rollback ensures that the system returns to a stable state, reducing downtime and avoiding negative user impact.

In a CI/CD pipeline, automatic rollback is crucial for maintaining high availability and minimizing manual intervention. It’s especially important for database-related deployments, as database schema and data changes can have irreversible consequences if not handled carefully.

2. Why Rollbacks Are Important in Database Deployments

Database deployments are often more complicated than application code deployments because:

Stateful nature: Databases are stateful systems, meaning that changes to data or schema directly affect the operation of the system.
Irreversibility: Certain database operations, such as data migrations or schema changes (like dropping a column), can’t easily be undone.
Complicated dependencies: Database objects like stored procedures, triggers, or views might depend on each other, meaning a failure in one could affect others.

To mitigate these risks, having a rollback strategy becomes paramount. A good strategy ensures that your database remains consistent, reliable, and in sync with your application after a failed deployment.

3. Common Types of Deployment Failures

Before diving into rollback strategies, it’s important to understand the common types of deployment failures:

Failed Schema Changes: Changes to the database schema (such as adding, modifying, or deleting tables, columns, or indexes) can fail due to conflicts, incorrect queries, or incompatible changes.
Data Migrations Failures: These failures occur when the migration scripts fail to migrate or transform data as expected. These can happen due to invalid transformations, unexpected data structures, or missing records.
Application Code Failures: If the application is tightly coupled with the database, failures in application code (such as queries, stored procedures, or business logic) can lead to deployment failures.
Environment Failures: These occur when the deployment fails due to environment-specific issues like resource exhaustion, configuration errors, or network failures.

Having a proper rollback strategy will help you recover from all these failures.

4. Rollback Strategies for Database Deployments

1. Version Control and Backup

The first line of defense for rolling back failed deployments is version control and backups.

a. Database Backups

Before starting a deployment, it is always a good practice to create a full backup of the database. This provides a safe restore point in case something goes wrong.

Full Backup: A complete backup of the database, including schema, data, and object structures.
Differential Backup: A backup of changes made since the last full backup.
Transaction Log Backup: A backup of all transactional changes that have occurred.

These backups allow you to restore the database to a known good state.

For example, in SQL Server, you can use:

BACKUP DATABASE YourDatabase TO DISK = 'C:\backups\YourDatabase.bak';

b. Automating Backups in CI/CD

In a CI/CD pipeline, ensure that automated backups are part of your pipeline. Here’s an example of an Azure DevOps pipeline step for SQL Server backup:

- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: '$(azureSubscription)'
    ServerName: '$(sqlServerName)'
    DatabaseName: '$(sqlDatabaseName)'
    SqlUsername: '$(sqlUsername)'
    SqlPassword: '$(sqlPassword)'

- script: |
    sqlcmd -S $(sqlServerName) -d $(sqlDatabaseName) -U $(sqlUsername) -P $(sqlPassword) -Q "BACKUP DATABASE $(sqlDatabaseName) TO DISK = 'C:\backups\$(Build.BuildId).bak'"
  displayName: 'Backup Database Before Deployment'

This ensures that backups are automatically created before every deployment.

2. Rolling Back Schema Changes Using DACPAC

For SQL Server, a DACPAC (Data-tier Application Package) provides an effective method for managing and rolling back schema changes. DACPACs allow you to publish and restore database schema changes.

a. Publishing Changes with DACPAC

The Publish operation using DACPAC applies schema changes to the database. This operation is idempotent, which means that if it fails, it can be undone or rolled back easily by restoring to a previous state.

sqlpackage /Action:Publish /SourceFile:"C:\Path\To\YourDacpac.dacpac" /TargetServerName:"YourSQLServer" /TargetDatabaseName:"YourDatabase"

b. Rollback with DACPAC

To rollback a deployment using DACPAC, you would typically re-deploy a previous, stable DACPAC version. To roll back to a previous state, you would:

Create a DACPAC of the current schema state before deploying any changes.
If a failure occurs, restore the previous DACPAC version.

For example:

sqlpackage /Action:Publish /SourceFile:"C:\Path\To\PreviousStableDacpac.dacpac" /TargetServerName:"YourSQLServer" /TargetDatabaseName:"YourDatabase"

3. Using SQL Scripts for Rollbacks

If you’re deploying schema changes using SQL scripts (instead of DACPACs), it’s important to write reverse migration scripts for each change. For example, if you added a column to a table, your rollback script would remove that column.

Here’s an example:

Deploy script: ALTER TABLE YourTable ADD NewColumn INT;
Rollback script: ALTER TABLE YourTable DROP COLUMN NewColumn;

4. Transaction-based Rollbacks

For smaller, transactional changes, you can use SQL transactions to group operations together. If something goes wrong, you can roll back all changes made during the transaction.

For example:

BEGIN TRANSACTION;

BEGIN TRY
    -- Apply schema changes
    ALTER TABLE YourTable ADD NewColumn INT;

    -- Apply data changes
    UPDATE YourTable SET NewColumn = 1 WHERE SomeColumn = 'SomeValue';

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    -- Log error or send notification
END CATCH

In this scenario, if any error occurs, the entire transaction is rolled back, and the database remains in a consistent state.

5. Data Migrations and Rollback

When deploying data migrations (for example, migrating from one database schema to another or transforming data), it’s essential to handle rollback properly. The general strategy is to:

Create reversible migration scripts: Each data migration script should be written in a way that can be reversed if needed.
Use transactional handling: Wrap the migration scripts in transactions to allow for complete rollback in case of failure.

For example, if you’re adding data to a table, the migration might look like this:

Migration script: INSERT INTO YourTable (Column1, Column2) VALUES ('Value1', 'Value2');
Rollback script: DELETE FROM YourTable WHERE Column1 = 'Value1' AND Column2 = 'Value2';

6. Rollback Strategies for Application Code

If your database is coupled with application logic (such as stored procedures or triggers), you may need to also roll back application code. This is typically done by:

Version control: Use version control systems (like Git) to manage application code versions.
Revert changes: If a deployment fails, you can revert the code changes using Git or other source control tools and redeploy the stable version of your application.

5. Automated Rollback in CI/CD Pipelines

For automated rollback, the key is to integrate rollback mechanisms directly into your CI/CD pipeline. This ensures that, if a deployment fails, the system can automatically attempt to revert to a stable state.

1. Azure DevOps Automated Rollback

In Azure DevOps, you can create a rollback pipeline by defining deployment strategies for both successful and failed deployments.

Example YAML pipeline configuration:

trigger:
- main

pool:
  vmImage: 'windows-latest'

jobs:
- job: DeployDatabase
  steps:
  - task: SqlAzureDacpacDeployment@1
    inputs:
      azureSubscription: '$(azureSubscription)'
      ServerName: '$(sqlServerName)'
      DatabaseName: '$(sqlDatabaseName)'
      DacpacFile: '$(Build.ArtifactStagingDirectory)/YourDatabase.dacpac'
      SqlUsername: '$(sqlUsername)'
      SqlPassword: '$(sqlPassword)'

  - script: |
      sqlcmd -S $(sqlServerName) -d $(sqlDatabaseName) -U $(sqlUsername) -P $(sqlPassword) -Q "EXEC tSQLt.RunAll"
    displayName: 'Run Tests'
    
  - task: PublishTestResults@2
    inputs:
      testResultsFiles: '**/TestResults.xml'

  - script: |
      if (Test-Path "$(Build.ArtifactStagingDirectory)/FailedDeployment.bak") {
          sqlcmd -S $(sqlServerName) -d $(sqlDatabaseName) -U $(sqlUsername) -P $(sqlPassword) -Q "RESTORE DATABASE $(sqlDatabaseName) FROM DISK='$(Build.ArtifactStagingDirectory)/FailedDeployment.bak'"
      }
    displayName: 'Rollback if Deployment Fails'

In this pipeline, after deploying the DACPAC:

Unit tests are run automatically.
If the tests fail, the rollback step restores the database from a backup taken before deployment.

2. Jenkins Automated Rollback

In Jenkins, similar logic can be implemented by defining post-deployment actions to handle rollbacks. A typical Jenkinsfile might look like this:

pipeline {
    agent any

    stages {
        stage('Deploy Database') {
            steps {
                script {
                    // Deploy DACPAC
                }
            }
        }

        stage('Run Tests') {
            steps {
                script {
                    // Run unit tests
                }
            }
        }

        stage('Rollback on Failure') {
            steps {
                script {
                    if (currentBuild.result == 'FAILURE') {
                        // Restore database from backup
                        bat "sqlcmd -S $(sqlServerName) -d $(sqlDatabaseName) -U $(sqlUsername) -P $(sqlPassword) -Q 'RESTORE DATABASE $(sqlDatabaseName) FROM DISK=\\backup\\FailedDeployment.bak'"
                    }
                }
            }

}
}
}


This pipeline automatically handles the rollback if the tests fail after deployment.

---



Rolling back failed deployments is a critical practice to ensure that your systems, especially databases, remain consistent and reliable. Using a combination of **version control**, **automated testing**, **transactional handling**, and **CI/CD automation**, you can ensure that your database deployments are robust and can be rolled back smoothly when necessary.

Remember, the key to success is not just in deploying but in managing failures efficiently, so your systems are always in a stable state. By using the strategies described above, you can minimize downtime and ensure business continuity.