What is SQL Change Automation?
Published 23 August 2019
SQL Change Automation (SCA) is a software framework that makes it quicker, safer and easier to build or update databases automatically, from source code. SCA will support teams that wish to improve, step-wise, the speed and reliability of the system they use to develop, build, test, and deploy SQL Server databases. It does this by providing a consistent and repeatable way of automating many of the tasks that are required, and by working in conjunction with the tools and techniques with which the teams are already familiar.
SCA uses PowerShell to glue processes together and uses .NET objects to represent data structures. Under the covers, it uses Redgate SQL Toolbelt components to do the heavy lifting and add-ins to integrate with commonly used deployment tools.
SCA is designed to be able to work with either the migration-based or static (state-based) database development approach. It can use the existing development IDEs such as SSMS and Visual Studio. It can integrate with existing tool chains, including systems for source control, build, integration, tracking, and reporting.
It avoids requiring radical change, and so can be used to any extent, from supporting a single process through to managing the whole database deployment, including the whole range of DevOps activities associated with building, testing, integrating, and deploying a database.
It consists primarily of a Development component, a Deployment component and an Integration component:
- SCA Development integrates directly into the SQL Server development GUI (Visual Studio or SQL Server Management Studio) and allows teams to develop new databases and modify existing databases, using migrations, in a way that promotes Continuous Build and Integration practices.
- SCA Deployment provides a set of PowerShell cmdlets to automate build and release processes for your SQL Server databases. These can be invoked directly from a command line interface, via scripting, from a .NET application, or using the SCA Integration plug-ins that work with most build and integration servers and workflow services.
- SCA Integration provides a range of add-ins to extend some of the popular build, continuous integration and deployment tools. For example, there is TeamCity plugin, an Azure DevOps extension, which works with Team Foundation Server (TFS). There are also add-ins for Bamboo and Jenkins, and Octopus Deploy step templates.
SCA packages its outputs (NuGet or .zip) in order to communicate database artifacts between processes. These packages can be used for workflow within the application deployment pipeline such as testing, deployment, approvals and sign-off. The packages can also be used for security and to guarantee against unapproved changes.
The product article What is SQL Change Automation? offers Phil Factor's take on the principles of SQL Change Automation, for consistent and repeatable ways of automating database build, test, and deployment processes.