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.

In a previous blog post, we discussed a quick overview of Continuous Integration and Deployment of .NET applications using Visual Studio Team Services (VSTS). This involved building and deploying regular old .NET applications with VSTS—something that we would definitely expect a Microsoft service to handle. However, there is some lesser-known support that VSTS has for other frameworks, including Java. The Microsoft VSTS website even has a portal page proclaiming their Java support: “Love Java? So do we!

VSTS support for Java build frameworks such as Maven and Ant came in handy for AIS recently, as we were tasked with developing some new features for an older Java desktop application for a federal client. And I will have to say that all of the VSTS tools for Java applications worked flawlessly. We were able to easily add the Java project source code to a Team Foundation Version Control (TFVC) repository hosted online in VSTS. Oracle even has an extension for integrating with a TFVC workspace—allowing us to check in changes right from the JDeveloper IDE. Read More…

AIS recently worked with the General Services Administration (GSA) Technology Transformation Services Division, better known as 18F.  The engagement involved working with 18F to digitize the Department of Labor’s Section 14(c) certification application process (part of the Fair Labor Standards Act). This is currently a paper-based process that 18F hoped to modernize as an intuitive, online application…and to do it using agile methodologies.

AIS was tasked with building the first version of the digital form within a 60-day period of performance – much shorter than typical federal contracts.  AIS pulled together a multi-disciplinary team comprised of user researchers, designers, and front- and back-end web developers to work closely with 18F and the Department of Labor (DOL) Product Owner. The team built the entire form with complex validation along with a registration and login and an administrative section to process the form applications. They performed multiple usability tests with actual end users, and followed 18F’s principles of working in the open using a public GitHub repository. All User Stories and discussion threads were thoroughly documented in that repository’s issues list.

AIS was able to work together with many divisions inside DOL to make this happen.  We addressed security concerns by the Chief Information Security Officer (CISO) and worked with the CIO office to coordinate delivery of the application and a testing and staging environment for deployment. We also set up a Continuous Integration/Continuous Deployment process so that multiple DOL stakeholders could stay abreast of what was happening and exercise the existing application state.  We were even able to address legal concerns with testing by external citizens by getting signed consent forms for testing and recording the sessions.

The collaboration was so successful that our client wrote their own blog post on the project, detailing exactly “how government and private industry can work together using agile methodologies to produce great results.” You can read it here. 

These types of successful, agile engagements break down the myths that software development for the government needs to take months (or even years). Government can and will move faster, and after every small win like this project, the traditional methods of building software and procuring software development are changing across the industry.  This bodes well not just for the citizens who need to interact with these digital services… but also for saving our tax dollars.

I have been trying lots of new things since I got on board with AIS. From MDX to PowerShell to MVC. My newest endeavor was Azure. I was initially intimidated by the amount of time I would have to spend getting to know the setup before I could do something simple on it, but I have to say it was about the easiest thing I’ve tried so far. Congrats to the folks at Microsoft for creating something so useful that “just works,” even if you are the person who always tries to make things too difficult (me).

I got my first website up and running on Azure in about one hour, including migrating my database. It was so easy I had to share a simplified version of my experience.

Read More…