5 September 2019

SQL Change Automation 4.0: Collaborative Database Development Across Visual Studio and SQL Server Management Studio

SQL Change Automation's Development component for developing new databases and modifying existing databases, using migrations, now integrates directly into SQL Server Management Studio as well Visual Studio. It allows teams to collaborate effectively during development, regardless of their preferred IDE, and in a way that integrates easily with common build/integration servers and release management tools.

SQL Change Automation 4.0 offers teams new ways to work when developing database changes for SQL Server, Azure SQL Database, and Azure SQL Database Managed Instances.

In this article, I’ll first give you a quick overview of the new SSMS development tool included in SQL Change Automation 4.0. I’ll explain why we have written a new extension, how we believe teams will benefit from this, and then give a high-level comparison of the Visual Studio and SSMS extensions. Finally, I’ll list out some additional features in the v4.0 release.

SQL Change Automation Development now works in SSMS

The Development component of SQL Change Automation lets teams develop and version database code using a migrations-first approach. It appeals to teams who want to control precisely and fine-tune the exact series of commands that are executed in a database deployment, while still having state-based features available to support merging branches and comparisons between source control and databases.

Previously, SQL Change Automation Development was available only as an extension for Microsoft Visual Studio (the tool was previously called ReadyRoll and was re-branded as SQL Change Automation in June 2018). With SQL Change Automation 4.0, teams can now develop database changes in either Visual Studio or Microsoft SQL Server Management Studio (SSMS), based on their preference, and can collaborate on a SQL Change Automation development project in both IDEs.

Throughout development, the team can continuously build, integrate and test and deploy their database changes, by supplying the project as a source to SQL Change Automation’s Deployment component, for building and deploying databases using PowerShell. This component is unaffected by the v4.0 release and continues to be available in PowerShell cmdlets, as well as Integration add-ins for Azure DevOps Server and Services, Octopus Deploy, TeamCity, Bamboo, and Jenkins.

Similarly, if you currently work only with the Deployment component of SQL Change Automation, this release does not change the way you work. You can still use a directory of object-level build scripts, or even a single database build script, as the source, and in this case should continue to work directly with the PowerShell cmdlets, or with the Integration add-in of your choice, for automating your database builds and deployments.

Improving DevOps collaboration

The goal when developing SQL Change Automation has always been that it will fit in with a team’s preferred way or working, rather than enforce change. It is designed to fit the current requirements of a team, but to be flexible and adaptable, as the teams evolve their process as a result of coding reviews, retrospectives and so on.

Consequently, the intent was always that, rather than being a standalone tool that would require teams to learn a new IDE, it should simply slot into the teams’ preferred IDEs. SQL Change Automation v4.0 now supports both teams who wish to do database development only in SSMS, and heterogeneous teams where, for example, application developers work in Visual Studio, while database developers and database administrators work in SSMS. It’s also simple to switch IDEs at any time and continue working on the same project.

This has some obvious advantages. First, it will help DevOps teams to collaborate much more effectively on database development and deployment, since the toolset and approach will be standardized, but without forcing any team member to learn a new IDE, while simultaneously trying to implement new and better Database DevOps processes.

It will also reduce training costs, and in some cases licensing costs, for team members who want to collaborate on SQL Change Automation database development projects, but previously only worked in SSMS.

How do the SQL Change Automation extensions compare?

Although SSMS is built on the foundation of the Visual Studio Shell, it provides a very different user experience. This is one of the reasons that some users have a strong preference for either Visual Studio or SSMS: it’s quite jarring to move between the tools unless you do so on a very regular basis.

Therefore, rather than simply try to force the SSMS extension to look and feel just like the Visual Studio extension our UX team and our developers worked with users to build a workflow for the SSMS extension that feels the most natural in that environment.

So, while SQL Change Automation’s Visual Studio and SSMS extensions both support the following functionality, you will find that the extensions display them a bit differently:

Creating a new project, and optionally creating a baseline schema (with filters if needed)
Opening an existing project
Using programmable objects and the offline schema model: these are state-based features which help identify merge conflicts when branching, and enable state-based comparisons between source control and databases
Importing and automatically scripting changes from a database into an editable migration script
Applying changes in migration scripts to an existing development database, which is not yet in sync with the codebase
Performing a verification of migration scripts – a type of local build for your project

One significant difference between the extensions is that SQL Change Automation for Visual Studio integrates with Visual Studio’s strong native source control integration, allowing you to easily use a graphic interface to commit and manage changes in the Version Control System of your choice.

You can and should still use version control for your SQL Change Automation project in SSMS, but the SSMS extension currently only provides a “working folder” experience. This means that SQL Change Automation looks at the files present on the file system, and you need to use either the command line, Visual Studio, or an external tool of your source to commit changes, create a branch, and so on, in your chosen Version Control System.

The Redgate teams are not done bringing you new features for SQL Change Automation, however – if you check out our Roadmap, you’ll see that we’re already busy researching Version Control System integration.

Additional features in SQL Change Automation 4.0

While the key feature in this release is the new SQL Change Automation Development extension for SSMS, we’ve recently made some important features available in incremental releases, which have haven’t formally announced:

Data compare options for PowerShell – SQL Change Automation allows finer control of options to support the automation of changes to static data
Static code analysis – identifies potential problems with the migration scripts to be deployed and warns the user about them. Alerts the user of issues before changes are made to Production and them to ensure best practices are being followed.

How to try out SQL Change Automation in SSMS

This release is the result of a major engineering effort, including time incorporating feedback from users during our beta program, and we would love to hear what you think.

If you’re new to SQL Change Automation, you can get V4.0 by purchasing a Toolbelt. Existing customers can upgrade in tool.

Tools in this post