5 September 2019
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.

 

You may also like

  • Webinar

    How SQL Change Automation helps you deliver value faster

    As an Architect or Senior Developer, you need to deliver value to your customers more quickly than ever. Your software delivery pipeline can’t be held up by database changes. Instead, you need to bring database changes into your Agile development practices and optimize these practices to find bugs well before they head to production.

  • Webinar

    How to keep your delivery processes secure with Database DevOps

    Extending DevOps practices to the database, brings additional advantages, ensuring you protect personal data across your SQL Server estate, while also improving the efficiency and quality of software delivery. We’ll explore the impact database DevOps has on regulatory and compliance requirements and how approaches such as automation, can improve accuracy, transparency, and faster recovery across the entire database development cycle.

  • Article

    Database Development in Visual Studio using SQL Change Automation: Getting Started

    Steve Jones shows how to set up a SQL Change Automation (SCA) project in Visual Studio, and import an existing database. As the team make database changes, either in SSMS or VS, they import them into the SCA project, which saves each change as a migration script that is then committed to source control.

  • Article

    Static Data and Database Builds

    Whichever way you wish to ensure that a database, when built, has all the data that will enable it to function properly, there are reasonably simple ways of doing it. Phil Factor explains the alternatives.

  • Forums

    SQL Change Automation Forum

    Continuous integration and automated deployments for your SQL Server database