Most of the time, deploying database scripts is tricky, time-consuming, and error-prone — specifically when a script fails due to mismatched schema, missing prerequisite data, dependencies, or any other factor.

Thankfully, different tools can automate and simplify the process…one of which is SQL Change Automation from Red Gate.

What is SQL Change Automation?

Put simply, SQL Change Automation (SCA) allows you to develop and deploy changes to a SQL Server database. It automates validation and testing, which can be performed on build and release management systems such as Azure DevOps, TeamCity, Octopus Deploy, Bamboo, and Jenkins.

Installation & Required Tools

  • Download SQL Toolbelt
  • Run the .exe and select only “SQL Change Automation 3.0” and “SQL Change Automation PowerShell 3.1
  • Visual Studio 2015/2017
  • Azure DevOps

Automated Deployment with SCA & ADO CI/CD

Create an SCA Project

  1. First, create a new SQL Change Automation project by clicking the Create Project button from SQL Change Automation menu under Tools.
  2. Select the Development (source) and Deployment Target Databases. SCA will detect the differences and create a baseline script.
    Note: This baseline script is created from the selected Target database
  3. The next step is to identify the source database changes that need to be scripted and deployed to the target database. For this, click on the Refresh button in the SQL Change Automation tab. It will list the database objects which are different from the target database.
  4. After selecting the required objects, click the Import and Generate Scripts button. It will automatically generate all the required scripts.
  5. Now go ahead and build the solution!

Setup GIT Repository in DevOps

  1. Create a new repository and upload the SCA project.
  2. Create a new Feature branch and commit the changes to this repo. Raise a pull request and assign the reviewer.
  3. Once the pull request is approved and marked complete, the changes will automatically merge to the master branch.

Setup CI/CD Pipeline in DevOps

  1. Create a new Build pipeline, select the repo.
  2. Add a new build task Redgate SQL Change Automation: Build and configure it.
    Note: This extension must be first installed into your Azure DevOps organization before using it as a task in the build flow
  3. Save the pipeline and queue a new build.
  4. Next setup a Release pipeline, create a new Release, and select the Build artifact as the input.
  5. Add a new Release task Redgate SQL Change Automation: Release and specify the configuration details like operation type, build package path, target SQL instance, database name, and credentials.
    Note: This extension must be first installed into your Azure DevOps organization before using it as a task in the release flow
  6. Save the Release pipeline and trigger a new Release.
  7. Once the Release is successful, connect to the target database and verify if the new database objects are deployed.
    Note: The target database server can be in Azure or on-premises.

Here a short video on how to configure SCA and integrate with Azure DevOps CI/CD pipeline.

Automated Rollback Using SCA & ADO CI/CD

Rolling back a database deployment is a complicated task. The code on other fronts is rather easy to rollback — just deploy the previous version of the code package and done. But databases are not as flexible. Imagine there’s an error in a script and all usernames get deleted. There isn’t a good way to roll that back! Sure, a backup could be restored. But when was that backup taken? Have any new users been in the system since that backup? What data will be lost if the backup is restored?

The process needs to be thought through right before the deployments to ensure an effective rollback process. The steps below walk through a simple example of how a rollback can be applied in an automated manner using SCA with CI/CD.

  1. First, create a new folder in your SCA solution and name it Rollback. Add your rollback scripts to this folder.
  2. While creating migration scripts (i.e., UP script), also create Down scripts. To create rollback scripts, right-click the database object and select View Revert Script option.
  3. Save the script in a new file and save it under the Rollback folder.
    Note: This rollback script will not be executed as part of the deployment.
  4. If there are any issues post-deployment, copy this rollback script to the Migration folder. Insert the Metadata and save the script.
  5. Commit the script to GIT and complete the pull request.
  6. Raise a new Build and let Release to complete.
  7. Once successful, verify the changes.

Here is a short video on how to perform rollback with SCA generated scripts.

Key Terms:

  • Baseline: The schema of the Deployment Target will be read to create a baseline schema.
  • Shadow Database: SCA keeps the shadow database consistent with all the migration scripts currently in the project as needed, and uses it to verify scripts to detect problems in your code.
  • (Table) [__MigrationLog] keeps track of the migrations and Programmable Objects/additional scripts that have been executed against your database. (Additional executions of Programmable Objects/additional scripts will result in new rows being inserted.)
  • (View) [__MigrationLogCurrent] lists the latest version of each migration/Programmable Object/additional script to have been executed against the database.
A big announcement from Microsoft this month: The introduction of Azure DevOps, the most complete offering of proven, modern DevOps tools and processes available in the public cloud. Used together, the Azure DevOps services span the entire breadth of the development lifecycle so enterprises can modernize apps in a faster and more streamlined way.

What Is DevOps, Anyway?

DevOps solutions bring together people, processes, and technology, automating and streamlining software delivery to provide continuous value to your users.What is DevOps?

If you want your next development or app modernization project to be a success, DevOps is the way to go.

High-performance DevOps enterprises achieve increased revenue with a faster time to market and produce solutions that are more powerful, flexible, and open. (Yes, Microsoft has been partnering with the open-source community to ship products that work for everyone.) New features can be safely deployed to users as soon as they’re ready vs. bundling them together in one large update down the road.

New Services & Tools in Azure DevOps

  • Azure Pipelines – Continuously build, test and deploy to ANY language, platform, or cloud.  Azure Pipelines offers unlimited build minutes and 10 free parallel jobs for all open-source projects!
  • Azure Boards – Plan, track and discuss your work and ideas across teams with proven agile tools.
  • Azure Artifacts – With the click of a button, add artifacts to your CI/CD pipeline.
  • Azure Repos – Unlimited private Git repos (cloud-hosted) allow team members to build and collaborate better.
  • Azure Test Plans – These manual and exploratory testing tools will allow you to test and ship with ease and confidence.

Azure DevOps is what’s next for Visual Studio Team Services. VSTS users will be automatically upgraded without jeopardizing functionality.

With the services provided with Azure DevOps, you can choose the tools and cloud services that you want to use and build end-to-end solutions for an enterprise-level toolchain. As long-time believers in both Azure and DevOps, we’re really excited about this offering and what it can offer our clients.

FREE HALF DAY SESSION: APP MODERNIZATION APPROACHES & BEST PRACTICES
Transform your business into a modern enterprise that engages customers, supports innovation, and has a competitive advantage, all while cutting costs with cloud-based app modernization.